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:

Excel monthly compound interest formula

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

2 Replies to “How to Calculate Monthly Compound Interest in Excel”

  1. Hi Zach

    Thanks for the info. I am not an Excel wizard!
    I understand the formula!
    A = P(1 + r/12)12t which gives me the amount of interest over years,

    But, how do I calculate the compounded interest for say, only 16 months?

    Appreciate.

  2. Help!!! I wanted find the cumulative interest for a CD in months. I changed the term from 10 years to 120 months in your example and modified the formula to =B1*(1+B2/B3)^(B3*B4/12). That seemed to work fine. I then wanted to include both yearly and monthly breakdowns by modifying the formulas to
    =B1*(1+B2/B3)^(B3*D1/12) and =E2*(1+($B$2/12)/$B$3)^($B$3),””),
    but the total monthly interest after 120 months was higher than the total cumulative interest. Also I was not able to figure a way to show yearly growth. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *