Compound interest is one of the most important financial calculations which most of us often use.

And, it’s must to learn to calculate it in Excel.

But before you do that...

...you need to understand what actually compound interest is.

Compound interest is interest calculated on the initial principal and also on the accumulated interest of previous periods of a deposit or loan.

In Excel, the method to calculate compound interest is simple.

Now, the thing is.

You just need to use a calculation method and specify the time period for which you want to calculate.

So today, in this post, I’d like to show you how to calculate compound interest in Excel using different time periods.

So let’s get started.

## Yearly Compound Interest Formula

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 and 10% interest for 5 years, you will get $16105.

In the first year, you will get $10000*10% which is $1000 and in the second year, ($10000+$1000)*10% = $1100 an so on.

## Quarterly Compound Interest Formula

Calculating quarterly compound interest is just like calculating yearly compound interest.

But, here you need 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 and 10% interest for 5 years, we will get $16386.

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

## Monthly Compound Interest Formula

While calculating monthly compound interest you need to use basis as you have used in other time periods.

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

To calculate the monthly compound interest in Excel, you can use below formula.

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

In above example, with $10000 of principal amount and 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 and same is for 60 months (5 years).

## Daily Compound Interest Formula

While calculating daily compound interest again we have to use the same method with below 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 and 10% interest for 5 years, we will get $16486.

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

## Sample File

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

You can download it from here to learn more.

Good stuff but … what formula would you use if you wanted simple interest compounded annually for a specific period of days such as 180 or 400 days. I had the idea it would be =Principal Amount*((1+Annual Interest Rate/1)^((Total Number of Days/365)*1)))

Compared to ((Principal Amount * Annual Interest Rate)/365)*Total Number of Days it’s close but only equal at exactly 365 days. That is a math mystery to me.

Wondorful, so easy.. thanks for posting

Another good example, Thanks.

can you do some on using goal seek/solver for things that are not interest rates?

Hi Puneet.

Why did you choose not to use the Excel built in financial functions?

Thanks

Adrian

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!

Thanks Brenda for pointing me out.

Just updated the link. Now you can download the file.

Excellent, thank you!

Welcome Brenda.