Compound interest is one of the most important financial calculations that most of us often use, and it’s a 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.Investopedia
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 the 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 the 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, and 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 a year. The 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 the 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 the same calculation method for 20 Quarters (5 years).
Monthly Compound Interest Formula
While calculating the monthly compound interest you need to use the basis 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 the below formula.
=Principal Amount*((1+Annual Interest Rate/12)^(Total Years of Investment*12)))
In the 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 the 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 the below calculation formula. We have to divide the interest rate by 365 to get a daily interest rate.
So, you can use the below formula to calculate daily compound interest.
=Principal Amount*((1+Annual Interest Rate/365)^(Total Years of Investment*365)))
In the above example, with $10000 of principal amount and 10% interest for 5 years, we will get $16486.
On the first day, we get 10000*(10%/365) which is $4, and on the second day, ($10000+$4)*(10%/365) = $4, and the same is for every day for 5 years.
By using the above methods, I have created a cumulative interest calculator [Template] to calculate all of the above calculations for interest in a single worksheet.
Get the Excel FileDownload
- Calculate Cube Root in Excel
- Calculate Percentage Variance (Difference)
- Calculate Simple Interest in Excel
- Calculate SQUARE ROOT in Excel
- Calculate the Weighted Average in Excel
- Does Not Equal Operator in Excel
- Round a Number to Nearest 1000, 100, and 10
- Round to Nearest .5, 5. 50 (Down-Up) in Excel
- Square a Number in Excel
- Round a Number to Nearest 1000 in Excel
- Add-Subtract Percentage from a Number
- Average But Ignore Errors
- Average Number but Exclude Zeros
- Average Only Non-Blank Cells
- Calculate the Average of the Percentage Values
- Calculate the Average of the Time Values
- Calculate the Cumulative Sum of Values
- Round Percentage Values
16 thoughts on “How to Calculate Compound Interest in Excel”
Thank you so much for this article. It’s very helpful. Can you please share me a guide on how to create that template “sample-files.xlsx”.
i love your excel-stuff.
I need a little hint to get my problem solved.
Let me give an example:
my initial deposit is 5000 $
my MONTHLY Interest Rate is 5% (which should be about 70something percent per year)
i invest 300 Bucks every beginning of month
with monthly compounding
i want to know what total i have after 5 Years
I want to do this in excel, completly, no VBA.
Any idea how to accomplish that challenge?
like this one:
Good work Puneet. Helpful.
Puneet, In compound interest calculations, you haven’t made allowance for leap years – in a five-year investment period, there can be either one or two extra days in which interest accrues and may need to be taken into account. One way around this is to count a year as 365.25 days. This won’t be exact, but it’s closer than 365. Another way is to have two variables, Year and Leap_Year. The amount, of course, isn’t going to be vastly different from the simpler calculation. I did calculate the exact formula some years ago (using the amounts earned on a bank deposit to check on the calculations) as an exercise, and was able to follow the interest earned to within a cent over some months. I’ve lost the figures since then (and have also lost the motivation to carry out the exercise again!), but it wasn’t difficult to get exact figures.
Hey Puneet, your sample download file(https://excelchamps.com/wp-content/uploads/2019/06/excel-compound-interest.xlsx) has a the following malware:
Suggest you create another file so that it doesn’t mess up peoples PCs.
Otherwise great job!
These calculations only work if you consider that the interest rate is not recalculated to be equivalent.
With a aer, the periodicity doesn’t matter for the final capitalisation.
Sir, 26th Sep,2019.
Very valuable and easy solution with description you have given and easy to understand too.
A classical work
S. K. Shrivastava
Sir, 15th July,2019.
Very valuable and easy sollution you have given and easy to undrstand too.
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?
Why did you choose not to use the Excel built in financial functions?
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!