Create Flat and Reducing Rate of Interest Calculator in Excel

In this article, we will show you how to create a flat and reducing rate of interest calculator in Excel using a few quick steps. Using the calculator, you will be able to see which methods give you a better financial return.


Download Practice Workbook

You can download the Excel file from the link below.


Flat Rate Interest

Many people fail to understand these two financial terms: “flat rate interest” and “reducing balance rate”. When you’re dealing with your personal finance, it is not wise to be in the dark. Not knowing general financial concepts can lead to financial disasters. If you read this article, you will be clear about these two financial terms. We will also provide you with the flat and reducing rate of interest calculator in Excel. But first, let us see the formula to calculate the flat rate of interest.

Flat Rate Interest = (Loan Amount x Number of Years x Annual Percentage Rate) / Total Number of Installments

It is better to understand this definition with an example. Suppose you took a loan for $100,000 with an annual percentage rate (APR) of 6%. You took the loan for 5 years, and you have to pay the installment every month. Your flat rate interest will be: ($100,000 x 5 x 6%) / 60 or, $500. This is your interest that you will pay in every installment. Now, let’s calculate your principal repayments.

Principal Repayment = Loan Amount / Total Number of Installments

If we plug in the values, then it will be = $100,000 / 60 or $1,666.67. So, your total monthly payment will be: $500 + $1666.67 = $2166.67 You see the summary of the above calculation (using my flat rate interest calculator) in the following image. The calculator also shows the following things:

  • Total Payments: $130,000
  • Total Interest Paid: $30,000
  • You will pay an overall 30% interest rate for the next 5 years. The rate is high, right?

Reducing the Rate of Interest

This is a better approach when you handle your loan, and this is widely used by banks and financial institutions. We will calculate the reducing rate of interest using the above loan details.

  • A loan of amount $100,000
  • Annual percentage rate (APR) 6%
  • Tenure of the Loan: 5 years.
  • Payment Frequency: Monthly

If the payment frequency is monthly, then first we have to calculate the rate for a month:

= Annual Percentage Rate / 12 = 6% / 12 or 0.005

The next thing is we have to calculate the monthly PMT for the loan using Excel’s PMT function:

=PMT(rate, nper, -loan)

  • Here, the values are:
    • rate = 0.005
    • nper = 60; [nper = number of total periods]
    • -loan = -100,000; [loan is negative as we want the PMT to be a positive value].

It returns the value: $1933.28.

Reducing the Rate of Interest

Now let’s discuss how the whole thing works:

1st Month:

At the start of the first month, you just take the loan. So, the balance is $100,000. At the end of the first month, you’re paying $1933.28 (the PMT amount) to the financial institution. How much of this amount ($1933.28) is paid in interest? Your first month’s interest is: $100,000 x 0.005 = $500. So, $500 of the amount of $1933.28 is provided as the interest payment. The rest, $1933.28 – $500 = $1433.28, will be deducted from your principal. So, at the end of the first month, your new principal will be: $100,000 – $1433.28 = $99,566.72.

2nd Month:

At the start of the 2nd month, your new principal is: $98,566.72. At the end of the second month, you’re paying $1933.28 (the PMT amount) to the financial institution. How much of this amount ($1933.28) is paid in interest? Your second month’s interest is: $98566.72 x 0.005 = $492.83. So, $492.83 of the amount of $1933.28 is provided as the interest payment. The rest, $1933.28 – $492.83 = $1440.45, will be deducted from your principal. So, at the end of the second month, your new principal will be: $98566.72 – $1440.45 = $97126.27. This is how the whole process advances. In the following images, you show the whole process.

You see from the following image that at the last payment, we paid only 9.62$ as the interest payment and we cleared all our principal with the remaining amount of $1923.66. Our ending balance is 0.00. So that is our last payment to the financial institution.

You see, the loan using the reducing rate of interest is simple. At the end of the day, we only pay 16% interest overall in this system.


Step-by-Step Procedures to Create Flat and Reducing Rate of Interest Calculator in Excel

We will show four quick steps to create a flat and reducing rate of interest calculator in Excel. Firstly, we will enter all the required values. Following that, we will calculate the flat rate interest rate. Thirdly, we will find the payment schedule. Finally, we will calculate the reducing rate of interest to wrap up the steps.


Step 1: Entering Required Values

We will type the required values in this first step. Additionally, we will use the data validation to let users set the data using a dropdown list. We will list the allowed values using a comma and input them into the source field of the data validation dialog box.

  • To begin with, type these values.

Entering Required Values to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Next, select cell C9.
  • Then, from the Data tab, select Data Validation.

  • Afterward, type these values with a comma in the source field and press OK.
    • Beginning of the Period
    • End of the Period

Data Validation to Create Flat and Reducing Rate of Interest Calculator in Excel

  • The values for the payment frequency and interest compounding frequency are as follows. Type these values using a comma inside the data validation source.
    • Weekly
    • Bi-weekly
    • Semi-monthly
    • Monthly
    • Bi-monthly
    • Quarterly
    • Semi-annually
    • Yearly
  • Thus, after doing all these, it will complete the first step.

Last Image of Step 1 to Create Flat and Reducing Rate of Interest Calculator in Excel

Read More: How to Calculate Interest Rate in Excel (3 Ways)


Step 2: Finding Flat Rate Interest

In this second step, we will calculate the flat rate of interest. We will be using the IF, VLOOKUP, and PMT functions. Moreover, we will be using the name range to keep things simple to apply. We have used a table for the lookup range for the VLOOKUP function. This table is hidden to keep the users from changing the values by accident.

  • Firstly, type the following fields in the cell range B13:B17.

Calculating Flat Rate Interest to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Next, type this formula in cell C15.

=C7*VLOOKUP(payment_frequency,periodic_table,3,FALSE)

Formula Breakdown

  • Firstly, the payment_frequency is “Monthly”.
  • Secondly, the periodic_table is the lookup range for our function. We can see the table from the name range or by unhiding the “Tables” sheet.
  • Thirdly, we will return values from the third column.
  • Lastly, we have used FALSE to indicate exact matching, and we are multiplying the data by the loan terms (years) value to get the value of the scheduled number of payments.
  • We can see the name range in the following way.
  • Firstly, from the Formulas tab, select Name Manager.

Name Manager to Create Flat and Reducing Rate of Interest Calculator in Excel

  • So, a dialog box will pop up and we can see the values of the named ranges.

Name Manager Dialog Box

  • Additionally, we can unhide the “Tables” sheet to see the “periodic_table”.

Hidden Table to Create Flat and Reducing Rate of Interest Calculator in Excel

  • After that, type another formula in cell C16.

=(1+apr/VLOOKUP(interest_compounded,periodic_table,3,0))^(VLOOKUP(interest_compounded,periodic_table,3,0)/VLOOKUP(payment_frequency,periodic_table,3,0))-1

Finding Interest Rate Create Flat and Reducing Rate of Interest Calculator in Excel

Formula Breakdown

  • Here, we have three VLOOKUP functions. Again, we have used the name range. Here, “interest_compunded” is in cell C11 and the value of “payment_frequency” is in cell C10.
  • VLOOKUP(interest_compounded,periodic_table,3,0)
    • Output: 12.
  • VLOOKUP(payment_frequency,periodic_table,3,0)
    • Output: 12.
  • The formula reduces to, (1+apr/12)^(12)/12)-1
    • Output: 0.00499999999999989.
  • Then, type another formula in cell C17. We are using a conditional here, when the payment type is the beginning of the period, then the formula will return 1. Otherwise, we will get 0.

=IF(C9="Beginning of the Period",1,0)

Finding Payment Type to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Next, type this formula and press ENTER. Using this formula, we can find the monthly installment.

=PMT(rate,nper,-loan)

Finding Installment to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Now, we will find the values of the flat rate of interest (summary).
  • So, type this formula and press ENTER.

=loan/nper

  • Then, type this formula and press ENTER.

=loan*rate

Flat Rate Summary

  • After that, type this formula and press ENTER.

=H5+H6

  • Next, type this formula and press ENTER.

=H7*nper

  • Then, type this formula and press ENTER.

=H8-loan

  • Afterward, type this formula and press ENTER. This will conclude the second step of this article.

=H9/loan

Read More: How to calculate periodic interest rate in Excel (4 ways)


Step 3: Calculating Payment Schedule

We will calculate the payment schedule in this third step. We will use four new functions in this step; the AND, OR, EDATE, and IFERROR functions. Without further ado, let us jump into the procedures.

  • To begin with, type the following formula in cell H21 and press ENTER.

=loan

Calculating Payment Schedule to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Next, type this formula and fill in the formula downward.

=IFERROR(IF(H21<=0,"",B21+1),"")

  • Afterward, type this formula and fill in the formula downward.

=IF($C$9="End of the Period",IF(B22="","",IF(OR(payment_frequency="Weekly",payment_frequency="Bi-weekly",payment_frequency="Semi-monthly"),loan_date+B22*VLOOKUP(payment_frequency,periodic_table,2,0),EDATE(loan_date,B22*VLOOKUP(payment_frequency,periodic_table,2,0)))),IF(B22="","",IF(OR(payment_frequency="Weekly",payment_frequency="Bi-weekly",payment_frequency="Semi-monthly"),loan_date+(B22-1)*VLOOKUP(payment_frequency,periodic_table,2,0),EDATE(loan_date,(B22-1)*VLOOKUP(payment_frequency,periodic_table,2,0)))))

Formula Breakdown

  • Firstly, we are using the nested IF formula.
  • OR(payment_frequency=”Weekly”,payment_frequency=”Bi-weekly”,payment_frequency=”Semi-monthly”)
    • Output: FALSE.
  • loan_date+B22*VLOOKUP(payment_frequency,periodic_table,2,0)
    • Output: 43467.
  • EDATE(loan_date,B22*VLOOKUP(payment_frequency,periodic_table,2,0))
    • Output: 43497.
  • loan_date+(B22-1)*VLOOKUP(payment_frequency,periodic_table,2,0)
    • Output: 43466.
  • EDATE(loan_date,(B22-1)*VLOOKUP(payment_frequency,periodic_table,2,0))
    • Output: 43466.
  • Then, the formula reduces to, IF($C$9=”End of the Period”,IF(B22=””,””,IF(FALSE,43467,43497)),IF(B22=””,””,IF(FALSE,43466,43466)))
    • Output: 43497.
    • The actual value in cell C9 is “End of the Period”, therefore, the function will return true. Now, cell B22 is not blank. So, IF(FALSE,43467,43497) this part will be executed. As the logical condition is false, we will get the value 43497.
  • Then, type this formula and fill in the formula downward.

=IF(H21=0,"",H21)

  • After that, type this formula and fill in the formula downward.

=IF(B22="","",IF(H21<payment,H21*(1+rate),payment))

  • Afterward, type this formula and fill in the formula downward. If the payment type is at the beginning of the month and the value of cell B22 is 1, then it will return 0. Otherwise, it will return the “H21*rate” part.

=IF(AND(payment_type=1,B22=1),0,IF(B22="","",H21*rate))

  • Next, type this formula and fill in the formula downward.

=IF(B22="","",E22-F22)

  • Next, type this formula and fill in the formula downward.

=IFERROR(IF(G22<=0,"",H21-G22),"")

  • Therefore, we will complete the third step and the payment schedule will look like this.

Read More: How to Calculate Interest Rate on a Loan in Excel (2 Criteria)


Similar Readings


Step 4: Calculating Reducing Rate of Interest

We will find the reducing rate of interest in this last step. We will use the SUM function in this step. Additionally, we have named the cell range F22:F10971 and G22:G10971 as the interest_paid and principal_paid respectively.

  • Firstly, type the following formula.

=SUM(interest_paid, principal_paid)

Calculating Reducing Rate of Interest to Create Flat and Reducing Rate of Interest Calculator in Excel

  • After that, type this formula.

=SUM(interest_paid)

Total Interest Paid to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Lastly, type another formula and press ENTER.

=H15/loan

Flat Interest Percentage to Create Flat and Reducing Rate of Interest Calculator in Excel

  • Finally, we completed the creation of a flat and reducing rate of interest calculator in Excel.

Read More: How to Calculate Monthly Interest Rate in Excel (3 Simple Methods)


Instructions for Using This Calculator

It is very easy to use this calculator. We will show you which values to enter and change in this section.

Enter the following values to use this calculator:

  • Loan Amount
  • Annual Percentage Rate (APR)
  • Loan terms (Years)
  • Loan Date (mm/dd/yy)
  • Payment type. It is a drop-down list. You will get two types of payments. End of the Period and Beginning of the Period. Choose one that suits your loan.
  • Payment frequency: It is also a drop-down list. You can choose payment frequencies:
Interest Compounded Calculated After

(Days or Months)

No. of Payments/Year
Weekly 7 Days 52
Bi-weekly 14 Days 26
Semi-monthly 15 Days 24
Monthly 1 Month 12
Bi-monthly 2 Months 6
Quarterly 3 Months 4
Semi-annually 6 Months 2
Yearly 12 Months 1
  • Interest Compounding Frequency: In most cases, your Payment frequency will be equal to Interest Compounding Frequency. In some countries, for example, Canada, Interest is compounded semi-annually but payment is done monthly. So, except in rare cases, your Payment frequency will always be equal to Interest Compounding Frequency.

There are no other values to input. The rest will be handled by the calculator.


EMI Calculator with Prepayment Option in Excel Sheet

Let’s assume we have an Excel worksheet that contains the information about the EMI calculation with the prepayment option. We will calculate the EMI with the prepayment option from our dataset using Excel’s PMT and IPMT financial formulas. PMT stands for payment, and IPMT is used to get the interest on a payment. We will apply these financial functions to calculate the EMI calculator on an Excel sheet with a prepayment option.

Steps:

  • Firstly, type in all these fields. Moreover, our values are for 1 year, so we have created the template for 12 months. You can add more as needed.

EMI Calculator with Prepayment Option in Excel Sheet

  • Next, type this formula to get the value of the beginning balance.

=F7-F8

  • After this, type another formula to return the value of EMI and fill the formula downward.

=PMT(F$4/F$6,F$5*F$6,F$11)

  • Then, type another formula to get the interest amount and fill the formula down.

=IPMT(F$4/F$6,B12,F$5*F$6,F$11)

  • After that, type another formula to get the principal amount and fill the formula downward.

=C12-D12

  • Next, type another formula to get the balance amount and fill the formula down.

=F11+E12

  • Then, type another formula to get the percentage of loan paid and fill the formula downward.

=(F$11-F11)/F$11

  • Finally, it will complete the creation of an EMI calculator with a prepayment option in an Excel sheet.

Read More: How to Calculate Interest Rate from EMI in Excel (with Easy Steps)


Simple Interest Loan Calculator Using Formula in Excel

Simple interest denotes the amount estimated from the principal amount of any loan taken from a bank or the initial contribution of any bank’s savings account. We can also say the amount of interest that gains directly from the principal amount. The mathematical expression of simple interest is:

I = P × R × n

Here the symbols denote the following things:

  • I = The amount of earned interest from the principal amount.
  • P = Principal amount
  • R = Annual rate of interest
  • n = Number of period

Now, we will show you three suitable examples of building a simple interest loan calculator using the Excel formula. We will use the PPMT function to calculate the principal amount in this section.

Steps:

  • Firstly, type the following fields and type a formula in cell D8.

=D7*12

Simple Interest Loan Calculator Using Formula in Excel

  • Then, type another formula and fill it down.

=PMT($D$6/12,$D$8,$D$5)

  • Next, type another formula and fill it down.

=IPMT($D$6/12,B13,$D$8,$D$5)

  • Afterward, type another formula in cell F13.

=PPMT($D$6/12,B13,$D$8,$D$5)

  • Next, type this formula to get the closing balance and fill in the formulas from columns F and G.

=C13+F13

  • Afterward, type this formula and fill it in.

=G13

  • Lastly, type this formula to get the total values and fill it right.

=SUM(D13:D24)

  • Finally, it will finish all the steps to create a simple interest loan calculator using the formula in Excel.


Conclusion

We have shown you step-by-step procedures to create the flat and reducing rate of interest calculator in Excel. So, it is clear that you will pay less interest in the Reducing Rate of Interest than the Flat Interest Rate. Always choose the method that costs you less. If you face any difficulties using this calculator, let us know in the comment box.


Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

20 Comments
  1. such a great help in my work since i handled loan applications. how about the pro-rata interest ? example the loan is granted on the 10th of the month. how do i include in the calculator the pro-rata interest. the payment is always at the end of the month so how do i include the pro-rata days to include in the monthly payment

    • Hi Lisa. You can use the YEARFRAC function to calculate the days difference in fraction of that year. Then, you can multiply the result with the annual interest payment (in this case, scheduled monthly payment*12) to include the pro-rata interest.

  2. hello myself ravneet from patiala i want to know which is best for small finance bank (jivan etc)
    1st and last installment change other remain same send formala detail for excel to find out

  3. Hi,
    Have gone through the template and found it quite interesting and will score it 8 out 10 reason:
    1. On Web most of the experts had shared template and urs is having same feasible solution. But have given two addition points in 8 belongs to combine approach of Straight line and reducing Method.

    2. ‘ve hold 2 points for one unique reason, suppose Mr. X had taken loan of USD 150000 for 5 Years at 18% intt. rate and at the end of 23rd installment he paid USD 60000 than in that particular scenario template got failed.

    Hoping that you got my point.

  4. Hi, I m trying to learn how to use excel sheets. The one thing I really need but have not figured out is this.

    I want to be able to put in a fixed value, that is changed with a set interest rate monthly, and able to auto calculate the new balance over a set number of months. The interest rate needs to be where it can be either a positive or negative value. could you share a formula that excel could use for this purpose?

  5. What if I have a fixed payment schedule? How do I handle interest calculations around that?

  6. THAT WAS GREAT HELP FOR ME

  7. Congratulations the loan calculator is good, but try to adjust the loan term(year), some are borrowing in short terms like 1month, 3months or 10months.Therefore put the loan term in months also.

    Thank you!

  8. Reply
    mario a. godornes Aug 1, 2021 at 7:29 PM

    Very helpful indeed, makes my work so easy… thank you very much sir…

  9. I do love this template, thank you for your effort
    my only comment is the template doesn’t consider if there is a grace period. i tried to change the date of the first installment as i thought it considers Today date and the first installment but it doesn’t.
    Thanks you
    Mostafa

    • Different credit cards have different grace periods. You can send us your Excel file and we will look into it.

  10. Reply
    Prashant Krishnan Jan 1, 2022 at 11:22 AM

    Pls advise if the interest rate is floating and revised every quarter basis LIBOR then for a given period of time how do we calculate the reducing rate of interest equivalent to the variable interest rate.

    • You can split the payment schedule into desired quarters and calculate the payments using that quarters’ interest rates.

  11. Thank you so much making it easy for me to understand my actual costing.

  12. Thank you so much for making a clear and concise explanation of this concept. THUMBS UP!!!

  13. Excellent..

Leave a reply

ExcelDemy
Logo