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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to calculate interest rate from EMI in Excel then you are in the right place. In this article, we’ll try to discuss how to calculate interest rates from EMI. Mainly, EMI is equated to monthly installment. This is the amount that a borrower pays to the lender monthly. The interest rate depends on this EMI. There are some other variables on which the interest rate is dependent. However, we’ll discuss here the procedure of finding interest rates from EMI.


How to Calculate Interest Rate from EMI in Excel: with Easy Steps

Excel has limited functions to calculate interest rates from EMI. The RATE function is the basic function to calculate the interest rate.
Eventually, the RATE function is like this.

=RATE(nper, pmt, pv,[fv], [type], [guess])

Here,
Nper(required): total payment periods number (years, months)
Pmt (required): the pre-set payment amounts for each period. It doesn’t vary over the annuity’s lifetime. It generally involves principal and interest but excludes taxes.
Pv (required): the present worth of the loan
Fv (optional): the future worth, or the cash balance you want after the last installment. It defaults to 0 if not specified.
Guess (optional): your best guess as to what the rate may be, if you leave it blank it defaults to 10%.

The generic formula with the RATE function is.

=RATE( periods, -payment, amount)*12

However, we’ll try to find out the Interest Rate in cell C9.

Here,
Payment Periods in Month (nper) is 12 months which is in the C5 cell.
Loan Amount (pv) is 15,000 USD which is in the C6 cell.
And EMI is 1,318 USD which is in the C7 cell. We will use the value of EMI to find the interest rate. Values of Payment Periods in Month and Loan Amount will also be used.

Firstly, we can write the formula in the C9 cell like this.

=RATE(C5,C7,C6)*C5

Generally, EMI is used not annually but monthly.

Formula Explanation

  • RATE(C5,C7,C6) → returns the periodic interest rate.
    • Output → 833%
  • RATE(C5, C7, C6)*12 → returns the actual interest rate found from EMI
    • Output →10%

how to calculate interest rate from EMI in Excel using RATE function

Secondly, press ENTER to find the Interest Rate as 10%.

how to calculate interest rate from EMI in Excel using RATE function

Read More: How to Calculate Interest Rate on a Loan in Excel


Calculation of EMI from Interest Rate

Additionally, we can use the PMT function when we need to calculate EMI from the interest rate. Suppose, we want to calculate EMI in cell C9. We have taken the Interest Rate as 10% which is in the C7 cell. Similarly, as before, the Payment Periods in Month is 12, and Loan Amount is 15,000 USD. Firstly, we need to write the following formula in the C9 cell to calculate EMI from the interest rate.

=PMT(C7/12,C5,-C6)

how to calculate EMI from interest rate in Excel using PMT function

Secondly, press ENTER to get the output as 1318.74 USD.

how to calculate EMI from interest rate in Excel using PMT function

Read More: How to Calculate APR in Excel


Things to Remember

  • The EMI has to be taken negatively. In annuity functions cash paid out is represented as a negative number.

Practice Section

Additionally, for doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

practise


Download Practice Workbook


Conclusion

We can calculate Interest Rate from EMI and subsequently EMI from Interest Rate if we study this article properly Because both of the procedures have been discussed here. Eventually, please feel free to drop comments if you have further queries.


Related Articles


<< Go Back to How to Calculate Interest Rate in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo