Excel guide · 2 min read

How to calculate compound interest in Excel

Q: 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.

Quick answerUse =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 result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	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.

=ROUND(B1*(1+B2/B4)^(B4*B3),2)

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.

=ROUND(FV(B2/B4,B3*B4,0,-B1),2)

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.

=ROUND(FV(B2/B4,B3*B4,0,-B1)-B1,2)

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.

=ROUND(FV(B2/B4,B3*B4,-B5,-B1),2)

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

=ROUND(B1*(1+B2)^B3,2)

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

Related guides

Updated October 31, 2026