Md. Sourov Hossain Mithun
Calculating interest is a common task in banking, NGOs, and other financial organizations. We can do it very quickly and easily in Excel by using manual formulas or functions. From this article, you will learn various ways to use a formula to calculate monthly compound interest in Microsoft Excel with vivid illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
Compound Interest with Monthly Compounding Periods
Compound interest is the total interest that includes the original interest and the interest of the updated principal which is evaluated by adding the original principal to the due interest. It is the interest that you get both on your initial principal and on the interest you earn with the passage of each compounding period. And it is called monthly compound interest when the interest is compounded after each of the 12 months through the whole year. It’s also known as ‘Interest on interest’ and it grows an amount faster than simple interest.
Basic Mathematical Formula:
Where,
I = Compounded interest.
P = Original principal.
r = Interest rate in percentage per year.
n = Time in years.
Mathematical Example:
Suppose a borrower took a $5000 loan at a 10% annual interest rate for 5 years.
So according to the mathematical formula, the monthly compound interest will be-
3 Formulas to Calculate Monthly Compound Interest in Excel
Formula 1: Calculate Monthly Compound Interest Manually in Excel Using the Basic Formula
In this method, we’ll use the basic mathematical formula to calculate monthly compound interest in Excel.
Suppose a client borrowed $10000 at a rate of 5% for 2 years from a bank. Now let’s find the monthly compounded interest using the above formula in Excel.
Steps:
- Cell C5 contains the original principal (Present value). We’ll have to multiply this value with the interest rate. So type
=C5*
- In this case, as the interest is to be compounded monthly, so we need to divide the annual interest rate by 12.
- As the interest will be compounded 12 times in a year so we need to give a cell reference where the number of years is mentioned so that we can multiply 12 with several years. So the formula inside the cell becomes
=C5*(1+(C6/12))^(12*C7).
- Then we have subtracted Cell C5 which contains the original principal to get the interest. Finally, the formula becomes-
=C5*(1+(C6/12))^(12*C7)-C5
- Now just hit the Enter button.
From the following image, you can see that we have successfully calculated the monthly compound interest.
Read More: How to Use Compound Interest Formula in Excel
Formula 2: Use Excel FV Function to Calculate Monthly Compound Interest
The FV function returns the future value of an investment.
Syntax of FV Function:
=FV(rate,nper,pmt,[pv],[type])Arguments:
rate(required argument) – The interest rate per period.
nper (required argument) – The total payment periods.
pmt (optional argument) – It specifies the payment per period. If we avoid this argument, we will need to provide the PV argument.
[pv](optional argument) – It specifies the present value (PV) of the investment. If it is omitted, defaults to zero. If we omit it, we need to provide the Pmt argument.
[type] (optional argument) – It identifies whether the wages are created at the start or at the end of the year. It will be 0 if the wage is created at the end of the period or 1 if the wage is created at the start of the period.
Now, follow the steps below.
Steps:
- Firstly, we’ll have to specify the rate in the FV function. As we are looking for monthly compound interest, I have divided the annual rate by 12. So type
=FV(C6/12,in Cell C9.
- Then we’ll have to specify the total periods so I have multiplied the time in years(C7) by 12 for total monthly periods.
- As we are not adding any additional amount to the original principal value in between the investment period, that is why we will put ‘0’ for ‘pmt.’ So the formula inside the cell becomes
=FV(C6/12,C7*12,0,.
- Later, As we are investing $10000 as the original principal and we have omitted the value for ‘pmt’ that’s why I will use the cell reference of Cell C5 with a negative (-) sign for ‘PV.’ Hence, type
=FV(C6/12,C7*12,0,-C5).
- Finally, we’ll have to subtract the original principal from the future value to get the interest. So lastly, the formula will be-
=FV(C6/12,C7*12,0,-C5)-C5
- After that just press the Enter button for the result.
Similar ReadingsÂ
- A Daily Compound Interest Calculator in Excel (Template Attached)
- How to calculate compound interest for recurring deposit in Excel!
- Compound Interest Formula in Excel: Calculator with All Criteria
Formula 3: Apply Excel FVSCHEDULE Function to Calculate Monthly Compound Interest
The FVSCHEDULE function returns the future value of an investment with a variable interest rate.
Syntax of FVSCHEDULE Function:
=FVSCHEDULE(principal, schedule)Arguments:
Principal (required argument) – The present value of the investment.
Schedule (required argument) – The array of values that provides the schedule of interest rates to be applied to the principal.
We have modified the dataset here as in the image below. Now let’s apply the FVSCHEDULE function to calculate the monthly compound interest.
- At first, we’ll have to insert the present value in the FVSCHEDULE function. So type
=FVSCHEDULE(C5,in Cell C10.
- Now we’ll have to pet the schedule of interest rates as an array. For a year the monthly schedule is 12 times so I have divided the annual rate by 12 in Cell C9.
- Put this value 12 times as an array in the formula. Type
=FVSCHEDULE(C5,{0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005}).
- Finally, just subtract the original principal. So the final formula will be as follows-
=FVSCHEDULE(C5,{0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005,0.005})-C5
- At this moment, just click the Enter button for the result.
Conclusion
I hope all of the methods described above will be well enough to use a formula to calculate monthly compound interest in Excel. Feel free to ask any question in the comment section and please give me feedback.
Related Articles
