Formula for Monthly Compound Interest in Excel (With 3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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:

Formula for monthly compound interest in excel

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.

Formula for monthly compound interest 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*

Monthly Compound Interest Manually in Excel Using the Basic Formula

  • In this case, as the interest is to be compounded monthly, so we need to divide the annual interest rate by 12.

Monthly Compound Interest Manually in Excel Using the Basic Formula

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

Monthly Compound Interest Manually in Excel Using the Basic Formula

  • 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

Monthly Compound Interest Manually in Excel Using the Basic Formula

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

FV Function to Calculate Monthly Compound Interest

  • Then we’ll have to specify the total periods so I have multiplied the time in years(C7) by 12 for total monthly periods.

FV Function to Calculate Monthly Compound Interest

  • 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,.

FV Function to Calculate Monthly Compound Interest

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

FV Function to Calculate Monthly Compound Interest

  • 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

FV Function to Calculate Monthly Compound Interest

  • After that just press the Enter button for the result.


Similar Readings 


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.

FVSCHEDULE Function to Calculate Monthly Compound Interest

  • 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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo