RATE function in Excel

Use

RATE function is a Financial function, which calculates the interest rate per period for an annuity.  

Syntax

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

The RATE function has the following arguments:

nper – Required. The total number of payments in an annuity.

pmt – Required. It is the amount, which will be paid in each payment period. Normally, it includes principal and interest amount. If omitted then FV argument must be included otherwise Excel returns the #NUM! error.

pv – Required. The present value of the annuity. The present worth of a serious of all future payments now.

[fv] – Optional. The future value of the annuity. It is the cash balance that you want to attain after the last installment. If [fv] is omitted then pv must be included in the function.

[type] – Optional. The argument used to specify when payments are due:

Set type equal to	If payments are due
0 or omitted	At the end of the period
1	At the beginning of the period

[guess] – Optional. It is the guess on the rate, default is 10%.

Example 1

Suppose, you obtained a loan of $1000 with 5 annual equal installments of $300. The rate of interest for the loan will be calculated as under:

Result. the loan with above terms has the interest rate of 15% annually.

Example 2

Suppose, you took a loan of $5000 with monthly payments of $300 each. The loan will be paid out in 5 years. the monthly interest rate for the loan will be calculated:

Result. Note that the payment terms of the loan are first converted in months by multiplying with 12.

Related Functions

PMT function is used to calculate the periodic payment for a loan.

NPER function returns the number of periods required to pay off a loan, given the constant payments and constant interest rate.

FV function calculates the future value of an investment.

PV function is used to get the present value/ current worth of the future cash flows that will receive at a future date.