Skip to content

Excel function · Financial

Excel PV function

PV gives the present value of regular payments or of a single future amount: how much you can borrow if you can pay 500 a month, or what an instalment price is worth in cash today.

In Turkish Excel: BD

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

English Excel=PV(rate, nper, pmt, [fv], [type])
Turkish Excel=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

ArgumentRequiredDescription
rateRequiredThe interest or return per period; for monthly payments, the annual rate divided by 12.
nperRequiredThe total number of payments.
pmtRequiredThe amount paid each period; negative if it leaves you. 0 for a single future amount.
fvOptionalAn amount received or paid after the last period; 0 if omitted.
typeOptional0 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.

E2fx=PV(C2/12,D2,B2)
How much you can borrow for a given payment: example table
ABCDE
1ScenarioMonthly PaymentAnnual RateMonthsCan Borrow
236 months-500.007.50%3616,073.96
348 months-500.007.50%4820,679.19
460 months-500.007.50%6024,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.

E2fx=PV(D2,C2,B2)
Pay in instalments or pay cash?: example table
ABCDEFG
1ItemInstalmentInstalmentsMonthly ReturnPresent Value of InstalmentsCash PriceCheaper
2Fridge-150.00120.40%1,754.061,700.00Cash
3Air conditioner-110.0090.40%970.49990.00Instalments
4Laptop-200.0060.40%1,183.381,199.00Instalments
  • 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

Type this in Nexell

Add a column to my loan scenarios table showing how much can be borrowed for each monthly payment, rate and term

Convert to table

Bring 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.

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free