Table of Contents
Summary
The Excel RATE function calculates the interest rate per period for a loan or investment. The function assumes constant payments (or cashflow if an investment) will be made. The RATE function is an Excel financial function.
Function
=RATE(nper, pmt, pv, [fv], [type], [guess])
Arguments (inputs)
nper = the number of periods (term of the loan, e.g., years, months)
pmt = the payment per period
pv = present value, the loan or principle amount
fv = [optional] the future value of the loan. If left blank it will default to 0.
type = [optional] 1 = payment at the beginning of the period, 0 = payment at the end of the period, if omitted it will default to end of period
guess = [optional] your guess for what the interest rate will be (as a starting point), if blank it will default to 0.10 or 10%
Note: the pmt and nper inputs should be aligned, meaning that if the nper is entered in years, the pmt should be an annual payment.
Return value
The RATE function returns the interest rate for a period for a loan or investment with constant payments or cashflow. The interest rate period is defined by the nper argument.
For example, if the nper is in years, then the interest rate will be an annual interest rate. If the nper is in months, then the interest rate will a monthly interest rate.
Example
In the example above the RATE function calculates the interest rate for a loan with the terms listed in cells C5, C7, and C9. In this example, the loan amount is $1,000 for 3 years with an annual payment of $375.
=RATE(C7, C5, -C9)
Note: A minus sign “-” is entered before the pv argument (C9) because the present value is negative (a loan). If a minus sign is not entered the function will return a “#NUM!” error.
The result returned is 6%, which is the annual interest rate.
