Compound interest grows a lump sum by applying the rate to an ever-larger balance. The formula is pure arithmetic — principal × (1 + rate)^periods — and FV does the same job with sign conventions built in.
(1 + rate) to the number of periods and multiply by the starting amount. For monthly compounding, use B2/12 and B3*12.
The example
$1,000 at 5% per year for 10 years, compounded annually.
| A | B | |
|---|---|---|
| 1 | Principal | $1,000 |
| 2 | Annual rate | 5% |
| 3 | Years | 10 |
| 4 | Future balance | $1,628.89 |
The formula
The balance after 10 years:
How it works
Each period multiplies the balance by (1 + rate):
(1 + B2)is the growth factor per period — 1.05 for 5%.- Raising it to the power
B3(^10) compounds that growth across all 10 years: 1.05^10 ≈ 1.629. - Multiplying by the principal
B1gives the ending balance —$1,628.89, of which $628.89 is interest. - For monthly compounding, divide the rate by 12 and multiply the periods by 12:
=B1*(1+B2/12)^(B3*12).
FV does the same thing. =FV(B2, B3, 0, -B1) returns the identical figure using Excel’s finance engine — handy when you also have regular contributions.
Try it: interactive demo
Adjust the inputs; see the compounded balance and total interest.
Variations
Monthly compounding
Divide the rate, multiply the periods:
Using FV
Same answer via the finance function:
Just the interest earned
Subtract the principal:
Pitfalls & errors
Rate as a whole number. 5% must be 0.05, not 5, or the balance explodes. Use a percent-formatted cell or divide by 100.
Match the rate to the compounding period. Monthly compounding needs the monthly rate (annual/12) and the month count (years×12).
Simple vs compound. principal × rate × years is simple interest — lower than compound. Use the power formula for compounding.
Practice workbook
Frequently asked questions
What is the compound interest formula in Excel?
How do I calculate monthly compound interest?
What's the difference between simple and compound interest?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class