RATE Excel Formula

Zaheer    Formulas     ,

The Excel function RATE is used to calculate the interest rate that is needed to pay for a loan amount. It is used to return the interest rate of a particular period of the annuity. It is calculated through iteration process and can have more solutions including zero. If successive results of the RATE function don’t meet to within 0.000001 even after 20 iterations the function will return an error value. The syntax of this function is as “RATE (nper,pmt,pv,fv,type,guess)”. The syntax should be followed properly when the function will be applied.

In the function, the argument nper denotes payment periods’ total number in an annuity. The term pmt denotes the payment that will be made in each period. This cannot be changed over the annuity life. Usually, the pmt includes the principal and the interest, but no other taxes or fees. If you omit the pmt you will have to include the argument fv. Pv denotes the present value. It is the value of the investment or loan at present. Fv denotes the future value which is the cash balance after completing the last payment. If the term fv is omitted, 0 will be assumed there. Type is an optional argument which tells whether the payment will be made at the beginning of a period or at the end. Type can be 0 or 1; it is 0 – if the payment is done at the end; it is 1 – if the payment is done at the beginning. If it is omitted, the value 0 will be taken as default; that means payment will be done at the end. Guess is the initial estimate of the rate. If it is omitted, a value of 10% will be taken as default.

An example can be stated here to make the description easy to understand. Consider an amount of loan 50000 dollar. The loan should be paid over a 5-years period with the payment 1000 dollar per month. The payment should be done at the end of the month. Now find out the total number of periods which is 5 X 12 = 60 months. Here, pmt argument will be negative as these are outgoing payments. The interest rate that will be returned is monthly rate. If yearly interest rate is needed then it should be multiplied by 12.

The remark that should be remembered when the function will be applied is stated here. You should be consistent enough about the units when specifying the nper and guess. If you have to pay monthly payments over a four-year time period at an annual rate of 12%, then for guess use 12% / 12 and for nper use 4 * 12. If you have to pay yearly payments over that four-year period at that annual rate – then for guess use 12% and for nper use 4. When you will want to apply a function in your database, you should truly understand all the terms of the function. For more examples, you can surf the internet. You can find the help files of the Excel program useful when you will read them thoroughly.