Compound Interest Formula in Excel

Master compound interest calculations in Excel to accurately project investment growth, savings returns, and financial planning scenarios. This comprehensive guide covers the FV function, manual formulas, and real-world examples to help you understand how money grows over time.

1,900 searches/month
Financial
What is Compound Interest?

Compound interest is the interest calculated on both the initial principal and the accumulated interest from previous periods. Unlike simple interest which only calculates on the principal, compound interest creates exponential growth over time—often called "interest on interest." This is the fundamental concept behind wealth building and why Albert Einstein allegedly called it "the eighth wonder of the world."

In Excel, you can calculate compound interest using the built-in FV (Future Value) function or create manual formulas for more complex scenarios. The compound interest formula in Excel helps investors, financial planners, and anyone managing money understand how investments will grow over time.

Key Concept:

The power of compound interest lies in time and frequency. The more frequently interest compounds (daily vs. annually) and the longer the investment period, the more dramatic the growth.

Compound Interest Formula in Excel
Two primary methods to calculate compound interest in Excel

Method 1: Using the FV Function (Recommended)

=FV(rate, nper, pmt, [pv], [type])
rate
Interest rate per period (annual rate / compounding periods per year)
nper
Total number of payment periods (years × compounding periods per year)
pmt
Payment made each period (use 0 if no regular payments)
pv
Present value (initial investment, entered as negative number)
type
0 = payments at end of period, 1 = beginning (optional, defaults to 0)

Method 2: Manual Compound Interest Formula

=P * (1 + r/n)^(n*t)
P
Principal amount (initial investment)
r
Annual interest rate (as decimal, e.g., 0.05 for 5%)
n
Number of times interest compounds per year
t
Number of years

In Excel: =A1*(1+B1/C1)^(C1*D1)

Step-by-Step Examples
Real-world compound interest calculations in Excel

1Investment Growth Calculator

Calculate the future value of a $10,000 investment at 6% annual interest, compounded monthly for 10 years.

Given:

  • • Principal (P) = $10,000
  • • Annual Rate (r) = 6% = 0.06
  • • Compounding = Monthly (n = 12)
  • • Time (t) = 10 years

Using FV Function:

=FV(0.06/12, 10*12, 0, -10000)

Result: $18,194.25

Your $10,000 investment grows to $18,194.25, earning $8,194.25 in compound interest over 10 years.

2Savings Account Growth with Regular Deposits

Calculate savings growth with $5,000 initial deposit, $200 monthly contributions, 4% annual interest compounded monthly over 5 years.

=FV(0.04/12, 5*12, -200, -5000)

Result: $18,584.43

Total contributions: $5,000 + ($200 × 60 months) = $17,000. Interest earned: $1,584.43

3Comparing Compounding Frequencies

See how $1,000 grows at 5% for 20 years with different compounding frequencies:

FrequencyFormulaFuture Value
Annual (n=1)=FV(0.05/1, 20*1, 0, -1000)$2,653.30
Quarterly (n=4)=FV(0.05/4, 20*4, 0, -1000)$2,701.48
Monthly (n=12)=FV(0.05/12, 20*12, 0, -1000)$2,712.64
Daily (n=365)=FV(0.05/365, 20*365, 0, -1000)$2,718.03

Notice how more frequent compounding leads to higher returns. Daily compounding yields $64.73 more than annual compounding over 20 years.

Common Use Cases
  • Retirement Planning:Calculate how 401(k) or IRA investments grow over 20-40 years with compound interest
  • College Savings (529 Plans):Project education fund growth with regular monthly contributions
  • Investment Analysis:Compare different investment options with varying interest rates and compounding frequencies
  • Certificate of Deposit (CD) Returns:Calculate exact returns on fixed-term deposits with different compounding schedules
  • Business Financial Projections:Model revenue growth, loan costs, or investment fund performance for business planning
  • Debt Payoff Analysis:Understand how credit card debt or loans grow when minimum payments don't cover interest
Compound Interest vs. Simple Interest

Compound Interest

Interest calculated on principal + accumulated interest. Growth is exponential.

=FV(rate, nper, 0, -principal)

Simple Interest

Interest calculated only on principal. Growth is linear.

=principal * (1 + rate * years)

Example: $10,000 at 5% for 20 years

  • • Compound Interest (annual): $26,533.00 (Formula: =FV(0.05, 20, 0, -10000))
  • • Simple Interest: $20,000.00 (Formula: =10000*(1+0.05*20))
  • • Difference: $6,533.00 more with compound interest!
Pro Tips for Excel Compound Interest Calculations
  • 💡
    Use cell references instead of hardcoded values - This makes your spreadsheet dynamic and allows for easy scenario analysis. Put principal in A1, rate in B1, years in C1, etc.
  • 💡
    Enter present value (pv) as negative - In the FV function, enter initial investment as a negative number to get a positive future value result (accounting convention).
  • 💡
    Convert percentages to decimals - Always divide interest rates by 100 or enter them as decimals (5% = 0.05) for accurate calculations.
  • 💡
    Match period consistency - If rate is monthly (annual rate/12), then nper must be in months (years×12). Mismatched periods are a common error.
  • 💡
    Use data tables for scenario analysis - Create Excel data tables to see how changing interest rates or time periods affects your investment growth.
  • 💡
    Calculate effective annual rate - Use formula =(1+rate/n)^n-1 to find the true annual return when interest compounds more frequently than yearly.
Related Excel Financial Formulas
FV FunctionPV (Present Value)PMT (Payment)RATE FunctionCAGR FormulaProfit Margin

Need a Custom Compound Interest Calculator?

Use our AI-powered formula generator to create tailored compound interest formulas for your specific scenario

Generate Custom Formula Free