# How to Calculate Monthly Compound Interest in Excel

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.