Monthly Loan Payment and Total Interest with PMT
Your task: In B5:B9, work out the monthly rate, number of payments, monthly payment, total repaid and interest.
Full instructions, scenario and goal
Scenario
A customer is thinking about a car loan. The bank quotes the amount, the annual interest rate and the length in years, with equal monthly payments. Before signing, the customer wants to know the monthly payment, how much will be repaid in total and how much of it is interest.
Your task
- In B5, calculate the monthly interest rate (the annual rate divided by 12).
- In B6, calculate the number of monthly payments (the years multiplied by 12).
- In B7, calculate the monthly payment as a positive amount, with PMT.
- In B8, calculate the total amount repaid over the whole loan.
- In B9, calculate the total interest paid. Select Check Answer when you are done.
You are done when
- The rate and the number of payments are both monthly.
- The payment is a positive amount.
- Total repaid and interest are built from the cells above.
What you will practise
Use PMT with matching rate and period units, and build the total cost of a loan from the monthly payment.
The idea in 30 seconds
PMT(rate, periods, loan) returns the fixed payment that pays off a loan. The rate and the number of periods must use the same unit: for monthly payments, divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with a monthly period count gives a number that looks plausible and is wrong.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell B5. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
PMT(rate, periods, loan) returns the fixed payment that pays off a loan. The rate and the number of periods must use the same unit: for monthly payments, divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with a monthly period count gives a number that looks plausible and is wrong.
Excel treats money you borrow as positive and money you pay as negative, so PMT returns a negative payment for a positive loan. Put a minus sign in front of the loan amount (or in front of PMT) to display the payment as a positive amount. Once you have the monthly payment, the total repaid is the payment times the number of payments, and the interest is the total repaid minus what you borrowed.
Skills you will practise
- PMT
- Loans
- Interest
- Finance
Complete first: Commission with One Rate Cell, Percent Change and Share of Total, Profit and Margin by Product
Common mistakes to avoid
- Mixing annual and monthly units
- PMT(6.5%, 5, ...) treats the loan as five annual payments at 6.5%. Convert both to months.
- A negative payment
- PMT returns negative numbers for a positive loan. Put a minus in front of the loan or of PMT.