Compound Interest Formula in Excel (With Annual and Monthly Examples)

The Excel compound interest formula is =P*(1+r/n)^(n*t), where P is the starting amount, r is the annual interest rate, n is the number of compounding periods per year, and t is the number of years. It returns the ending balance, including your starting amount. Subtract the starting amount to find interest earned.

For example, $1,000 at 5% interest compounded annually for 10 years becomes $1,628.89:

=1000*(1+5%)^10

The examples below use a constant annual rate and reinvest all interest. They exclude fees, taxes, withdrawals, and additional deposits unless a deposit is explicitly included.

Set Up the Compound Interest Inputs

Enter these labels in column A and values in column B:

Enter 5% or 0.05 in B3; both represent the same rate. Format B3 as Percentage and the balance cells as Currency with two decimal places. Enter numeric inputs without typing units such as “years” into the value cells.

With these inputs, the formula is ready to paste into another cell:

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

Here is how the calculation works:

• B3/B5 gives the rate for one compounding period.
• B5*B4 gives the total number of periods.
• ^ raises the growth factor to that number of periods.
• Multiplying by B2 converts the growth factor into an ending balance.

For this formula, the annual rate is a nominal annual rate: the stated rate before the effect of compounding within the year. An effective annual rate or APY already includes that effect, so do not divide an APY by 12 and use it as the monthly rate.

You can download the compound interest example workbook to change the four inputs and compare the manual formula with FV.

Calculate Annual Compound Interest

Annual compounding adds interest once per year, so keep B5 set to 1. The general formula then simplifies to:

=B2*(1+B3)^B4

After the first year, the $1,000 starting amount earns $50, giving a balance of $1,050. In year two, interest is calculated on $1,050, so it earns $52.50. That growing interest base is what makes the calculation compound.

To build a small results section, enter these formulas:

The interest-only calculation uses ordinary subtraction: ending balance minus principal. If you are new to cell references, the guide on how to subtract in Excel explains that pattern with simpler examples.

You can also calculate interest earned directly without a separate balance cell:

=B2*(1+B3/B5)^(B5*B4)-B2

Calculate Monthly Compound Interest

Change B5 from 1 to 12. The formula in B8 stays the same and now returns $1,647.01. Interest earned in B9 becomes $647.01.

The equivalent formula with monthly frequency written directly is:

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

For the example inputs:

=1000*(1+5%/12)^(12*10)

There are 120 monthly periods in 10 years. Each period uses 5%/12, approximately 0.416667%. Leave that division inside the formula so Excel retains the full precision.

Both the rate and the period count must use months. Dividing the rate by 12 while leaving the exponent at 10 would calculate only 10 months of growth.

Use the FV Function Instead

Excel's FV function calculates future value. For the same lump sum, enter this formula in B10:

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

Its arguments mean:

The negative starting deposit makes the returned future balance positive. With B5 set to 12, the result is $1,647.01, matching the manual formula. With B5 set to 1, both return $1,628.89.

Microsoft's FV function documentation explains the cash-flow signs and the need to use matching units for the rate and number of periods. It lists support for Microsoft 365 on Windows and Mac, Excel 2024, Excel 2021, Excel 2019, and Excel 2016.

Compare Annual, Quarterly, Monthly, and Daily Compounding

The following results all use a $1,000 principal, a 5% nominal annual rate, and 10 years. Only the frequency changes.

To reproduce this comparison alongside your inputs, type the frequencies 1, 4, 12, and 365 into E3:E6. Enter the manual formula in F3 and copy it down:

=$B$2*(1+$B$3/E3)^(E3*$B$4)

For the matching FV results, enter this in G3 and copy down:

=FV($B$3/E3,E3*$B$4,0,-$B$2)

The dollar signs keep the principal, rate, and years fixed while the frequency reference changes with each row. Format columns F and G as Currency to compare the results.

For a positive nominal rate, more frequent compounding produces a higher ending balance because interest begins earning interest sooner. The daily row assumes exactly 365 compounding periods per year; it does not model actual calendar dates or a bank's day-count rules.

Add Regular Monthly Deposits with FV

If you also deposit $100 at the end of every month, use:

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

With the same $1,000 starting amount, 5% annual rate, and 10 years, the ending balance is $17,175.24. Total contributions are $13,000: the initial $1,000 plus 120 deposits of $100. Interest earned is therefore $4,175.24.

The final argument, 0, places deposits at the end of each month. Change it to 1 for deposits at the beginning of each month; the balance then becomes $17,239.94 because each monthly deposit earns one extra month's interest.

This example assumes both deposits and compounding occur monthly. Microsoft's payments and savings formula guide shows how FV combines a starting balance with regular deposits using negative values for money paid in.

Simple Interest vs Compound Interest

Simple interest uses the original principal for every period. Compound interest includes previously earned interest in the balance used for the next calculation.

For simple interest, calculate the ending balance with:

=B2*(1+B3*B4)

The extra $128.89 in the annual compound example comes from interest earning further interest. This comparison assumes no additional deposits for either method.

Common Compound Interest Formula Mistakes

Entering 5 instead of 5%

The formulas need the rate as a decimal fraction. A stored value of 5 means 500% in the calculation. Enter 5% explicitly to avoid ambiguity.

Mixing annual rates with monthly periods

For monthly compounding, use B3/12 for the rate and B4*12 for the periods. Change both together. Also check that B5 is a positive number; zero produces a division-by-zero error.

Getting a negative FV result

If the starting amount is positive inside FV, Excel returns a negative future value for this lump-sum example. Use -B2 for the initial deposit and a negative payment for any regular deposits.

Rounding the periodic rate too early

Keep B3/B5 intact. Rounding that rate before compounding changes the ending balance.

Currency formatting displays two decimals while retaining the underlying value. If you need the final calculated value itself rounded to cents, use the ROUND function:

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

Round the final output when needed, rather than every intermediate step. An account that rounds each interest credit can produce a slightly different balance from this unrounded mathematical model.

Frequently Asked Questions

What is the monthly compound interest formula in Excel?

Use =B2*(1+B3/12)^(12*B4) with the principal in B2, nominal annual rate in B3, and years in B4. For $1,000 at 5% over 10 years, the ending balance is $1,647.01.

How do I calculate only the compound interest earned?

For a lump sum, subtract the principal from the ending balance: =B2*(1+B3/B5)^(B5*B4)-B2. If you make additional deposits, subtract those contributions too when calculating interest earned.

Does this formula work if the interest rate changes?

The single-rate formula assumes the same rate throughout. For changing rates, use a schedule with one row per period, applying that period's rate to the previous closing balance. Keep the rate's units consistent with each row's time period.

Can I use the formula as a compound interest calculator?

Yes. Keep B2:B5 as editable inputs and use the manual and FV formulas in separate result cells. The example workbook includes this compact setup and a comparison across compounding frequencies.