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 NPER function to return the number of periods for an investment.
* You can use the PMT function to return the full amount (principal + interest) paid every period on a loan with fixed interest.
* You can use the PV function to return the present value of 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(252, -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%

1 - What annual interest rate have you received if you pay £5 in every year for 3 years and receive £16 at the end.
2 - What annual interest rate have you received if you pay £5 in every year for 3 years and receive £15 at the end.
3 - What annual interest rate have you received if you pay £5 in every year for 3 years and receive £30 at the end.
4 - What annual interest rate have you received if you pay £5 in every year for 3 years and receive £35 at the end.
5 - What annual interest rate have you been charged if you pay off a £1,000 loan in 4 years making annual payments of £300.
6 - This is the same as 5.
7 - What annual interest rate have you been charged if you pay off a £1,000 loan in 4 years making annual payments of £300 but this time making payments at the start of each period. The first payment will actually net off against some of the loan so this is equivalent to 8.
8 - What annual interest rate have you been charged if you pay off a £700 loan in 3 years making payments at the end of each period.
9 - What annual interest rate have you been charged if you pay off a £5,000 loan in 2 years making annual payments of £3000.
10 - What annual interest rate have you been charged if you pay off a £5,000 loan in 2 years making semi-annual payments of £1,500.
11 - What annual interest rate have you been charged if you pay off a £5,000 loan in 2 years making monthly payments of £250.
12 - What monthly interest rate have you been charged if you pay off a £5,000 loan in 2 years making monthly payments of £250.
13 - What annual interest rate have you been charged if you pay off a £5,000 loan in 2 years making weekly payments of £55.
14 - What has been the average annual growth rate for an investment of £2,000 is worth £3,000 after 7 years.
15 - This is checking the answer in 11.
16 - What is my average annual return if 6 years ago I had £10,000 and after making a deposit of £5,000 at the end of every year I currently have £55,000.
17 - What annual interest rate would you need if you wanted your savings of £20,000 to grow to £25,000 in 5 years assuming the interest rate is compounded yearly.
18 - What annual interest rate would you need if you had £5,000 in the bank already and by saving a further £250 at the start of every month you wanted to have saved £45,000 in 10 years.
19 - What is the yield to maturity of a bond with a current price of $1,085 that is sold on an interest payment date when the redemption is $1,000, the coupon is 5% (ie $50) paid annually and there are 3 years to maturity. Notice that because the price is above the principal the yield to maturity will be lower than the coupon rate.