PMT for a savings goal
Not a loan this time: how much to put away each month to reach a target.
Task:
You want 15,000 for a deposit in 3 years, saving monthly into an account paying 4% a year. In B5 give the monthly amount to save, 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.04 |
| 2 | Years | 3 |
| 3 | Target | 15000 |
| 4 | ||
| 5 | Save each month |
What this exercise teachesMay contain the answer
PMT works for any stream of equal payments, not just loans. With pv at 0 and the goal as fv it answers "how much each month". Interest does some of the work: the total saved is less than 15,000.