How to use the Excel RATE function

Excel RATE Function

Excel offers some great tools to work with interest. The Excel RATE function is one such tool. It is a financial function. It calculates the rate of interest for a given period of an investment or loan. In this tutorial, we will learn how to use the function RATE in Excel.

Figure 1. Example of How to Use the Excel Rate Function

Syntax & Arguments

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

nper – This argument is required. It is the count of period in total that we need to pay the amount.
pmt – This argument is also required. It is the amount paid in each period. This value can not be changed during its course.
pv – It is a required value as well. It represents the total amount of the investment or loan.
fv – This value is optional. It represents the cash balance we want to achieve after last payment. Default is 0.
type – This value is also optional. It indicates the time payments are due. 0 represents amounts are scheduled in the end of the period. 1 indicates amounts are due at the start. The default value is 0.
guess – It is an optional value as well. It is our guess at the rate. The default is 10%.

Setting Up Data

The following example contains some sample values for a loan. Column A has the descriptions of the values. Column B has the years of the loan, the monthly payment and the amount of the loan.

Figure 2. The Sample Data

To calculate the interest rate using the Excel RATE function in cell B5, we need to:

Go to cell B7. Click on it with the mouse.
Assign the formula =RATE(B3*12,B4,-B2) to B7.
Press Enter.

Figure 3. Applying the Formula

This will show the interest rate in cell B7 which is 4%.

Notes

Excel calculates RATE by iterations. If the results of RATE do not converge to 0.0000001 after 20 iterations, it returns a #NUM! error. To prevent this, we may provide an initial ‘guess’ argument to the function.
Excel returns a #VALUE error if any of the arguments provided for RATE are non-numeric.
We need to change the number of periods or interest rate to months or quarters. We need to do this before calculating quarterly or monthly payments. Hence, we need to make sure nper has the correct units, i.e., quarters = 4*years, months = 12*years.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Hello, If I would like to calculate the monthly, quaterly, or bi-annual interest rate that compounded would give me a X annual percentage return rate. For example 1: Annual return rate = 10% number of periods = 12. Montly interest rate =?? (c1%). example 2: Annual return rate =10%, number of periods 4. Quaterly interest rate =?? (c2.5%)? Thanks

An Excelchat Expert solved this problem in 12 mins!

The drum teacher has decided to create three models for an increase in the base hourly rate charged for lessons before deciding which base rate to use next year. examine the Scenario Summary report shown. Create three scenarios to save the hourly base rates shown: Low Rate Increase, Mid Rate Increase, and High Rate Increase.

An Excelchat Expert solved this problem in 29 mins!