by Raj Kumar

Created on: July 13, 2009

Microsoft Excel provides wide range of inbuilt functions for diverse categories grouped into Financial, Statistical, Logical, Date & Time, Math & Trig, Lookup & Reference, Database, Text, Information and User Defined. Each group incorporates multiple functions. User Defined functions depends on the functions created by user for various macros. With the help of these functions, user can smartly automate and make complex things easier and faster.

Financial functions are related to monetary calculations. Rate function is one of those financial types. When we discuss rate, it normally point towards interest rate. Interest rate can be applied to loan or investment instrument. In case of loan, initial due amount decreases every month as the monthly interest amount is repaid for some fixed period of time or till the outstanding amount gets reduced to zero. Reverse to this process, in case the amount is invested for some specific time period, the invested or principal amount increases with cumulative addition of interest amount quarterly, semi-annually or annually. Rate function proves to be very useful tool in evaluating periodic interest rate for loan or investment.

Excel rate function returns the periodic interest rate needed for an invested amount to grow to a specific value spread over a specified number of compounding periods. By default, the periodic payment amount per period should be known. Rate function has six parameters viz. Nper, Pmt, Pv, Fv, Type, and Guess. Syntactically rate function is written as "RATE (Nper, Pmt, Pv, Fv, Type, Guess)".

First parameter i.e. Nper is numerical value which represents total number of payment periods in an annuity. Second parameter i.e. Pmt is optional. It include principal and interest amount excluding fees and taxes. Pmt represents the payment amount made during each period. It cannot change over the life of annuity. Third parameter i.e. Pv is nothing but the present value which shows the current worth of the monetary instrument. Fourth parameter i.e. Fv is important if Pmt is not provided. Fv represents the future value which is assumed to be zero if not provided. After last payment, the outstanding cash balance can be assigned to Fv. Fifth parameter i.e. Type act as logical value. If value of Type is 0 (or not provided), it signifies that payments are due at the end of the period. Conversely, if Type is 1, payments are due at the beginning of the period. Return value of rate also depend on last parameter i.e. Guess. If Guess is not provided or omitted, the value of rate is assumed as 10%. Otherwise rate value varies depending on the value (between 0 and 1) of Guess parameter.

As per the Microsoft Excel documentation, rate function returns the interest rate per period of an annuity. Rate is calculated by iteration and can have zero or more solutions. If the successive results of rate do not converge to within 0.0000001 after 20 iterations, rate returns the "#NUM!" error value. Care should be taken with the Guess parameter in order to find the accurate solution. If it is not properly assigned, the result for the rate part of user formula may fail to converge after 20 interactions. So we need to introduce a Guess value in many cases to align with the solution which may look sometime very unusual. If the payment amount per period is unknown, we can still use the rate function, but we need to modify it slightly. When the payment per period (Pmt) is not known, we should enter the number of compounding periods (Nper), the present value as a negative number (Pv) and the future value (Fv).

Learn more about this author, Raj Kumar.
Click here to send this author comments or questions.