How to Calculate Interest Rate in Excel (3 Ways)

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.

Convert


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.

Use Formula

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)

Use Formula to Calculate Periodic Interest Rate in Excel

  • Press Enter.

Use Formula to Calculate Periodic Interest Rate in Excel


Case 1.2 – Annual Interest Rate

We’ll compute the monthly interest rate first, then multiply by 12 to get the annual interest rate.

Use Formula to Calculate Periodic Interest Rate in Excel

Steps:

  • Insert the following formula in cell F4:
=RATE(C4,-C5, C6)

Use Formula to Calculate Periodic Interest Rate in Excel

  • 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

Use Formula to Calculate Periodic Interest Rate in Excel

  • The Annual Interest Rate will be shown in cell F6.

Use Formula to Calculate Periodic Interest Rate in ExcelPay 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.

Apply Formula to Calculate Effective Interest Rate in Excel


Case 2.1 Use the Formula of Compound Interest

Steps:

  • Use this formula.

Apply Formula to Calculate Effective Interest Rate in Excel

  • The formula becomes:
=(1+C4/C5)^C5 - 1

Apply Formula to Calculate Effective Interest Rate in Excel

  • Press Enter.

Apply Formula to Calculate Effective Interest Rate in Excel


Case 2.2 – Use the EFFECT Function

Steps:

=EFFECT(C4, C5)

Apply Formula to Calculate Effective Interest Rate in Excel

  • Press Enter to get the Effective Interest Rate.

Apply Formula to Calculate Effective Interest Rate in Excel


Method 3 – Convert Effective Interest to Nominal Interest

You can get back to the Nominal/Simple Interest Rate from the Effective Interest Rate.

Convert

Steps:

  • Enter the following formula of the NOMINAL function.
=NOMINAL(C7, C5)

Convert

  • Press Enter to get the Nominal Interest Rate.

Convert

Read More: Nominal vs Effective Interest Rate in Excel


Download the Practice Workbook


How to Calculate Interest Rate in Excel: Knowledge Hub


<< Go Back to  Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo