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

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.

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

Formulas for Monthly Compound Interest in Excel: 3 Suitable Ways

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, we need to divide the annual interest rate by 12.

As the interest will be compounded 12 times in a year 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 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.

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. Lastly, the formula will be

=FV(C6/12,C7*12,0,-C5)-C5

After that, press the Enter button for the result.

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.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Calculate Monthly Compound Interest.xlsx

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.

<< Go Back to Compound Interest in Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Emely Phiri Oct 18, 2023 at 12:39 AM

How can you calculate monthly interest where you have the principal and every month you save ?
- Reply
  Raiyan Zaman Adrey Oct 18, 2023 at 1:43 PM
  
  Dear Emely Phiri,
  Thanks for reaching out to us. Let’s clear your confusion.
  Think of it this way, you already know how one can calculate annual interest when one has the principle and the every month’s saving’s amount. If it is simple interest, you use the I=P*r*n formula and for compound interest, you use I=P*(1+r)^n-P.
  Now, if I say, your principle is $1200, interest rate is 10% then the annual interest using the simple formula would be $1200*0.1*1=$120 right? Which means, the interest you are actually getting is $120/12=$10 every month. And if you save like $50 every month, the total amount in your account at the end of the first month and at the beginning of the second month will be $1200+$10+$50=$1260, where $1200 is your principle, $10 is the first month’s interest and $50 is the amount you further deposited into your account.
  Now, as you have deposited $50 more, would the interest at the end of the second month and at the beginning of the third month be the same? Of course not.
  At the beginning of the second month, the net amount in your account is $1260 right? Then, as the interest rate is 10%, so in that sense the annual interest using this new amount should be $1260*0.1*1=$126. So basically, the monthly interest this time becomes $126/12=$10.5. That means, at the end of the second month and at the beginning of the third month, the net amount in your account now is $1260+$10.5+$50=$1320.5, where $1260 is the net amount at the beginning of the second month, $10.5 is the monthly interest and $50 is the amount you save every month. And the same procedure goes on for the rest of the months as you are depositing money every month into your account.
  For monthly compound interest, the procedure is mentioned in this article.
  Hope, this clears your confusion. Have a great day.