RATE Function

Calculates the interest rate per period of a year

What is the RATE Function?

The RATE function is a Financial functions, that will calculate the interest rate required to pay off a loan or reach a specified amount on an investment for a given period.

For a financial analyst, the RATE function can be useful to calculate the interest rate on zero coupon bonds.

 

Formula

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

 

The RATE function uses the following arguments:

  1. Nper (required argument) – It is the number of periods over which the loan or investment is to be paid.
  2. Pmt (required argument) – It is the payment made for each period. It cannot be changed over its life. PMT includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
  3. PV (required argument) – It is the present value that is the total amount that a series of future payments is worth now.
  4. FV (optional argument) – It is the future value or a cash balance that we want to attain after the last payment is made. If we omit fv, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, we must include the pmt argument.
  5. Type (optional argument) – The number 0 or 1 and indicates when payments are due. If type is set to 0 or omitted, payments are due at the end of the period. If set to 1, payments are due at the start of the period.
  6. Guess (optional argument) – We guess what the rate would be.
    1. When omitted, it takes 10%.
    2. If RATE does not converge, we need to try different values for guess. The function usually converges if guess is between 0 and 1.

 

How to use the RATE Function in Excel?

As a worksheet function, the RATE function can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:

 

Example – Using the RATE function

Suppose we are given the following data:

 

RATE Function

 

We can find out the rate using the RATE function. We will use the formula below:

 

RATE Function - Example 1

 

We get the result below:

 

RATE Function - Example 1a

 

Few things to remember about the RATE Function:

  1. #NUM! error – If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. It might be because we failed to use the cash flow convention of negative numbers to represent outgoing payments and positive numbers to represent incoming payments. Alternatively, we may need to provide an initial ‘guess’ to the function.
  2. #VALUE! error – Occurs if any of the given arguments are non-numeric.
  3. When calculating monthly or quarterly payments, we need to convert the interest rate or the number of periods to months or quarters. For this, we need to ensure that the nper argument is expressed in the correct units, i.e., months = 12 * years, quarters = 4 * years.

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!