Excel Productivity Guide

Useful Excel Tips | Keyboard Shortcuts | VBA Codes

**Download this Free E-Book **to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

**Today, I will show you how to calculate compound interest in excel.**

Compound interest is one of the most important financial calculations.

And, you can calculate it on a yearly, quarterly, monthly, and daily basis.

In this post, you will learn all these methods.

End of this post, you can download a simple template to calculate compound interest.

Here are the steps.

For calculating yearly compound interest, you just have to add interest of the one year into next year’s principal amount to calculate the interest of the next year. And, the formula in excel for yearly compound interest will be.

**=Principal Amount*((1+Annual Interest Rate/1)^(Total Years of Investment*1)))**

Let me show you an example.

In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16105.

In the first year, we get $10000*10% which is $1000 & in the second year, ($10000+$1000)*10% = $1100 & same is for 5 years.

Calculating quarterly compound interest is just like calculating yearly compound interest. But, here we have to calculate interest four times in a year.

Interest amount for each quarter will add to the principal amount for the next quarter.To calculate the quarterly compound interest you can use the below-mentioned formula.

**=Principal Amount*((1+Annual Interest Rate/4)^(Total Years of Investment*4)))**

Here is an example.

In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16386.

In the first quarter, we get 10000*(10%/4) which is $250 & in the second quarter, ($10000+$250)*(10%/4) = $256 & same is for 20 Quarters (5 years).

While calculating monthly compound interest we have the same basis as we have used in other time periods.

Just we have to calculate the interest at the end of each month. And, in this method interest will divide by 12 for a monthly interest rate.

To calculate the monthly compound interest you can use the below-mentioned formula in excel.

**=Principal Amount*((1+Annual Interest Rate/12)^(Total Years of Investment*12)))**

In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16453.

In the first month, we get 10000*(10%/12) which is $83.33 & in the second month, ($10000+$83.33)*(10%/12) = $84.02 & same is for 60 months (5 years).

While calculating daily compound interest again we have to use the same method with below-mentioned calculation formula.

We have to divide interest rate with 365 to get a daily interest rate.

So, you can use below formula to calculate daily compound interest.

**=Principal Amount*((1+Annual Interest Rate/365)^(Total Years of Investment*365)))**

In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16486.

In the first day, we get 10000*(10%/365) which is $4 & in the second day, ($10000+$4)*(10%/365) = $4 & same is for every day for 5 years.

*By using above methods, I have created a cumulative interest calculator to calculate all of the above calculations for interest in a single worksheet.*

I hope now you know how to calculate compound interest in excel.

This calculator will help you to calculate compound interest instantly.

You can use this template to calculate yearly, half-yearly, quarterly, monthly & daily compound interest rate.

Now, It’s your turn.

*Is there any other method to calculate compound interest in excel?*

Let me know in the comment box.

- Brenda G
- Puneet Gogia
- Brenda G
- Puneet Gogia