Shares

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.

# How To Calculate Compound Interest In Excel

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.

## Yearly Compound Interest

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.

## Quarterly Compound Interest

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).

## Monthly Compound Interest

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).

## Daily Compound Interest

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.

## Conclusion

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

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

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

Let me know in the comment box.

• Brenda G

Hello, Puneet! This is another wonderful article. However, I tried to download the sample file, but the link is not working. Can you check it? Thank you!

• Puneet Gogia

Thanks Brenda for pointing me out.