RATE
Returns the interest rate per period for a loan or investment. Useful for comparing different financial offers.
RATE(nper, pmt, pv, [fv], [type], [guess])Arguments
nperTotal number of payment periodspmtPayment made each periodpvPresent value (loan amount)fvFuture value (default 0)(optional)type0 = end of period, 1 = beginning(optional)guessYour estimate of the rate (default 10%)(optional)
=RATE(60, -500, 25000)Monthly rate on a $25K loan with $500 payments over 60 months
=RATE(60, -500, 25000)*12Multiply by 12 to get the annual rate
- •Returns the rate per period, multiply by 12 for annual rate
- •PMT must be negative (money you pay out)
- •May return #NUM! if it cannot converge, try providing a guess
- •Forgetting that RATE returns a periodic rate, not necessarily an annual one - if payments are monthly, the result needs to be multiplied by 12 to get the equivalent annual rate
- •Not supplying a guess argument when RATE fails to converge on a solution (returning a #NUM! error), since RATE uses an iterative method that occasionally needs a starting estimate closer to the actual answer
- •Mixing up the sign convention between pmt and pv - like other financial functions, one must be negative and the other positive, or RATE can't solve for a sensible rate
PMTCalculates the payment amount for a known rate, the inverse of what RATE solves for.NPERSolves for the number of periods rather than the interest rate, when the rate is already known.IRRFinds the discount rate implied by a series of uneven cash flows, rather than RATE's fixed, regular payment structure.Why does RATE return a #NUM! error?
RATE uses an iterative calculation that sometimes fails to converge, especially with unusual inputs. Try supplying a guess argument closer to the expected rate, like 0.01 for roughly 1% per period.
Is the rate RATE returns annual or monthly?
It matches whatever period your payments use - if payments are monthly, RATE returns a monthly rate, which you'd multiply by 12 to compare against an annual percentage rate.
Why do I need both a negative and a positive value among pmt and pv?
RATE follows the same sign convention as other financial functions - one side represents money going out and the other money coming in, and using two same-signed values makes the scenario unsolvable.
Need to translate a formula using RATE?
Use our translator to convert your complete formula