- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Advanced Excel Financial - PV Function
Description
The PV function calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value, which is your investment goal.
Syntax
PV (rate, nper, pmt, [fv], [type])
Arguments
| Argument | Description | Required/ Optional |
|---|---|---|
| Rate | The interest rate per period. | Required |
| Nper | The total number of payment periods in an annuity. | Required |
| Pmt | The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. |
Required |
| Fv | The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). |
Optional |
| Type | The number 0 or 1 and indicates when payments are due. Look at the Type-Payment Table given below. |
Optional |
Type-Payment Table
| Set type equal to | If payments are due |
|---|---|
| If payments are due | At the end of the period |
| 1 | At the beginning of the period |
Notes
Make sure that you are consistent about the units you use for specifying rate and nper
If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper
If you make annual payments on the same loan, use 12% for rate and 4 for nper
The following functions apply to annuities −
CUMIPMT
CUMPRINC
FV
FVSCHEDULE
IPMT
PMT
PPMT
PV
RATE
XIRR
XNPV
An annuity is a series of constant cash payments made over a continuous period.
For example, a car loan or a mortgage is an annuity. In annuity functions,
cash you pay out, such as a deposit to savings, is represented by a negative number
cash you receive, such as a dividend check, is represented by a positive number
Microsoft Excel solves for one financial argument in terms of the others. If rate is not 0, then −
$$pv*\left ( 1+rate \right )^{nper}+pmt\left ( 1+rate*type \right )*$$
$$\left ( \frac{\left ( 1+rate \right )^{nper}-1}{rate} \right )+fv = 0$$
If rate is 0, then −
(pmt * nper) + pv + fv = 0
If any of the specified arguments is non-numeric, PV returns #VALUE! Error.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016
Example
