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.
Learning Objectives:
- Project a savings balance forward with FV
- Convert an annual rate and a term in years into the monthly units FV expects, the same conversion PMT needs
- Read the negative sign FV expects for a regular contribution, the same convention PMT uses
- Turn a projected value into a plain yes-or-no with IF, once it's just two numbers being compared
Solve without hints for +5 XP
Interactive Spreadsheet
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 teaches (contains 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.