Excel Compound Interest Formula: Calculate Growth Over Time

Use an Excel compound interest formula to estimate how a balance grows when interest is added to the amount already invested.

The key is matching the formula to your compounding schedule—and accounting separately for any regular deposits or withdrawals.

What is the Excel compound interest formula?

For a single starting balance with no additional contributions, the standard compound interest formula is:

=P*(1+r/n)^(n*t)

• P is the initial principal, or starting balance.
• r is the annual interest rate as a decimal.
• n is the number of compounding periods per year.
• t is the number of years.

In Excel, the caret (^) raises a value to a power.

For example, if the annual rate is 5%, enter it as 5% or 0.05.

Both represent the same rate.

Calculate compound interest with cell references

Set up a small input table so you can change assumptions without rewriting the formula:

Enter this formula in A6:

=A2*(1+A3/A4)^(A4*A5)

With the example values, the ending balance is approximately 1647.01.

The interest earned is the ending balance minus the principal.

To calculate that separately, use =A6-A2, which gives about 647.01.

These results assume a fixed 5% nominal annual rate, monthly compounding, no fees or taxes, and no deposits or withdrawals.

Format the input and result cells as Currency or Number with two decimal places if you want a familiar presentation.

Use Excel’s FV function for compound growth

Excel’s FV function calculates the future value of an investment using a constant interest rate and regular payments.

Its syntax is:

=FV(rate, nper, pmt, [pv], [type])

For the same $1,000 starting balance, 5% annual rate, monthly compounding, and 10-year period, use:

=FV(5%/12, 10*12, 0, -1000)

This gives approximately 1647.01.

The monthly rate is the annual rate divided by 12, and the total number of periods is 10 times 12.

The starting value is negative because Excel’s financial functions use cash-flow signs: money invested is an outflow, while money received is an inflow.

The result is positive under this convention.

When using cell references, the equivalent formula is =FV(A3/A4, A5*A4, 0, -A2).

Here, A4 contains the number of compounding periods per year.

Use FV when you want a standard financial function or need to include regular payments; use the direct formula when explaining the growth of a single deposit.

Include regular contributions

To model regular contributions, use FV and supply a payment amount.

Suppose you invest $100 at the end of each month for 10 years, earn 5% annually compounded monthly, and make no initial deposit:

=FV(5%/12, 10*12, -100, 0, 0)

This estimates a future value of about 15528.23.

The negative payment represents money paid into the investment.

The final argument, 0, means each contribution occurs at the end of the period.

Use 1 when contributions occur at the beginning of each month, as beginning-of-period deposits earn interest for an additional period.

If you also have an initial balance, include it as the pv argument.

For a $1,000 opening balance plus $100 deposited at the end of each month, use =FV(5%/12, 10*12, -100, -1000, 0).

Choose the right compounding frequency

The compounding frequency must reflect how the quoted rate is applied.

Common values for n include 1 for annual compounding, 2 for semiannual, 4 for quarterly, 12 for monthly, and 365 for daily.

For example, quarterly compounding over five years at 6% is:

=1000*(1+6%/4)^(4*5)

Check the terms behind the rate before choosing a frequency.

A quoted annual percentage yield may already account for compounding, while a nominal annual rate generally needs to be divided by the compounding periods per year.

Treating an effective annual yield as a nominal rate and dividing it again can overstate growth.

Calculate compound growth by year

A year-by-year table makes the calculation easier to audit.

Put the initial balance in B2, the annual rate in C1, and year numbers in A3:A12.

In B3, enter =B2*(1+$C$1), then fill the formula down.

The dollar signs keep the rate reference fixed while the balance reference advances by row.

This layout assumes annual compounding and no contributions. For monthly compounding, use =B2*(1+$C$1/12)^12 in B3, then fill it down. Each row represents one year of growth with 12 monthly compounding periods. If the rate or period count varies by year, place those assumptions in separate columns and reference the appropriate row values.

Common errors and edge cases

• Entering 5 instead of 5%: Excel interprets 5 as 500%.
  
  Enter 5% or 0.05 for a 5% rate.
• Mixing periods: The rate per period and total number of periods must use the same frequency.
  
  For monthly compounding, divide the annual rate by 12 and multiply years by 12.
• Using a zero or negative frequency: The number of compounding periods per year must be positive.
  
  A zero value makes the direct formula invalid because it divides the rate by zero.
• Confusing nominal and effective rates: Confirm whether the stated annual rate is nominal or already reflects compounding before applying a frequency adjustment.
• Ignoring cash-flow timing in FV: The optional type argument is 0 for end-of-period payments and 1 for beginning-of-period payments.
• Rounding too early: Keep full precision in intermediate calculations and format the displayed result to two decimal places.
  
  Rounding each period can cause a small difference from a single compound-growth calculation.
• Forgetting fees, taxes, or changing returns: The basic formula assumes a constant rate and no deductions.
  
  Use a period-by-period model when returns, contributions, fees, or withdrawals vary.

Excel and Google Sheets compatibility

The direct arithmetic formula uses standard operators and works in current desktop and web versions of Microsoft Excel, as well as Google Sheets.

The FV function is also supported in Excel and Google Sheets with the same core arguments.

If your spreadsheet uses a locale with semicolons as function argument separators, replace commas in the FV formula with semicolons.

Cell references and decimal separators can also vary by regional settings.

For a one-time deposit, use =P*(1+r/n)^(n*t) or the equivalent cell-reference formula.

For regular equal contributions, use FV and set the payment sign and timing to match how money moves in and out of the account.