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 |
|
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. |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top