Working with financial functions (III)

RATE (nper,pmt,pv,fv,type,guess) function

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

Pv is the present value— the total amount that a series of future payments is worth now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due.

Eg: for the RATE(360;-599,55;100000) we should obtain 0%, which is the monthly interest, to obtain the annual interest we need to multiply the value by 12, and then multiply the result by 100 to get the percentage.

IRR function (values;guess)

Returns the internal rate of return of an investment for a set of values in cash.

These cash flows do not need to be constant, as in the case of an annuity. But the cash flows need to occur at regular intervals like months or years. The internal rate of return equals the rate produced by an investment project with payments (negative values) and earnings (positive values) that occur in regular periods.

IRR syntax (values;guess)

Values = is a matrix or reference to a cell that contains the numbers with which it needs to calculate the internal rate of return.

• The values argument needs to contain at least one positive value and one negative value with which to calcuate the internal rate of return, if not, it will return the error #¡NUM!

• IRR interprets the order of the box flows according to the the order of value argument.The values of the payments and earnings need to be correct.

Guess = is a number that is estimated that will approximate the IRR result. In most cases it is not necessary to provide the estimate argument, it is assumed to be 0,1 (10%)

Eg:

For an investment table like the one seen below.

Cell E3=IRR(A3:D3) and cell F3=IRR(A3:C3)

MIRR function (values;financing_rate;reinvestment_rate)

Returns the modified internal rate of return, for a series of periodic flows, declared cost of the investment and interest to be returned on investing the cash.ctivo.

MIRR syntax(values;financing_rate;reinvestment_rate)

Values = is a matrix or a cell reference that contains numbers. These numbers represent a series of payments (negative values) and earnings (positive values) that are performed in regular periods.

The values argument should contain at least one positive and one negative value, in order to calculate the modified internal rate. If not , TIM will return the error value #¡DIV/O!

Financing_rate = is the interest rate payed for the money used in the cash flow

Reinvestment_rate = is the interest rate obtained from the cash flows measured by their reinvestment.

Eg:

For an investment table like the following:

Cell G3=MIRR(A3:D3;E3;F3)

Continue to the next page....