Project a reserve fund that already holds a balance and gets monthly top-ups with FV
FV doesn't have to start from zero — handed a starting balance alongside the regular contributions, it grows both at once in a single call instead of needing two projections added together.
Task:
You manage facilities for a property management company that keeps a reserve fund for major equipment replacements. The building's HVAC system will need replacing in 6 years. The reserve fund already holds $4,000, invested at 4% annual interest compounded monthly, and the company sets aside a further $150 a month into the same fund. In B6, use FV to project the fund's balance in 6 years. In B8, work out the surplus or shortfall against the $18,000 replacement cost in B7.
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 | Current reserve balance | 4000 |
| 3 | Monthly contribution | 150 |
| 4 | Annual interest rate | 0.04 |
| 5 | Years until replacement | 6 |
| 6 | Projected fund balance | |
| 7 | Replacement cost | 18000 |
| 8 | Surplus / (shortfall) |
What this exercise teachesMay contain the answer
FV(B4/12,B5*12,-B3,-B2) grows the existing $4,000 balance and the new $150-a-month contributions together, because the pv argument lets a starting balance compound alongside the regular payments instead of needing a separate calculation added on afterward. The annual rate and the 6-year term only agree with the monthly contribution once both are converted to the same monthly unit, exactly the reconciliation PMT needs. Both B2 and B3 go in negative because FV reads them the same way PMT does — money set aside now, in exchange for a balance it hands back later. The projection lands at $17,266.35, and B8's subtraction against the $18,000 target comes out negative: even with 4% growth compounding monthly for six years, the fund is $733.65 short, a gap worth knowing about years before the HVAC system actually fails rather than the week it does.