Compound Interest
Free Compound Interest: calculate compound interest with =P*(1+rate/n)^(n*years), where n is the number of compounding periods per year — or with...
Total interest earned: €6,470.09 · Monthly · 10 years
| Year | Interest | Balance |
|---|---|---|
| 1 | €511.62 | €10,511.62 |
| 2 | €537.79 | €11,049.41 |
| 3 | €565.31 | €11,614.72 |
| 4 | €594.23 | €12,208.95 |
| 5 | €624.63 | €12,833.59 |
| 6 | €656.59 | €13,490.18 |
| 7 | €690.18 | €14,180.36 |
| 8 | €725.49 | €14,905.85 |
| 9 | €762.61 | €15,668.47 |
| 10 | €801.63 | €16,470.09 |
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
How it works
In Excel or Google Sheets, calculate compound interest with =P*(1+rate/n)^(n*years), where n is the number of compounding periods per year — or with =FV(rate/n, n*years, 0, -P) using the built-in future value function.
Put principal, annual rate, years, and periods per year in four cells.
Compute =P*(1+rate/n)^(n*years) — or =FV(rate/n, n*years, 0, -P).
Note the minus sign on P in FV: Excel treats the deposit as money paid out.
Format the result as Currency and sanity-check against the calculator above.
What this does
Compound interest pays interest on previously earned interest, so a balance grows geometrically rather than linearly. The compounding frequency n matters: 5% compounded monthly yields slightly more than 5% compounded yearly, because each month's interest starts earning its own interest immediately. FV exists precisely for this; the explicit power formula shows what it is doing.
A worked example
With €10,000 at 5% compounded monthly for 10 years, =10000*(1+0.05/12)^(12*10) returns €16,470.09. Compounded only yearly, =10000*(1.05)^10 returns €16,288.95 — the extra €181 is the compounding-frequency effect. The equivalent built-in is =FV(0.05/12, 120, 0, -10000). Savings plans, loans, and investment projections all run on compound growth. Setting the formula up once with cell references lets you test scenarios by typing.
Common mistakes
- Using the annual rate per month without dividing by 12.
- Forgetting the minus sign on the present value in
FVand getting a negative result. - Comparing offers with different compounding frequencies by nominal rate alone — use
=EFFECT(rate, n)to get the effective annual rate. - Entering 5 instead of 0.05 (or 5%) for the rate, which explodes the result.
FAQ
What is the compound interest formula in Excel?
There is no COMPOUND function; use =P*(1+rate/n)^(n*years) or =FV(rate/n, n*years, 0, -P).
What does compounding frequency change?
How often interest is added to the balance. More frequent compounding yields a higher effective annual rate for the same nominal rate.
How do I add monthly deposits?
Use the pmt argument of FV: =FV(rate/12, months, -deposit, -P) for end-of-month deposits.