Compound Interest in Excel: FV, Regular Contributions, and a Year-by-Year Table
The FV function handles compound growth with contributions in one line. Here is the build, the timing argument almost everyone leaves on the wrong setting, and how to separate real growth from inflation.
Compound Interest in Excel
You can write compound interest longhand as =P*(1+r/n)^(n*t), and for a lump sum with nothing added that is fine. As soon as you contribute monthly, the longhand version needs a second term for the annuity and gets awkward. FV handles both in one function.
Compare against the online compound interest calculatorModel the same scenario with contributions and see the interest-on-interest portion broken out.Step 1: the input block
Swipe sideways to compare columns.
| Cell | Label | Example |
|---|---|---|
| B1 | Starting balance | 10000 |
| B2 | Monthly contribution | 400 |
| B3 | Annual return | 7% |
| B4 | Years | 25 |
| B5 | Compounds per year | 12 |
| B6 | Periodic rate | =B3/B5 |
| B7 | Total periods | =B4*B5 |
Step 2: the FV formula
Step 3: the timing argument nobody sets
The final argument decides whether each contribution lands at the start or the end of the period. It defaults to 0, meaning the end, which quietly costs one period of growth on every contribution.
Swipe sideways to compare columns.
| type | Meaning | Result | Difference |
|---|---|---|---|
| 0 | Contribution at period end | $379,177 | — |
| 1 | Contribution at period start | $381,389 | +$2,212 |
Over 25 years the timing is worth roughly one extra monthly contribution grown for the full term. If you invest on the first of the month, use 1. It is a small difference, but it is free accuracy.
Step 4: a year-by-year table
A single FV number tells you the destination but not the shape of the journey, and the shape is the persuasive part. Headers in row 10: Year, Opening, Contributions, Interest, Closing.
Swipe sideways to compare columns.
| Column | Formula in row 11 | Notes |
|---|---|---|
| A (Year) | 1 | Fill down =A11+1 |
| B (Opening) | =$B$1 | Then =E11 in row 12 onwards |
| C (Contributions) | =$B$2*$B$5 | Annual total added |
| D (Interest) | =FV($B$6,$B$5,-$B$2,-B11,0)-B11-C11 | Growth within the year |
| E (Closing) | =B11+C11+D11 | Carries into the next row |
Fill down 25 rows. What the table shows is the crossover: in year 1 the account gains $4,800 in contributions against roughly $900 in interest. By year 14 interest exceeds contributions for the first time, and by year 25 interest is adding over $24,000 a year against the same $4,800 you put in.
Step 5: working backwards
Three sibling functions answer the reverse questions, each taking the same arguments in a different order.
Swipe sideways to compare columns.
| Question | Function | Example |
|---|---|---|
| How long to reach $500,000? | NPER | =NPER(B6, -B2, -B1, 500000)/12 → 28.4 years |
| What return do I need? | RATE | =RATE(B7, -B2, -B1, 500000)*12 → 8.24% |
| What must I contribute? | PMT | =PMT(B6, B7, -B1, 500000) → $598/month |
RATE solves iteratively and occasionally returns #NUM! when it cannot converge. Supplying a guess as the final argument, such as 0.005 for a monthly rate, usually resolves it.
Step 6: showing the number in real terms
A projection in nominal currency overstates what the money will buy. The clean way to handle it is to run the whole projection at a real rate rather than deflating the answer afterwards.
Rerunning the same plan at 4.39% gives $271,000 in today's purchasing power against $379,177 nominal. Both are correct; only one is meaningful when you are deciding whether the plan is enough.
Step 7: Goal Seek for a target
When the question has one unknown but no closed-form function, Goal Seek solves it numerically. On the Data tab, What-If Analysis, Goal Seek: set the FV cell to the target value by changing the contribution cell. Google Sheets has the same feature under Extensions, Add-ons, Goal Seek.
- Set cell: the FV formula cell
- To value: your target, for example 500000
- By changing cell: B2, the monthly contribution
Why does Excel FV return a negative number?
Because of the cash-flow sign convention. Contributions and the starting balance are money leaving you, so enter them as negative. FV then returns a positive future balance.
What does the type argument do in FV?
It sets whether each contribution occurs at the end of the period (0, the default) or the beginning (1). Using 1 gives every contribution one extra period of growth, which over 25 years is worth roughly one extra contribution.
How do I calculate compound interest without FV?
For a lump sum, =P*(1+r/n)^(n*t). With regular contributions you must add the annuity term: =P*(1+i)^n + PMT*(((1+i)^n - 1)/i), where i is the periodic rate. FV does both in one call, which is why it is worth using.
How do I adjust a projection for inflation?
Run the projection at a real rate rather than deflating the result. Real rate = (1 + nominal) / (1 + inflation) − 1. At 7% nominal and 2.5% inflation that is 4.39%.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team