Learning how to calculate compound interest in Excel is one of the most valuable financial skills you can have. Whether you’re calculating investment growth, loan costs, or savings goals — Excel makes it fast and accurate. In this complete guide, you’ll learn 3 methods with real formulas, step-by-step examples, and ready-to-use templates.
=P*(1+r/n)^(n*t)Where: P = Principal | r = Annual Rate | n = Compounding periods/year | t = Years
What Is Compound Interest?
Compound interest is interest calculated on both the initial principal AND the accumulated interest from previous periods. Unlike simple interest (which only earns on the original amount), compound interest grows exponentially — which is why Einstein reportedly called it the “eighth wonder of the world.”
Method 1 — Manual Compound Interest Formula in Excel
The standard formula to calculate compound interest in Excel is:
Excel Formula:
=P*(1+r/n)^(n*t)
Or with cell references: =B2*(1+B3/B4)^(B4*B5)
Step-by-Step: Set Up Your Excel Sheet
Set up your spreadsheet like this:
Result: $10,000 at 8% annual rate, compounded monthly for 5 years = $14,898.46
Method 2 — Excel FV Function (Easiest Method)
Excel has a built-in FV (Future Value) function that calculates compound interest automatically. This is the easiest method for most users.
FV Function Syntax:
=FV(rate, nper, pmt, [pv], [type])
Compound Interest Example:
=FV(8%/12, 5*12, 0, -10000)
Result: $14,898.46 (same as manual formula)
FV Function Parameters Explained
Method 3 — Year-by-Year Compound Interest Table in Excel
This method shows how your money grows each year — perfect for presentations and financial planning. Create this table in Excel:
Put the interest rate in cell D1. Then drag formulas down for as many years as needed. This creates a complete amortization-style schedule.
Compound Interest Formulas for Different Compounding Frequencies
The compounding frequency significantly affects your final amount. Here are the Excel formulas for each:
Monthly Compound Interest Formula in Excel
Monthly compounding is the most common for savings accounts and loans. Here’s the exact formula:
Monthly Compound Interest Formula:
=Principal * (1 + AnnualRate/12)^(Years*12)
Real Example ($5,000 at 6% for 3 years):
=5000*(1+0.06/12)^(3*12)
Result: $5,983.40
Compound Interest with Regular Monthly Contributions in Excel
Want to calculate how much you’ll have if you invest regularly each month? Use the FV function with a payment amount:
FV with Regular Monthly Contributions:
=FV(rate/12, years*12, -monthly_payment, -initial_investment)
Example: $10,000 initial + $500/month at 8% for 20 years:
=FV(8%/12, 20*12, -500, -10000)
Result: $318,204.34
This is the power of compound interest + regular investing. A $10,000 lump sum plus $500/month at 8% grows to over $318,000 in 20 years.
Rule of 72 — How Long to Double Your Money in Excel
The Rule of 72 is a quick mental math trick to estimate how many years it takes to double your investment at a given compound interest rate.
=72/interest_rateMore precise Excel formula:
=LOG(2)/LOG(1+rate)Reverse Compound Interest — Find the Principal (PV Function)
Want to know how much you need to invest today to reach a future goal? Use Excel’s PV function:
PV Function (Reverse Compound Interest):
=PV(rate/n, years*n, 0, future_value)
Example: How much to invest now to have $50,000 in 10 years at 7%?
=PV(7%/12, 10*12, 0, -50000)
Result: $24,991.76 (invest ~$25K today)
5 Common Mistakes When Calculating Compound Interest in Excel
- Using annual rate without dividing by n — Always divide your annual rate by the number of compounding periods:
rate/12for monthly - Forgetting to multiply years by n — Use
years*12for monthly compounding, not justyears - Positive vs negative signs in FV — In FV function, the present value (pv) must be negative (money you’re paying out)
- Confusing nominal and effective rates — The formula uses nominal rate. Use EFFECT() function to convert to effective annual rate
- Wrong cell format — Make sure rate cells are formatted as percentages or decimals correctly (0.08 = 8%)
Excel Compound Interest Template — Copy This Setup
Here’s a ready-to-use Excel layout you can copy directly into your spreadsheet:
Don’t want to build it yourself? Use our free online compound interest calculator that does this instantly — no Excel needed.
Effective Annual Rate (EAR) in Excel
The Effective Annual Rate tells you the real return after compounding. Excel has a built-in function for this:
EFFECT Function:
=EFFECT(nominal_rate, npery)
Example: 8% nominal rate, monthly compounding:
=EFFECT(8%, 12)
Result: 8.30% effective annual rate
Real-World Examples of Compound Interest in Excel
Example 1: Savings Account Growth
$5,000 deposited in a savings account at 4.5% annual interest, compounded monthly, for 10 years:
Example 2: Credit Card Debt
$3,000 credit card balance at 24% APR, compounded daily, if you make no payments for 2 years:
Example 3: Retirement Planning
$500/month investment at 10% annual return for 30 years (using FV function):
Frequently Asked Questions
What is the formula for compound interest in Excel?
The basic compound interest formula in Excel is =P*(1+r/n)^(n*t) where P is principal, r is annual interest rate, n is compounding periods per year, and t is years. Alternatively, use the built-in FV function: =FV(rate/n, years*n, 0, -principal).
How do I calculate monthly compound interest in Excel?
For monthly compounding, use =P*(1+r/12)^(t*12). Example: $10,000 at 8% for 5 years monthly: =10000*(1+0.08/12)^(5*12) = $14,898.46. The key is dividing the annual rate by 12 and multiplying the years by 12.
What is the difference between FV and compound interest formula in Excel?
Both give the same result. The manual formula =P*(1+r/n)^(n*t) is more transparent and easier to understand. The FV function is more efficient and can also handle regular periodic payments (like monthly deposits). FV is preferred by finance professionals for its versatility.
How do I calculate compound interest in Excel with monthly deposits?
Use: =FV(annual_rate/12, years*12, -monthly_deposit, -initial_investment). Example: $10,000 initial + $500/month at 8% for 20 years: =FV(8%/12, 20*12, -500, -10000) = $318,204.
Why is my FV function returning a negative number?
This happens when the present value (pv) is entered as a positive number. In Excel’s FV function, the present value must be negative because it represents money you’re paying out. Change 10000 to -10000 in your formula.
How do I calculate compound interest for daily compounding in Excel?
For daily compounding, use n=365: =P*(1+r/365)^(365*t). Example: $10,000 at 8% daily for 5 years: =10000*(1+0.08/365)^(365*5) = $14,917.71. Daily compounding gives slightly more than monthly.
Related Calculators
- Percentage Calculator — Calculate interest rates and percentages instantly
- Reverse Sales Tax Calculator — Remove tax from any total amount
- Break Even Calculator — Calculate your business break even point
- Commercial Loan Calculator — Calculate loan payments and total interest
- Startup Valuation Calculator — Estimate your startup value