Calculating the Monthly Compound Interest in Excel – 3 Formulas

 

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.

Basic Mathematical Formula:

Formula for monthly compound interest in excel

Where,

I = Compound interest.
P = Original principal.
r = Interest rate in percentage per year.
n = Time in years.

Mathematical Example:

A borrower took a $5000 loan at a 10% annual interest rate for 5 years.

The monthly compound interest will be


Formula 1 – Calculate Monthly Compound Interest Manually in Excel Using the Basic Formula

The basic mathematical formula will be used.

A client borrowed $10000 at a rate of 5% for 2 years from a bank. To find the monthly compound interest: Formula for monthly compound interest in excel

Steps:

  • C5 contains the original principal (Present value). Multiply this value by the interest rate. Enter:=C5*

Monthly Compound Interest Manually in Excel Using the Basic Formula

  • Divide the annual interest rate by 12.

Monthly Compound Interest Manually in Excel Using the Basic Formula

  • Provide a cell reference with the number of years to multiply the value by 12. The formula becomes=C5*(1+(C6/12))^(12*C7).

Monthly Compound Interest Manually in Excel Using the Basic Formula

  • Subtract C5 which contains the original principal. The formula becomes-
=C5*(1+(C6/12))^(12*C7)-C5

Monthly Compound Interest Manually in Excel Using the Basic Formula

  • Press Enter.

The monthly compound interest is displayed.

Read More: Methods to Apply Continuous Compound Interest Formula in Excel


Formula 2 – Use the Excel FV Function to Calculate the 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 this argument isn’t used, the PV argument must be provided.
[pv](optional argument) – It specifies the present value (PV) of the investment. If it is omitted, it defaults to zero and the Pmt argument must be provided.
[type] (optional argument) – It identifies whether the wages are created at the start or at the end of the year. It will be 0 at the end, and 1 at the start.

Steps:

  • Specify the rate in the FV function. Divide the annual rate by 12: enter =FV(C6/12, in C9.

FV Function to Calculate Monthly Compound Interest

  • Specify the total periods. Multiply the time in years(C7) by 12.

FV Function to Calculate Monthly Compound Interest

  • As no additional amount is added to the original principal value in between the investment period,  ‘0’ is used for ‘pmt.’ The formula becomes=FV(C6/12,C7*12,0,.

FV Function to Calculate Monthly Compound Interest

  •  As $10000 as the original principal is invested and the value for ‘pmt’ is omitted, the cell reference C5 is used with a negative (-) sign. Enter: =FV(C6/12,C7*12,0,-C5).

FV Function to Calculate Monthly Compound Interest

  • Subtract the original principal from the future value to get the interest. Enter the formula:
=FV(C6/12,C7*12,0,-C5)-C5

FV Function to Calculate Monthly Compound Interest

  • Press Enter.

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


Formula 3 – Apply the Excel FVSCHEDULE Function to Calculate the 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.

The dataset was modified.

  • Enter the present value in the FVSCHEDULE function:  =FVSCHEDULE(C5, in C10.

FVSCHEDULE Function to Calculate Monthly Compound Interest

  • Set the schedule of interest rates as an array. Divide the annual rate by 12 in C9.

  • Enter this value 12 times as an array in the formula. Enter: =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}).

  • Subtract the original principal. The final formula is:
=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

  • Press Enter.


Download Practice Workbook

Download the free Excel template here and practice.

 


Related Article


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. How can you calculate monthly interest where you have the principal and every month you save ?

    • Reply Avatar photo
      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo