We can use the following formula to find the ending value of some investment after a certain amount of time:
A = P(1 + r/n)nt
where:
- A:Â Final Amount
- P:Â Initial Principal
- r:Â Annual Interest Rate
- n:Â Number of compounding periods per year
- t:Â Number of years
If the investment is compounded monthly, then we can use 12 for n:
A = P(1 + r/12)12t
The following example shows how to use this formula in Excel to calculate the ending value of some investment that has been compounded monthly.
Example: Monthly Compound Interest Formula in Excel
Suppose we invest $5,000 into an investment that compounds at a rate of 6% annually. Assume the investment compounds on a monthly basis.
The following screenshot shows how to use the compound interest formula in Excel to calculate the ending value of this investment after 10 years:
This investment will be worth $9,096.98 after 10 years.
The following screenshot shows how to calculate the ending investment after each year during the 10-year period.
Note: Column F shows the formula we used in each corresponding cell in Column E:
From the output we can see:
- At the end of year 1, the investment is worth $5,308.39.
- At the end of year 2, the investment is worth $5,635.80.
- At the end of year 3, the investment is worth $5,983.40.
And by the end of year 10, the investment is worth $9,096.98.
To visualize the investment growth over time, highlight the cells in the range E2:E11, then click the Insert tab along the top ribbon, then click the 2-D Column Chart option with the Charts group:
The x-axis shows the year and the height of the bars represent the investment value at the end of each year.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Find the Antilog of Values in Excel
How to Solve a System of Equations in Excel
How to Calculate a Five Number Summary in Excel