Finance function
Excel PV function
PV calculates the present value: what a series of future payments, or a single future amount, is worth today. Use it to see how big a loan you can afford, or to compare a lump sum with an annuity.
=PV(rate, nper, pmt, [fv], [type])Arguments
rate- The interest rate per period.
nper- The total number of payments.
pmt- The payment made each period. Enter money you pay out as negative.
fv- Optional. A lump sum received at the end.
type- Optional. 0 for payments at the end of each period (default), 1 for the start.
PV examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Scenario | Payment | Annual rate | Years |
| 2 | Car budget | 450 | 0.05 | 4 |
| 3 | Home budget | 1800 | 0.065 | 30 |
| 4 | Prize payout | 10000 | 0.06 | 5 |
Result: 19540.33
With 450 a month for 4 years at 5%, you can borrow about 19,540.
Result: 284779.48
A 1,800 monthly budget supports a mortgage of about 284,780.
Result: 7472.58
A single 10,000 paid in 5 years is worth about 7,472.58 today at 6%.
How to use PV
- Click the cell for the result.
- Type =PV( and the rate per period.
- Add the number of periods.
- Add the payment as a negative number, then press Enter.
Common PV mistakes
Using an annual rate with monthly payments
Divide the annual rate by 12 and multiply the years by 12.
Getting a negative answer you did not expect
PV returns the opposite sign of the payment. Enter the payment as negative to get a positive amount.
Putting a lump sum in the pmt argument
A single future amount goes in fv, with pmt set to 0.
PV FAQ
How much can I borrow based on my monthly payment in Excel?
Use =PV(annual_rate/12, years*12, -monthly_payment). The answer is the largest loan that payment can cover.
How do I calculate present value in Excel?
Use PV with the rate per period, the number of periods, and the payment. For a single future amount, use 0 for pmt and put the amount in fv.
What is the difference between PV and NPV?
PV assumes the same payment every period. NPV handles a list of uneven cash flows.
Guides that use PV
Related functions
Updated