Excel guide · 2 min read
How to calculate compound interest in Excel
Use =P*(1+r/n)^(n*t), for example =B1*(1+B2/B4)^(B4*B3), where P is the starting amount, r the annual rate, n the compounds per year and t the years. The FV function gives the same result: =FV(B2/B4,B3*B4,0,-B1).
Sample data
Every formula on this page was calculated live against this sheet.
| A | B | |
|---|---|---|
| 1 | Principal | 10000 |
| 2 | Annual rate | 0.05 |
| 3 | Years | 10 |
| 4 | Compounds per year | 12 |
| 5 | Monthly deposit | 200 |
Method 1: The compound interest formula
Compound interest adds interest to the balance, so next period's interest is earned on a bigger amount. The classic formula is A = P(1 + r/n)^(nt). In Excel, ^ means "to the power of".
With 10,000 at 5% compounded monthly for 10 years, the rate per period is 5%/12 and there are 120 periods.
Result: 16470.09
Returns 16,470.09, the balance after 10 years.
Method 2: The FV function
FV(rate, nper, pmt, [pv]) returns the future value of an investment. Pass 0 for pmt when there are no extra deposits, and the principal as a negative pv (money you pay in). The result is the same as the formula above.
Result: 16470.09
Returns 16,470.09, matching the manual formula.
Interest earned
Subtract the principal to see how much of the balance is interest. Simple interest would have earned exactly 5,000 (500 a year for 10 years), so the extra is the compounding effect.
Result: 6470.09
Returns 6,470.09 of interest.
Compound interest with regular deposits
FV really shines when you add money every period. Put the monthly deposit in pmt, also as a negative because it is money going in. Payments are assumed at the end of each month; add 1 as the last argument if you deposit at the start.
Result: 47526.55
Returns 47,526.55 after 10 years with 200 added each month.
Compare compounding frequencies
Change B4 to 1 for yearly, 4 for quarterly, 12 for monthly or 365 for daily. More frequent compounding earns a little more. The yearly version of this example gives 16,288.95 instead of 16,470.09.
Excel's EFFECT function converts a nominal rate to the effective annual rate: =EFFECT(5%,12) is about 5.12%.
Result: 16288.95
Returns 16,288.95 with annual compounding.
Common mistakes
Forgetting to divide the rate
With monthly compounding the rate per period is the annual rate divided by 12. Using 5% per month vastly overstates growth.
FV returns a negative number
FV uses cash-flow signs. Enter the principal and deposits as negatives, or put a minus sign in front of FV.
Missing brackets around n*t
=B1*(1+B2/B4)^B4*B3 raises to the power of B4 only, then multiplies by B3. Wrap the exponent: ^(B4*B3).
FAQ
What is the compound interest formula in Excel?
=P*(1+r/n)^(n*t), with each letter pointing at a cell. Or use =FV(r/n, n*t, 0, -P) for the same answer.
How do I calculate monthly compound interest in Excel?
Divide the annual rate by 12 and multiply the years by 12: =P*(1+r/12)^(12*t) or =FV(r/12,t*12,0,-P).
How do I build a year-by-year compound interest table?
List years 1 to 10 in column A, then use =$B$1*(1+$B$2/$B$4)^($B$4*A8) in each row. Lock the inputs with dollar signs so only the year changes.
What is the difference between simple and compound interest?
Simple interest is paid only on the original amount: =P*r*t. Compound interest is also paid on interest already earned, so it grows faster the longer you leave it.
How do I find how long it takes to double my money?
Use NPER: =NPER(5%/12,0,-10000,20000) returns the number of months to double at 5% compounded monthly, about 166.7 months or roughly 13.9 years.
Functions used
Keep learning
- How to calculate a loan payment in Excel
- Absolute vs relative cell references in Excel
- How to calculate percentage in Excel
Updated 2026-10-31