Khan Muhammad Kawser
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.
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.
- 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
- 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.
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.
- 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.
- So, a dialog box will pop up and we can see the values of the named ranges.
- Additionally, we can unhide the “Tables” sheet to see the “periodic_table”.
- 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
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)
- Next, type this formula and press ENTER. Using this formula, we can find the monthly installment.
=PMT(rate,nper,-loan)
- 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
- 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
- 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
- Effective Interest Rate Method Excel Template (Free)
- Calculate Effective Interest Rate On Bonds Using Excel
- How to Use Nominal Interest Rate Formula in Excel
- Nominal vs Effective Interest Rate in Excel (2 Practical Examples)
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)
- After that, type this formula.
=SUM(interest_paid)
- Lastly, type another formula and press ENTER.
=H15/loan
- 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.
- 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
- 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.
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.
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
Hello Ravneet, you can follow this article and adjust your payments on the “Extra Payment” column.
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.
Thank you sir such a great work in financial tools for finance related
You’re most welcome, Sumit!
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?
What if I have a fixed payment schedule? How do I handle interest calculations around that?
THAT WAS GREAT HELP FOR ME
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!
Very helpful indeed, makes my work so easy… thank you very much sir…
Best regards, Mario!
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.
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.
Thank you so much making it easy for me to understand my actual costing.
Thank you so much for making a clear and concise explanation of this concept. THUMBS UP!!!
Excellent..
Dear Sir
Can be available Loan Amortization table in excel
Can it be possible client wise auto update loan amortization table?
Also if possible interest rate change so auto update automatic in excel
Extra Payments means(Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments)
Additional Payment,
Variable or Fixed Rate ,
Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ?
how to create in excel & Suppose provide only interest
Hello BHAVNESH,
Have a look at Article 1 and Article 2. I hope it will help you to meet your desire.