PMT with annual terms converted to monthly
The loan is quoted per year; the payment is per month.
Task:
A 25,000 loan is offered at 6.5% a year over 4 years, repaid monthly. In B5 give the monthly repayment as a positive figure.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of PMT
0/4The same formula in the other shapes it takes at work.
- PMT Function
Calculate the payment for a loan based on constant payments and a constant interest rate using the PMT function.
CoreAdvanced - PMT with annual terms converted to monthly
The loan is quoted per year; the payment is per month.
Advanced - PMT with a balloon payment
Leave part of the loan to be paid at the end, and the monthly payment falls.
Advanced - PMT for a savings goal
Not a loan this time: how much to put away each month to reach a target.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Annual rate | 0.065 |
| 2 | Years | 4 |
| 3 | Loan | 25000 |
| 4 | ||
| 5 | Monthly repayment |
What this exercise teachesMay contain the answer
Using 6.5% with 48 periods charges 6.5% a month; using 0.54% with 4 periods repays the loan in four months. Both answers are absurd once you look, but a single cell rarely gets looked at. Rate and periods must always describe the same period length.