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

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.

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.