How to Do Compound Interest in Excel

Compound interest is interest earned on both your original money and the interest it has already earned. Over time that snowball effect makes a big difference, and Excel can calculate it in seconds, whether you’re projecting savings, comparing accounts or checking a loan.

This guide shows the classic compound interest formula, the faster FV function, how to include regular contributions, how to build a year-by-year table, and how to handle changing rates.

How Compound Interest Works

Say you deposit $10,000 at 5% interest, compounded once a year. After the first year you earn $500, so the balance is $10,500. In the second year you earn 5% on $10,500, which is $525, not $500. In the third year you earn $551.25. Each year’s interest is a little bigger than the last because it’s calculated on a growing balance. More frequent compounding, such as monthly, makes the effect slightly stronger, because interest starts earning interest sooner.

The general formula is Future value = P × (1 + r/n)^n×t, where P is the principal, r is the annual rate, n is the number of compounding periods per year, and t is the number of years.

Set Up Your Inputs

Put each input in its own cell so you can change them later:

Cell	Input	Example
B1	Principal (starting amount)	10,000
B2	Annual interest rate	5%
B3	Years	10
B4	Compounding periods per year	12 (monthly)

Use 1 for annual, 4 for quarterly, 12 for monthly or 365 for daily compounding.

Method 1: Use the Compound Interest Formula

In B5, enter:

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

This returns the future value, $16,470.09 with the example inputs. To see just the interest earned, subtract the principal in another cell: =B5-B1, which gives $6,470.09.

Method 2: Use the FV Function

Excel’s FV function does the same calculation and handles regular deposits too:

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

• rate is the interest per period (annual rate divided by periods per year).
• nper is the total number of periods.
• pmt is a regular payment or deposit (0 here).
• pv is the starting amount, entered as a negative number because it’s money you pay in.

The result is again $16,470.09. Microsoft’s reference for the FV function explains each argument and the sign convention.

Add Regular Monthly Contributions

If you also deposit $100 every month, put the payment in the pmt argument as a negative number:

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

With the example inputs, the balance after 10 years is $31,998.32. Of that, $22,000 is money you put in ($10,000 plus 120 deposits of $100), and almost $10,000 is interest. The basic formula can’t handle contributions without extra math, which is why FV is usually the better choice.

Build a Year-by-Year Table

1. In D2:D12 enter years 0 to 10.
2. In E2, enter =$B$1 for the starting balance.
3. In E3, enter =$B$1*(1+$B$2/$B$4)^($B$4*D3) and fill it down to E12.
4. Select the table and choose Insert > Line Chart to see the growth curve.

The dollar signs keep the input cells fixed as you copy the formula down. Add a third column with =E3-E2 to show the interest earned each year, and you’ll see it rise steadily.

Compare Rates and Time Periods

To see how different rates change the outcome, list rates such as 3%, 4%, 5% and 6% down a column and use a one-variable data table pointed at B2, or simply copy the FV formula next to each rate. Doing the same with years shows how much difference starting earlier makes. For the data table approach, see how to do a data table in Excel.

Handle Changing Interest Rates

When the rate changes each year, list the rates in a range such as H2:H4 (4%, 5% and 6%) and use FVSCHEDULE: =FVSCHEDULE(B1,H2:H4). With $10,000, that returns $11,575.20 after three years.

Other Useful Calculations

• Annual compounding shortcut: =B1*(1+B2)^B3 returns $16,288.95 for the example.
• Continuous compounding: =B1*EXP(B2*B3) returns $16,487.21.
• Effective annual rate: =EFFECT(B2,B4) shows that 5% compounded monthly is about 5.12% a year.
• Time to reach a goal: =NPER(B2/B4,0,-B1,20000)/B4 returns the years needed to grow $10,000 to $20,000.

Troubleshooting

• FV returns a negative number. Enter pv and pmt as negatives, or wrap the result in ABS. Excel treats money you pay in and money you receive with opposite signs.
• The result is far too large. The rate was entered as 5 instead of 5%. Format B2 as a percentage or enter 0.05.
• Monthly results look wrong. Divide the annual rate by 12 and multiply the years by 12. Mixing annual rates with monthly periods is the most common mistake.
• #NUM! from NPER. The goal can’t be reached with the inputs, often because signs are mixed up.
• Copied formulas give odd values. Lock input references with $ signs.

Frequently Asked Questions

What’s the difference between simple and compound interest?

Simple interest is calculated only on the original principal. Compound interest is calculated on the principal plus interest already earned, so it grows faster.

Can I change the compounding frequency?

Yes. Change the periods per year in B4, for example to 4 for quarterly or 365 for daily.

Does this work for loans?

Yes. The same formulas show how a balance grows with interest. For monthly loan payments, use the PMT function instead.

Does this work in Google Sheets?

Yes. The formula, FV, FVSCHEDULE and EFFECT all work in Google Sheets.

Wrapping Up

Use =P*(1+r/n)^(n*t) or the FV function to calculate compound interest, subtract the principal to see the interest earned, and add a pmt value for regular deposits. Build a year-by-year table for a quick growth chart. For related finance formulas, see how to calculate bond price in Excel.