PMT, PV and FV in Excel: how to use it
PMT gives the payment per period on a loan, PV how much you can borrow with a fixed monthly payment, and FV what your savings are worth after a while.
Try the first exercises for freeIn the lesson Borrowing and saving with PMT, PV, FV and NPER
What do PMT, PV and FV do?
PMT, PV and FV calculate with interest over a series of fixed payments. PMT gives the amount per period: how much you pay each month for a loan. PV turns that around: how much you can borrow if you can spare a fixed amount each month. FV gives what you'll have at the end if you save the same amount every month.
All three ask for the interest rate per period and the number of periods. If you pay or save per month, divide the annual rate by 12 and multiply the years by 12. Money you pay out gets a minus sign in Excel: put a minus sign in front of the amount, and the result comes out positive.
When do you use PMT, PV and FV?
- How many months it takes to pay off a loan at 300 a month: =NPER(B2/12,-300,A2).
- The monthly payment on a loan: =PMT(B2/12,C2*12,-A2).
- How much you can borrow with 300 a month: =PV(B2/12,C2*12,-300).
- What you save with 100 a month: =FV(B2/12,C2*12,-100).
- What an amount is worth after 10 years at an annual interest rate: =FV(B2,10,0,-A2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The amount per period, such as the monthly payment on a loan.
Filled in: =PMT(B2/12,C2*12,-A2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
PMT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | PMT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
rate | The interest rate per period. If you pay per month, divide the annual rate by 12. | B2/12 |
nper | How many times you pay or pay in, such as 48 months. | C2*12 |
pv | The present value: today's amount, such as the loan. With a minus sign if you're the one paying it. | -A2 |
[fv] | Optional: the amount left at the end. Without fv, that's 0. | left out |
[type] | Optional: 1 if you pay at the start of each period, 0 or nothing if you pay at the end. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
PMT, PV and FV step by step
D2 contains =PMT(B2/12,C2*12,-A2). A loan of 10,000 at 5% a year, paid back over 4 years: 230.29 a month. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click D2 and type PMT with a bracket.
=PMT( - Give the interest rate per month: the annual rate in B2 divided by 12. Type a comma.
=PMT(B2/12, - Give the number of months: the years in C2 times 12. Type a comma.
=PMT(B2/12,C2*12, - Give the loan with a minus sign, so that the monthly payment comes out positive.
=PMT(B2/12,C2*12,-A2 - Close the bracket and press Enter: 230.29 a month.
=PMT(B2/12,C2*12,-A2)
More examples
How much can you borrow?
D2 contains =PV(B2/12,C2*12,-A2). With 300 a month, for 5 years at 4% a year, you can borrow about 16,290.
Saving every month
D2 contains =FV(B2/12,C2*12,-A2). After 10 years, you've paid in 12,000 and you have about 13,974: the interest earned interest too.
How much interest you pay in total
D2 contains =PMT(B2/12,C2*12,-A2)*C2*12-A2. 48 months times 230.29 is 11,054: 1,054 of that is interest.
Interest and periods in the same unit
If you pay monthly, divide the yearly rate by 12 and multiply the years by 12. Mixing a yearly rate with months gives far too high an amount.
Where it often goes wrong
- If you forget the minus sign, you get the same amount, but negative.
- PMT works with a fixed rate and fixed payments. If the rate changes along the way, the result no longer holds.
Practise this in the lesson Borrowing and saving with PMT, PV, FV and NPER. The first exercises are free.
Frequently asked questions
What do the last two arguments mean?
With fv you give an amount left over at the end, such as a final payment. With type 1 you pay at the start of each period instead of at the end. Usually you leave them out.
Read next
ROUNDCEILING and FLOORMOD and INTLARGE and SMALLMEDIAN and MODE