PV function
PV finds today's worth of a series of future payments. For instance, it shows how large a loan a given monthly payment can support, with payments you make entered as negatives.
Example
Read it as a sentence: find the lump sum today that equals 500 paid at the end of each month for 20 years at 8% a year, which gives 59777.1458511878.
A
B
C
D
1
59777.1458511878
2
3
Syntax
PV(rate, nper, pmt, [fv], [type])
Arguments in [square brackets] are optional.
-
rate(required) - Interest rate per period.
-
nper(required) - Total number of periods.
-
pmt(required) - Payment made each period.
-
fv(optional) - Future value wanted; 0 if omitted.
-
type(optional) - 0 = payments at period end (default), 1 = at start.
Returns: Present value of a stream of payments.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=PV(0.08/12,240,500) | ($59,777.15) | Annuity |
=PV(0.05,10,0,1000) | ($613.91) | Fv only |
=PV(0.05,10,100,0,1) | ($810.78) | Type 1 |
=PV(0,10,100) | ($1,000.00) | Rate 0 |
=PV(0.05,0,100) | $0.00 | Nper 0 |
=PV("0.05","10","100") | ($772.17) | Text number |
=PV(-1,10,100) | #DIV/0! | Rate -1 |
=PV("a",10,100) | #VALUE! | Text |
Watch out for
- Payments you make should be negative so the result is positive.
- Keep rate and nper in matching units.
Where it works
- Excel
- PV is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .