Lesson 39 of 45, Finance
Loan payments with PMT
Quick answer
What a loan costs per month, and in total.
- Key formula
=PMT(B3/12,B4*12,-B2)(Monthly payment)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
PMT(rate, nper, pv) returns the payment per period. Match the units: for monthly payments, divide the annual rate by 12 and multiply the years by 12.
Excel treats money you receive and money you pay as opposite signs, so the payment on a positive loan comes back negative. Put a minus in front of the loan amount to get a positive payment.
Syntax
=PMT(B3/12,B4*12,-B2)Monthly payment=B6*B4*12-B2Total interest paidWatch out for
- Using the annual rate with monthly periods overstates the payment roughly twelvefold.
- A negative result isn't wrong, it's the sign convention. The grader accepts either sign.
The exercises
- Monthly payment. In B6, calculate the monthly payment for the loan in B2:B4.
- Total interest. In B7, calculate the total interest paid over the life of the loan.
- Rate scenarios. In B10, calculate the monthly payment at the rate in A10, using the loan amount and years above. Fill down to B13.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Monthly payment
- Total interest
- Rate scenarios