=PMT(B2/12,B3*12,-B1) returns the monthly payment on a loan of B1 at the annual interest rate in B2, repaid over B3 years. The rate is divided by 12 and the years are multiplied by 12 because the payments are monthly.
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
A 250,000 loan at 6.5% over 30 years costs 1,580.17 a month, and the interest over the whole term is 318,861.22, more than the loan itself. Change B3 to 15: the payment rises to 2,177.77 but the total interest drops below half.
PMT syntax
=PMT(rate, nper, pv, [fv], [type])
rate: the interest rate per period. For monthly payments on an annual rate, userate/12.nper: the number of payments. 30 years of monthly payments is30*12, which is 360.pv: the present value, the amount borrowed.fv(optional): the amount left at the end. 0 for a loan paid off in full, which is the default; the savings goal for a savings plan.type(optional): 0 (default) for payments at the end of each period, as with most loans; 1 for the start of each period, as with rent or leases.
PMT assumes a fixed rate and equal payments. It does not include taxes, insurance or fees that a lender adds to a mortgage payment.
Why PMT returns a negative number
Excel's finance functions (PMT, PV, FV, NPV, IRR) use the sign of a number for the direction of the money. Money you receive is positive and money you pay out is negative. A loan is money you receive, so =PMT(B2/12,B3*12,B1) returns -1,580.17: a payment going out.
That is why the formula on this page has -B1: the loan amount enters as negative and the payment comes out positive. =-PMT(B2/12,B3*12,B1) does the same. Pick one and use it everywhere, because the same sign rule decides fv: a savings goal you will receive is entered as a negative future value with a 0 present value.
Compare loan terms
Put the terms in a column, keep the amount and the rate absolute, and fill the formula down to compare payments and total cost side by side.
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
Stretching the loan from 3 to 6 years cuts the payment from 876.13 to 489.56, while the interest grows from 3,540.58 to 7,248.66. For more on the $ signs, see absolute references.
How much to save each month
With pv at 0 and a future value, PMT answers the reverse question: how much to put away each month to reach a goal. The goal is money you will receive, so it goes in as a negative number to make the deposit positive.
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
Depositing 301.66 a month for 60 months at 4% reaches 20,000; the deposits add up to about 18,100, and interest supplies the rest. Change B2 to 0% and the deposit becomes exactly 20,000 divided by 60.
Interest and principal: IPMT and PPMT
Each payment pays some interest and some of the loan. IPMT returns the interest part of one payment and PPMT the principal part; together they equal PMT. Both take the payment number as their second argument, which gives an amortization schedule when filled down.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
In month 1, 1,354.17 of the 1,580.17 payment is interest and only 226.00 reduces the balance. The principal part grows a little every month. In a real workbook, put the rate, term and amount in cells and refer to them with $, as in the earlier sheets.
Try it: a car loan payment
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
Your turn: In B5, calculate the monthly payment for the loan in B1 at the annual rate in B2 over the years in B3. Show it as a positive number.
Hint: the rate and the number of payments must both be monthly.
Common mistake: an annual rate with monthly payments
The rate and nper must use the same period. Leaving the rate annual while counting months charges 6.5% every month, and the payment comes out about ten times too high:
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
The wrong formula asks for 16,250.00 a month, which is just the interest at 6.5% a month. A second version of the same mistake is typing the rate as 6.5 instead of 6.5% or 0.065: that means 650% a year. If a payment looks absurd, check the units of the rate first. For yearly payments use the annual rate and the number of years as they are.
Frequently Asked Questions
How do I calculate a monthly loan payment in Excel?
Use =PMT(rate/12, years*12, -amount), for example =PMT(6.5%/12,30*12,-250000) for a 30 year loan of 250,000 at 6.5%. It returns about 1,580.17 a month.
Why is PMT negative in Excel?
Excel's finance functions use signs for direction: money you receive is positive and money you pay is negative. The loan comes to you, so the payment going out is negative. Put a minus before the loan amount (-B1) or before PMT to show it as a positive number.
How do I calculate total interest on a loan in Excel?
Multiply the payment by the number of payments and subtract the loan: =PMT(B2/12,B3*12,-B1)*B3*12-B1. For the interest paid in the first year, use =-CUMIPMT(B2/12,B3*12,B1,1,12,0).
What is the difference between PMT, IPMT and PPMT?
PMT is the whole payment. IPMT is the interest part of one payment and PPMT the principal part, and the two add up to PMT. Early payments are mostly interest.
How do I calculate how much to save each month in Excel?
Give PMT a future value instead of a present value: =PMT(4%/12,5*12,0,-20000) is the monthly deposit that grows to 20,000 in 5 years at 4% a year.
