Related Function:

The RATE function calculates the interest rate required to pay off a specified amount of a loan, or to reach a target amount on an investment, over a given period.

Syntax

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

Arguments

Argument Description
nper The total number of payment periods in an annuity
pmt The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes.

  If pmt is omitted, the [fv] argument must be included
pv The present value — the total amount that a series of future payments is worth now
[fv] Optional. The future value, or a cash balance you want to attain after the last payment is made.

  If [fv] is omitted, the default is 0, e.g. the future value of a loan is 0
  If [fv] is omitted, the pmt argument must be included
[type] Optional. Defines whether the payment is made at the start or the end of the period. The [type] argument can have the value 0 or 1, meaning:

  0 the payment is made at the end of the period; default if argument is omitted
  1 the payment is made at the beginning of the period
[guess] Optional. Your guess for what the rate will be

  If [guess] is omitted, it is assumed to be 10 percent
  If RATE does not converge, try different values for [guess]. RATE usually converges if [guess] is between 0 and 1

Examples

  A B C D
1 Data      
2 4 Loan term – years    
3 -$200.00 Monthly payment    
4 $8,000 Present value    
5        
6 Formula Result Notes
7 =RATE(A2*12,A3,A4) 1% Monthly interest rate of the loan with the terms above
8 =RATE(A2*12,A3,A4)*12 9.24% Annual interest rate of the loan with the terms above

Note: In the example, the number of years had to be converted into months (=4*12)

Make sure that you are consistent about the units you use for specifying [guess] and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for [guess] and 4*12 for nper.

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#NUM! Occurs if if the function fails to converge to a solution

  Use the cash flow convention of negative numbers to represent outgoing payments and positive numbers to represent incoming payments
Result is much higher or much lower than expected Usually occurs due to failure to convert the nper to months or quarters, i.e.:

  months = 12 * years
  quarters = 4 * years
Result is presented as a zero or 0% Likely to be due to the formatting of the cell containing the function and can be fixed by formatting the cell as a percentage, with decimal places