# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/compound-interest-in-excel-fv-formula
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Compare against the online compound interest calculator](https://dothecalculation.com/calculators/compound-interest-calculator) — Model the same scenario with contributions and see the interest-on-interest portion broken out.

## Step 1: the input block

**Inputs**
| 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

**FV in Excel and Google Sheets**

```
=FV(rate, nper, pmt, [pv], [type])
```
- rate: rate per period
- nper: number of periods
- pmt: the contribution each period, negative because it leaves your pocket
- pv: the starting balance, also negative
- type: 0 for end-of-period contributions, 1 for beginning

**The working formula**

```
=FV(B6, B7, -B2, -B1, 0)
```
- On the values above this returns 379,177.
- Total contributed: 10,000 + (400 × 300) = 130,000
- Interest earned: 249,177

> **The sign rule, once** — Money you put in is negative and money you get back is positive. Negating both pmt and pv makes FV return a positive balance. If your answer comes back negative, this is why.

## 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.

**Same inputs, different timing**
| 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.

**Yearly schedule formulas, row 11 onwards**
| 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.

> **The point of the table** — The crossover year is the single most useful number in a savings plan. Before it, your deposits drive the balance. After it, the balance drives itself, and stopping contributions costs far less than it would have earlier.

## Step 5: working backwards

Three sibling functions answer the reverse questions, each taking the same arguments in a different order.

**Solving for the other unknowns**
| 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.

**Real rate of return**

```
Real rate = (1 + nominal) / (1 + inflation) − 1
```
- 7% nominal with 2.5% inflation: 1.07 / 1.025 − 1 = 4.39%
- Subtracting instead gives 4.5%, close but not exact, and the gap compounds.

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

Tool: [Try the savings goal calculator](https://dothecalculation.com/calculators/savings-calculator) — Work backwards from a target balance to the monthly contribution it needs.

**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%.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/compound-interest-in-excel-fv-formula). Quote freely with attribution and a link to this page._