Excel PV Function: What a Future Amount Is Worth Today
Returns what a series of future payments, or a future lump sum, is worth now.
PV answers the question at the heart of finance: money later is worth less than money now, so how much less? Given a rate, a number of periods and a payment, it discounts everything back to today and tells you what the whole arrangement is worth at this moment.
The practical uses are comparing options that pay out differently. A supplier offering £500 a month for ten years and one offering £45,000 up front are not obviously comparable until you discount the first back — PV is how you find out which is actually better at your cost of capital.
It shares PMT's two conventions exactly. The result is negative because paying in is an outflow, and the rate must describe the same period as nper: monthly payments need the annual rate divided by twelve and the years multiplied by it. Getting that wrong gives an answer that looks reasonable and is out by an order of magnitude.
Syntax
=PV(rate, nper, pmt, [fv], [type])Arguments
rateRequired- The discount rate per period. Divide an annual rate by 12 for monthly periods.
nperRequired- The total number of periods, in the same unit as rate.
pmtRequired- The payment made each period. Pass 0 if you are valuing only a future lump sum.
fvOptional- A lump sum arriving at the end, on top of the payments. Defaults to 0.
typeOptional- 0 for payments at the end of each period (default), 1 for the start.
The example data
Headers in row 1, data in A2:E5. Rates are annual.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Plan | Deposit | Annual rate | Years | Target |
| 2 | Reserve fund | 500 | 0.045 | 10 | 75000 |
| 3 | Equipment pot | 250 | 0.032 | 5 | 16000 |
| 4 | Expansion | 1200 | 0.061 | 8 | 150000 |
Worked examples
=PV(C2/12, D2*12, -B2)Result: 48158.44
What ten years of £500 monthly deposits is worth today at 4.5%. The deposit is entered negative so the present value comes back positive.
=PV(C2, D2, 0, -E2)Result: 48283.51
Valuing the £75,000 target alone, with no payments — a single future amount discounted back ten years.
=PV(C3/12, D3*12, -B3)Result: 13836.61
The smaller plan. Note the rate and term are both converted to months to match each other.
=PV(C2/12, D2*12, -B2) > 45000Result: TRUE
The comparison that makes PV useful: the payment stream beats a £45,000 lump sum today.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
The result is negative
Why it happens: Excel's cash-flow convention. If pmt is positive (money coming in), PV is negative (what you would pay for it).
How to fix it: Enter the payment as a negative number, or negate the whole function. Do not do both.
Answer is about twelve times out
Why it happens: The annual rate was used with a monthly term or vice versa.
How to fix it: rate and nper must describe the same period. This is the single most common error across the whole finance family.
#VALUE!
Why it happens: An argument is text — often a rate typed with a % sign into a text cell.
How to fix it: Rates must be numeric. 4.5% is 0.045, or 4.5% in a percentage-formatted cell.
#NUM!
Why it happens: The arguments describe something impossible, such as a negative nper.
How to fix it: Check the term is a positive number of periods.
Tips worth knowing
- PV with pmt of 0 values a single future amount; PV with fv of 0 values a stream of payments. Both together handles an annuity with a balloon.
- NPV is the sibling for uneven cash flows — PV assumes every payment is identical.
- Keep rate, term and payment in labelled cells. A finance formula with numbers typed inline cannot be audited.
- PV, FV, RATE, PMT and NPER are five views of the same equation: give any four and Excel solves for the fifth.
Frequently asked questions
What is the difference between PV and NPV?
PV assumes a constant payment every period. NPV takes a range of individually specified cash flows, which can differ from period to period. Use PV for regular instalments and NPV for a project with varying yearly returns.
Why is my PV negative?
The cash-flow convention: inflows and outflows carry opposite signs. If you enter the payment as a positive number, PV returns a negative one to show it is the amount you would pay to receive that stream. Enter the payment negative to get a positive present value.
How do I discount a single future amount?
Pass 0 for pmt and put the amount in fv: =PV(rate, nper, 0, -amount). With no periodic payment, the whole calculation is discounting that one figure back to today.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.