Excel PV Function: Present Value of Payments and Loans

The PV function in Excel tells you what money in the future is worth today. Give it an interest rate, a number of periods and a regular payment, and it returns the present value: for example, how big a loan a $500 monthly payment can repay. This guide is for anyone who meets loans, savings or finance questions in a spreadsheet or a job test, and it uses plain example figures that you can type in and check.

All the figures in this article are examples, checked in Python. They are not financial advice.

What does the PV function do in Excel?#

PV turns a stream of equal future payments, or one future amount, into a single value today. Money today is worth more than the same money later, because today's money could earn interest. PV removes that interest, which is called discounting.

=PV(rate, nper, pmt, [fv], [type])

PV works in every version of Excel. It is the mirror of PMT: the PMT function starts with the loan and finds the payment, while PV starts with the payment and finds the loan.

How to calculate how much you can borrow#

To find a loan amount, put the yearly rate, the years and the monthly payment in cells, then point PV at them. Set up this small sheet:

In B5, type:

=PV(B1/12,B2*12,-B3)

The result is 25,862.78. Each part does one job:

1. B1/12 turns 6% a year into 0.5% a month.
2. B2*12 turns 5 years into 60 monthly payments.
3. -B3 is the payment, entered as negative because you pay it out.

So 60 payments of $500 ($30,000 in total) repay a loan of about $25,863. The difference, about $4,137, is the interest.

Why is the PV result negative?#

PV is negative when the payment is positive, because Excel's finance functions follow a cash-flow sign rule. Money that leaves your pocket is negative and money that comes in is positive. If you pay $500 a month, the loan you receive today is the opposite sign.

=PV(B1/12,B2*12,B3) returns -25,862.78. The number is right; only the sign looks odd. You have three ways to show it as positive:

• Enter the payment as negative: -B3, as above.
• Put a minus in front of the whole function: =-PV(B1/12,B2*12,B3).
• Wrap it in ABS. The guide to the ABS function and absolute values shows how ABS drops the sign.

Pick one style and use it everywhere in the workbook, so readers don't have to guess.

How to find today's value of a future lump sum#

To value one amount you will receive later, set pmt to 0 and put the amount in the fv argument. How much is $10,000 in 10 years worth today, at 5% a year?

=PV(5%,10,0,-10000)

The result is 6,139.13. Put the other way round, $6,139.13 invested today at 5% a year grows to $10,000 in 10 years. You can check it without PV:

=10000/(1+5%)^10

This gives the same 6,139.13. Here the periods are years, so the rate stays as 5% with no division.

How to round the PV result#

PV returns many decimal places, so wrap it in ROUND when you need cents. Without rounding, the loan above is really 25,862.780375… The cell format can hide the extra digits, but they are still used in later sums.

=ROUND(PV(B1/12,B2*12,-B3),2)

This returns exactly 25,862.78. The 2 means two decimal places. The ROUND function guide covers ROUNDUP and ROUNDDOWN too, which help when a bank rounds in one direction only.

How to compare scenarios with absolute references#

To compare several payments, lock the rate and term cells with $ and fill the formula down. Say B1 holds 6% and B2 holds 5 years, and D2:D4 list three monthly payments. In E2, type:

=ROUND(PV($B$1/12,$B$2*12,-D2),2)

Fill it down to E4 (Windows: Ctrl+D; Mac: ⌘D):

$B$1 and $B$2 stay fixed while D2 moves down. Without the dollar signs, the rate would slide to B2 and B3 and the answers would be wrong. The article on absolute references and the dollar sign explains the rule, and you can practise relative and absolute references in a live sheet.

Change the rate instead and keep the payment at $500: at 4% the loan is 27,149.53, and at 8% it is 24,659.22. A higher rate means you can borrow less for the same payment.

Payments at the start of the period#

Use 1 as the type argument when each payment is made at the start of the period, such as rent paid in advance. Each payment then has one less period of interest, so the present value is a little higher:

=PV(6%/12,60,-500,0,1)

This returns 25,992.09, compared with 25,862.78 for payments at the end of each month. Most loans use the default, 0.

Common PV mistakes#

Most wrong PV answers come from the period, not the formula. Check these:

1. Yearly rate with monthly payments. =PV(6%,60,-500) treats 6% as a monthly rate and returns about 8,080.71, far too low. Divide the rate by 12.
2. Years instead of periods. With monthly payments, nper is months: 5*12, not 5.
3. Confusing PV with NPV. PV needs equal payments. For cash flows that change each year, use the NPV function. For the rate of return on uneven cash flows, use the IRR function.
4. The rate typed as a whole number. 6 means 600%. Type 6% or 0.06.
5. Mixing up fv and pmt. A one-off amount at the end goes in fv, with pmt set to 0.

Practise it#

PV is one of a family of loan formulas that finance tests like to ask about, and the hard part is getting the periods and signs right under time pressure. Start with the exercise on this page, then practise budget calculations in a live spreadsheet: you type the formulas and each answer is checked instantly. When you feel ready, try the finance Excel mock test.