RATE FunctionWayne Becker20150907T09:48:46+00:00
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 fouryear 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 