We’ll use the RATE function:
=
RATE(nper, pmt, pv,[fv], [type], [guess])
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
We’ll try to find out the Interest Rate in cell C9.
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.
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.
- Use the formula in the C9 cell like this.
=RATE(C5,C7,C6)*C5
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%
Press Enter to find the Interest Rate as 10%.
Calculation of EMI from Interest Rate
We can use the PMT function when we need to calculate EMI from the interest rate. 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.
- Use the following formula in the C9 cell to calculate EMI from the interest rate.
=PMT(C7/12,C5,-C6)
- Press Enter to get the output as 1318.74 USD.
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
We have provided a Practice section like below in each sheet on the right side.
Download the Practice Workbook
Related Articles
- How to Convert Monthly Interest Rate to Annual in Excel
- How to Calculate Monthly Interest Rate in Excel
- How to Create Effective Interest Method of Amortization in Excel
<< Go Back to How to Calculate Interest Rate in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!