Check whether a retirement savings plan hits its target
FV projects what regular contributions grow into — turning that projection into a yes or no just takes a plain IF.
Task:
You advise clients on retirement accounts at a financial planning firm. One client plans to contribute $250 a month into an account earning 4.5% annual interest, compounded monthly, for the next 12 years. Work out the projected balance in B5, then in B7 report whether it clears the $50,000 goal in B6.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | Item | Value |
| 2 | Monthly contribution | 250 |
| 3 | Annual interest rate | 0.045 |
| 4 | Term (years) | 12 |
| 5 | Projected balance | |
| 6 | Goal | 50000 |
| 7 | Goal met? |
What this exercise teachesMay contain the answer
FV's rate and nper only agree with each other if they describe the same period, exactly like PMT: an annual rate divided by 12 describes a month, and a term in years multiplied by 12 counts in the same unit — skip either conversion and the projected balance comes out wildly wrong rather than merely off. The contribution goes in as -B2 because FV reads it as a cash flow the same way PMT does: money leaving the account every month, set against the balance FV is building up in return. At $250 a month for 12 years the projection lands at $47,618 — short of the $50,000 goal even with 4.5% growth working in the client's favor, a gap that would be easy to get wrong doing the compounding by hand and that FV gets right by construction. B7 is an ordinary threshold IF, but it exists because a client asking "will this get me there" wants a yes or no, not a number they have to interpret themselves.