Lesson 13: Loan payments and interest with PMT

Practice exercises for accountants, checked instantly in your browser.

PMT(rate, periods, present value) returns the fixed payment on a loan. The rate and the periods must use the same unit: for monthly payments, divide the annual rate by 12 and multiply the years by 12. PMT shows money going out as negative, so enter the loan amount as negative (-B1) to get a positive payment.

Each payment splits into interest and principal. Interest for a month = opening balance × annual rate ÷ 12, rounded to the cent. The rest of the payment reduces principal.

Worked example: =PMT(6%/12,36,-20000)
Monthly payment on a 20,000 loan at 6% over 3 years: 608.44.

Exercises

Exercise 1

Quarry Lane Bakery financed an oven: loan B1, annual rate B2, term in years B3. In B4, calculate the monthly payment as a positive number.

Exercise 2

In B5, calculate the first month's interest, rounded to the cent.

Exercise 3

In B6, calculate how much of the first payment reduces principal.