# How to calculate compound interest in Excel

Use principal × (1 + rate/n)^(n × years): `=B2*(1+B3/B4)^(B4*B5)`, with n the number of compounding periods per year.

**Excel:** `=B2*(1+B3/B4)^(B4*B5)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Deposit | Value |
| Principal | 10000 |
| Annual interest rate | 0.045 |
| Compounding periods per year | 12 |
| Years | 10 |
| Balance |  |

Formula in B6: `=B2*(1+B3/B4)^(B4*B5)`

Result: $15,669.93

## How it works

Each period, interest is added to the balance, and the next period's interest is charged on the bigger balance. With monthly compounding the rate per period is 4.5%/12 and there are 120 periods, so the principal is multiplied by 1.00375 a hundred and twenty times.

## Other ways

- `=FV(B3/B4,B4*B5,0,-B2)`: FV does the same calculation, and can add regular deposits as its third argument.
- `=B2*(1+B3/B4)^(B4*B5)-B2`: Subtract the principal to see the interest earned. (Interest only: $5,669.93.)

## Common mistakes

- Leaving out n and using `(1+B3)^B5` compounds once a year, which gives less than monthly compounding.
- Write the rate as 4.5% or 0.045, not 4.5, or the balance explodes.

## Questions

**How do I calculate continuous compounding?**

Use EXP: `=B2*EXP(B3*B5)`.

**How do I make a year-by-year table?**

List the years in A, then `=$B$2*(1+$B$3/$B$4)^($B$4*A9)` filled down.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-compound-interest