Go Back

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

  1. 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.
  2. Excel returns a #VALUE error if any of the arguments provided for RATE are non-numeric.
  3. 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:
Here are some problems that our users have asked and received explanations on

SUMPRODUCT FORMULA IN EXCEL 1 2 3 4 Qty Rate Qty Rate Qty Rate Qty Rate 10 200 5 190 5 180 5 180
Solved by A. A. in 26 mins
Rate Start Date = 05/01/2011 Rate End Date = 31/05/2017 Declaration Date From = March-17 Declaration Date To = May-17 Volumes = 69 Rate = 0.20p I need a formula that will calculate volumes * Rate, but only if the declaration dates are within the rate start and end date range please
Solved by S. L. in 14 mins
i need to calculate foreign exchange variance based on previous rate and current rate -
Solved by C. L. in 18 mins

Leave a Comment

avatar