Compound Interest Formula in Excel, Step by Step

The compound interest formula in Excel is =P*(1+r/n)^(n*t). Compound interest means you earn interest on your interest, not just on the money you started with. This guide builds the formula step by step from input cells, compares yearly, monthly and daily compounding, shows the FV shortcut and builds a year-by-year table with absolute references.

What the compound interest formula means#

The formula has four inputs:

r/n is the rate for one period: 5% / 12 per month. n*t is the total number of periods: 120 months. Each period, the balance is multiplied by (1+r/n), and the ^ operator repeats that multiplication for every period.

How to calculate compound interest in Excel#

Put the inputs in cells so you can change them later:

In B6, type:

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

The result is 16,470.09. Excel works out the brackets first, then the power, then the multiplication, which is exactly the order the formula needs.

Both bracket pairs matter. Without the second pair, =B1*(1+B2/B3)^B3*B4 raises to the power of 12 only and then multiplies by 10, giving over 105,000. To get the interest alone, subtract the principal: =B1*(1+B2/B3)^(B3*B4)-B1 returns 6,470.09.

To store the balance to the cent, wrap it in ROUND: =ROUND(B1*(1+B2/B3)^(B3*B4),2). Round only the final result, not each step, or small errors add up. The ROUND function guide and the article on ROUND, ROUNDUP and ROUNDDOWN explain why.

Yearly vs monthly vs daily compounding#

The more often interest is added, the faster the balance grows, but the extra gain gets smaller each time. Change n in B3 to compare:

Going from yearly to monthly adds about 181. Going from monthly to daily adds only about 17. For comparison, simple interest, which never pays interest on interest, gives =10000*(1+5%*10), or 15,000.

Using the FV function instead#

FV returns the same result without writing the formula out. Give it the rate per period, the number of periods, no regular deposit, and the principal as a negative number:

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

This returns 16,470.09. The minus sign follows Excel's cash-flow rule: money you put in is negative, money you get back is positive. FV can also add a regular deposit each month in its third argument, which the written formula can't do on its own.

How to build a year-by-year compound interest table#

A table shows how the balance grows each year. Put the rate in E1 (5%) and the principal in B9 (10,000) next to year 0 in A9. Then list the years 1 to 10 in A10:A19.

In B10, type a formula that grows last year's balance by one year of monthly compounding:

=B9*(1+$E$1/12)^12

Fill it down to B19 (Windows: Ctrl+D; Mac: ⌘D). B9 is relative, so it moves to B10, B11 and so on: each row builds on the row above. $E$1 is absolute, so every row uses the same rate. Press F4 on Windows (⌘T on Mac) to add the $ signs. The lesson on relative vs absolute references practises this exact pattern.

Year 10 matches the single-cell formula. Notice the yearly gain grows from about 512 in year 1 to more than 800 by year 10: that's compounding at work.

Compound interest with regular deposits#

The written formula handles one starting amount. If you also add money every month, use FV with the deposit in its third argument:

=FV(5%/12,120,-100,-10000)

This starts with 10,000, adds 100 at the end of every month for 10 years, and compounds monthly at 5%. The result is about 31,998.32. You paid in 22,000 in total (10,000 plus 120 deposits of 100), so roughly 9,998 is interest. Both the deposit and the starting amount are negative because they leave your pocket. The FV function guide explains each argument, including how to handle deposits at the start of the month.

How to find the effective annual rate#

The effective annual rate is the yearly growth you really get once compounding is included. For 5% compounded monthly:

=(1+5%/12)^12-1

This returns about 5.12%. Excel also has EFFECT for this: =EFFECT(5%,12). It's useful when you compare a savings account that compounds monthly with one that compounds yearly.

Common compound interest mistakes#

Check these when your answer looks off:

1. Rate typed as 5 instead of 5%. Excel reads 5 as 500%.
2. Rate not divided by n. Monthly compounding needs r/12 as well as 12*t periods.
3. Missing brackets around n*t. ^B3*B4 is not the same as ^(B3*B4).
4. No $ on the rate in a table. The rate reference slides down and later rows show 0 growth.

Compound interest works in reverse for loans. To see the payment that pays off a loan with interest, use the PMT function, and to see how each payment splits into interest and principal, build a loan amortization schedule. These calculations are core Excel skills for finance jobs.

Practise it#

Compound interest questions test brackets, powers and absolute references all at once. Practise relative and absolute references in a live spreadsheet: you type the formula and it checks it instantly. Then practise ROUND to finish financial results to the cent, and try the finance Excel test when you're ready.