Consider the following dataset that we’ll use to calculate interest rates. We’ll calculate the Monthly and Yearly Interest Rates, as well as the Effective and Nominal Interest Rates, using formulas and functions.
Method 1 – Use a Formula to Calculate the Periodic Interest Rate in Excel
We will compute the interest rate for months and then for years in the two subsections below.
We will apply the RATE function.
=RATE(nper, pmt, pv, [fv], [type], [guess])
Here,
- Nper (required) – total payment periods number (years, months)
- Pmt (required) – the pre-set payment amounts each period that cannot be varied 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.
- Type (optional) – specifies the date on which the payments are made:
- Payment is due at the end of the period if 0 or absent (default).
- 1 – the first payment is required at the start of the period.
- Guess (optional) – your best guess as to what the rate may be If you leave it blank, it defaults to 10%.
Case 1.1 – Monthly Interest Rate
Steps:
- Select cell F4.
- Insert the following formula.
=RATE(C4,-C5, C6)
- Press Enter.
Case 1.2 – Annual Interest Rate
We’ll compute the monthly interest rate first, then multiply by 12 to get the annual interest rate.
Steps:
- Insert the following formula in cell F4:
=RATE(C4,-C5, C6)
- Multiply the previous calculation by the value of C7 or use the following formula to get the annual interest rate:
=RATE(C4, -C5,C6)*C7
- The Annual Interest Rate will be shown in cell F6.
Pay attention to the C5 cell. It is in negative form since the payment will be reduced after each transaction. It will show a #NUM error if you don’t put the value in negative.
Method 2 – Apply a Formula to Calculate the Effective Interest Rate in Excel
Here:
- i= the rate of interest.
- n= number of the compounding periods.
Case 2.1 Use the Formula of Compound Interest
Steps:
- Use this formula.
- The formula becomes:
=(1+C4/C5)^C5 - 1
- Press Enter.
Case 2.2 – Use the EFFECT Function
Steps:
- In cell C8, use the formula of the EFFECT function:
=EFFECT(C4, C5)
- Press Enter to get the Effective Interest Rate.
Method 3 – Convert Effective Interest to Nominal Interest
You can get back to the Nominal/Simple Interest Rate from the Effective Interest Rate.
Steps:
- Enter the following formula of the NOMINAL function.
=NOMINAL(C7, C5)
- Press Enter to get the Nominal Interest Rate.
Read More: Nominal vs Effective Interest Rate in Excel
Download the Practice Workbook
How to Calculate Interest Rate in Excel: Knowledge Hub
- How to Use Nominal Interest Rate Formula in Excel
- How to Calculate Weighted Average Interest Rate in Excel
- How to Calculate Periodic Interest Rate in Excel
- How to Convert Monthly Interest Rate to Annual in Excel
- How to Calculate APR in Excel
- How to Calculate Interest Rate from EMI in Excel
- How to Create Effective Interest Method of Amortization in Excel
<< Go Back to Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!