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.

**Table of Contents**Expand

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

**Read More:** Methods to Apply Continuous 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.

*â€“ The total payment periods.*

**nper (required argument)***â€“ It specifies the payment per period. If we avoid this argument, we will need to provide the PV argument.*

**pmt (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.*

**[pv](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.*

**[type] (optional argument)**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 typein`=FV(C6/12,`

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

**Read More:Â **How to Calculate Compound Interest for Recurring Deposit in Excel

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

*â€“ The array of values that provides the schedule of interest rates to be applied to the principal.*

**Schedule (required argument)**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 typein`=FVSCHEDULE(C5,`

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

**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**

- Excel Formula to Calculate Compound Interest with Regular Deposits
- How to Calculate Compound Interest in Excel in Indian Rupees

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

How can you calculate monthly interest where you have the principal and every month you save ?

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*nformula and for compound interest, you useI=P*(1+r)^n-P.Now, if I say, your principle is

$1200, interest rate is10%then the annual interest using the simple formula would be$1200*0.1*1=$120right? Which means, the interest you are actually getting is$120/12=$10every month. And if you save like$50every 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$1200is your principle,$10is the first monthâ€™s interest and$50is the amount you further deposited into your account.Now, as you have deposited

$50more, 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

$1260right? Then, as the interest rate is10%, 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$1260is the net amount at the beginning of the second month,$10.5is the monthly interest and$50is 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.