How to Calculate Compound Interest in Excel (Formulas and Examples)
Compound interest is the engine for multiplying assets in fixed income and variable income investments. Known as "interest on interest", they cause each month's income to be added to the principal amount to yield even more in the following period.
In this guide, you will learn exactly how to apply the compound interest mathematical formula in Microsoft Excel and Google Sheets.
1. The Mathematical Formula for Compound Interest
The classic formula for compound interest is:
$M = P \times (1 + i)^n$
Where:
- M: Accumulated final amount.
- P: Initial capital (Main).
- i: Interest rate per period (ex: 1% per month = 0.01).
- n: Total number of periods (months or years).
2. How to Use the VF Function in Excel
Microsoft Excel has a native function ready to calculate compound interest with monthly contributions: the VF (Future Value) function.
VF Function Syntax:
=VF(taxa, nper, pgto, [vp], [tipo])
- rate: Monthly interest rate (ex:
1%). - nper: Number of months (ex:
12). - payment: Fixed monthly contribution value (use negative value, e.g.
-500). - vp: Initial investment (use negative value, e.g.
-1000).
Practical Example of Formula:
If you invest $ 1.000 de início e aportar $ 500 per month at a rate of 1% per month for 24 months, the formula in Excel is:
=VF(1%; 24; -500; -1000)
Excel result: $ 15.656,58 (sendo $ 13,000 invested and $2,656.58 in pure interest).
3. Assembling the Month by Month Table in Excel
If you prefer to view the evolution of your assets month by month:
- Column A: Month (1, 2, 3...)
- Column B (Initial Balance): In month 1, enter the initial value (ex:
=1000). In the following months,=E1(Final Balance of the previous month). - Column C (Contribution): Fixed monthly amount (ex:
500). - Column D (Interest for the Month):
=(B2 + C2) * 1% - Column E (Final Balance):
=B2 + C2 + D2
Drag the line down to line 24 to watch your money grow!
4. Instant Online Simulator
Don't want to open Excel? Perform unlimited simulations with evolution graphs in our Compound Interest Calculator.