What it does
Because of the time value of money, 10,000 received a year from now is worth less than 10,000 today. PV discounts future payments back to today at the rate you give. For a loan, that is the largest amount you can borrow for a payment you can afford.
The sign rule: the payment leaves you, so it is negative, and the result (the loan you receive today) comes back positive. For the present value of a single future amount, set pmt to 0 and enter that amount as fv.
For cash flows that differ from period to period, use NPV; to find a payment use PMT, and for a future value use FV. The rates in the examples are assumptions.
Syntax
=PV(rate, nper, pmt, [fv], [type])=BD(oran; dönem_sayısı; devresel_ödeme; [gd]; [tür])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
rate | Required | The interest or return per period; for monthly payments, the annual rate divided by 12. |
nper | Required | The total number of payments. |
pmt | Required | The amount paid each period; negative if it leaves you. 0 for a single future amount. |
fv | Optional | An amount received or paid after the last period; 0 if omitted. |
type | Optional | 0 or omitted: payments at the end of each period. 1: at the start. |
Examples
1How much you can borrow for a given payment
The monthly payment will leave you, so it is negative in column B. PV discounts the payments at the monthly rate and returns the amount you can borrow as a positive number. The rates are assumptions.
=PV(C2/12,D2,B2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Scenario | Monthly Payment | Annual Rate | Months | Can Borrow |
| 2 | 36 months | -500.00 | 7.50% | 36 | 16,073.96 |
| 3 | 48 months | -500.00 | 7.50% | 48 | 20,679.19 |
| 4 | 60 months | -500.00 | 7.50% | 60 | 24,952.65 |
- E2 (filled down to E4)
=PV(C2/12,D2,B2)
Because the payment is negative, the result is positive: 16,073.96 for 36 months. Stretching to 60 months raises it to 24,952.65, but the more payments you make, the more interest you pay in total.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Pay in instalments or pay cash?
The instalments are discounted at the assumed monthly return your money could earn elsewhere (column D). Column G says "Cash" if the cash price is below the present value of the instalments.
=PV(D2,C2,B2)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Instalment | Instalments | Monthly Return | Present Value of Instalments | Cash Price | Cheaper |
| 2 | Fridge | -150.00 | 12 | 0.40% | 1,754.06 | 1,700.00 | Cash |
| 3 | Air conditioner | -110.00 | 9 | 0.40% | 970.49 | 990.00 | Instalments |
| 4 | Laptop | -200.00 | 6 | 0.40% | 1,183.38 | 1,199.00 | Instalments |
- E2 (filled down to E4)
=PV(D2,C2,B2) - G2 (filled down to G4)
=IF(F2<E2,"Cash","Instalments")
The fridge's instalments are worth 1,754.06 today; the cash price is lower, so paying Cash is cheaper. For the air conditioner, the instalments are worth less than the cash price: Instalments.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Negative result
Cause: The payment was entered as a positive number, so Excel treated it as money coming in and returned the present value as negative.
Fix: Enter the payment as a negative number:
=PV(0.075/12,36,-500).- Result far too small
Cause: The annual rate was used with monthly payments without dividing by 12, so the future payments were over-discounted.
Fix: Divide the rate by 12.
- #VALUE!
Cause: One of the arguments is text that cannot be read as a number.
Fix: Make sure the cells really hold numbers.
Good to know
- To compare an instalment price with the cash price, discount the instalments at the monthly return your money could earn elsewhere; if the cash price is lower, paying cash is cheaper.
- The present value of a single future amount:
=PV(0.05,2,0,10000)brings 10,000 due in two years back to today; the result is negative.
In Nexell
Get Nexell to do it from a sentence
Add a column to my loan scenarios table showing how much can be borrowed for each monthly payment, rate and term
Convert to tableBring your scenario table into the tool and type this. Nexell adds the column with real Excel formulas and shows it in the preview; in the downloaded file, the amount changes when the payment or term does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How much can I borrow for the monthly payment I can afford?
Use =PV(annual_rate/12,months,-monthly_payment). Because the payment is negative, the result comes back positive as the amount you can borrow. Fees and charges from the lender may lower it.