# RATE

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

Returns the interest rate for a series of equal cash flows at regular intervals.

 nper The total number of payments. pmt The fixed payment made each period. pv The present value. fv (Optional) The future value (or cash balance) after all the payments. type (Optional) The number indicating when the payments are due:0 = the end of the period (default)1 = the start of the period guess (Optional) An estimate as to what the rate will be.

 REMARKS
 * For an illustrated example refer to the Loan Repayments page.* This function allows you to calculate the interest rate of a simple annuity.* This function uses iteration and can have zero or more solutions.* If the successive results of this function do not converge to within 0.0000001 after 20 iterations, then #NUM! is returned.* If the function does not converge, you should try a different value for the "guess". The function will usually converge when the "guess" is between 0 and 1.* A negative number represents any cash you pay out.* A positive number represents any cash you receive (start with or end with).* The "nper", "pmt" and "guess" MUST be expressed in the same units of time: years, months or days.* The "nper" is the number of compounding periods.* If "pmt" is left blank, then you must include "fv".* If "fv" is left blank, then 0 is used.* If "type" = 0, then payments are made in arrears.* If "type" is left blank, then 0 is used.* If "guess" is left blank, then 0.1 (or 10%) is used.* If you make annual payments on a 4 year loan at 12% annual interest, the "guess" should be 12% and the "nper" should be 4.* If you make monthly payments on a 4 year loan at 12% annual interest, the "guess" should be 12%/12 and the "nper" should be 4*12. * This function can be used to calculate the bond yield to maturity on a coupon date.* You can use the FV to return the future value for a series of equal cash flows at regular intervals.* You can use the YIELD function calculate the bond yield to maturity on any day.* You can use the MIRR function to return the interest rate for a series of unequal cash flows at regular intervals (explicit reinvestment rate).* You can use the IRR function to return the interest rate for a series of unequal cash flows at regular intervals (implicit reinvestment rate).* You can use the XIRR function to return the interest rate for a series of unequal cash flows at irregular intervals (implicit reinvestment rate).* The equivalent VBA function is VBA.RATE* For the Microsoft documentation refer to support.microsoft.com* For the Google documentation refer to support.google.com

 A 1 =RATE(3,-5,0,16) = 6.52% 2 =RATE(3,-5,0,15) = 0.00% 3 =RATE(3,-5,0,30) = 79.13% 4 =RATE(3,-5,0,35) = 100.00% 5 =RATE(4,-300,1000,0) = 7.71% 6 =RATE(4,-300,1000,0,0) = 7.71% 7 =RATE(4,-300,1000,0,1) = 13.70% 8 =RATE(3,-300,700,0) = 13.70% 9 =RATE(2,-3000,5000) = 13.07% 10 =RATE(2*2,-3000/2,5000)*2 = 15.43% 11 =RATE(2*12,-3000/12,5000)*12 = 18.16% 12 =RATE(2*12,-3000/12,5000) = 1.51% 13 =RATE(2*52,-55,5000)*52 = 13.65% 14 =RATE(7,0,-2000,3000) = 5.96% 15 =FV(0.059634,7,,-2000) = \$3,000 16 =RATE(6,-5000,-10000,55000) = 9.33% 17 =RATE(5,0,-20000,25000,0) = 4.56% 18 =RATE(10*12,-250,-5000,45000,1) = 0.35% 19 =RATE(3,50,-1025,1000) = 4.10%