PV Function in Excel, Google Sheets, Airtable & Notion
PV returns today's value of a series of equal future payments (and an optional final amount) at a constant interest rate. A typical use is "how big a loan can I afford for this monthly payment?".
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | PV(rate, nper, pmt, [fv], [type]) |
| Google Sheets | ✔ Built in | PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning]) |
| Airtable | ≈ Workaround | -pmt * (1 - POWER(1 + rate, -nper)) / rateNo PV function in Airtable; this is the annuity formula with Excel's sign convention (fv = 0, payments at period end). |
| Notion | ≈ Workaround | -pmt * (1 - pow(1 + rate, -nper)) / rateNo pv() in Notion; use the closed-form formula. For a 0% rate the answer is -pmt * nper. |
| Excel | =PV(6%/12, 30*12, -1500) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =PV(6%/12, 30*12, -1500) | open sheets.new ↗ |
| Airtable workaround | 1500 * (1 - POWER(1 + 0.06/12, -30*12)) / (0.06/12) | |
| Notion workaround | 1500 * (1 - pow(1 + 0.06/12, -30*12)) / (0.06/12) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
A negative payment (money you pay) gives a positive present value (money you can receive today, e.g. a loan amount). Rate and term are converted to per-period values.
Arguments
| Argument | What it means |
|---|---|
rate | Interest rate per period, e.g. 6%/12. |
nper | Total number of payments, e.g. 30*12. |
pmt | Payment each period. Payments you make are negative. |
[fv] optional | A lump sum at the end. Defaults to 0. |
[type] optional | 0 (default) = payments at period end, 1 = at the beginning. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=PV(6%/12, 30*12, -1500) | 250187.42 | Loan that $1,500 a month repays over 30 years at 6%. | |
=PV(5%, 10, 0, 10000) | -6139.13 | Invest $6,139.13 today to have $10,000 in 10 years at 5%. | |
=PV(4%, 20, -5000) | 67951.63 | Value today of $5,000 a year for 20 years. |
Examples use Excel / Google Sheets syntax unless noted.
How PV works
PV discounts future cash back to today. Use it to find the loan a given payment supports, the lump sum needed today to fund a series of withdrawals, or the cash price equivalent of an instalment plan. It is the inverse of PMT: =PV(6%/12, 360, PMT(6%/12, 360, 250000)) returns 250,000.
Rate and nper must use the same period (monthly rate with a number of months), and the sign convention applies: a negative payment gives a positive present value and vice versa. When you pass only fv (as in the second example), the result is negative because it is the amount you would have to put in today.
Airtable and Notion have no PV function. For the common case — equal payments, nothing left at the end — the closed form pmt × (1 − (1 + r)−n) / r is exact, and the copy buttons write it with Excel's sign. For PV with a future lump sum only, use fv / (1 + r)n with the sign flipped.
Frequently Asked Questions
- What is the difference between PV and NPV?
- PV assumes equal payments every period. NPV discounts a list of cash flows that can differ from period to period.