Working with financial functions (IV)

PV function(rate;nper;payment;vf;type)

Returns the actual value of an investment. The actual value is the sum of a series of payments to be made in the future.

PV syntax(rate;nper;payment;type)

RATE = is the interest rate per period

Nper = is the total of periods in an annuity

Payment = is the payment effected in each period and does not change during the life of the annuity.

Vf = is the future value or balance in cash that you wish to obtain after effecting the final page. If the vf argument is omitted, the value will be assumed to be zero. (a loan for example)

Type = is the number 0 (expiration of payments at the end of the period), or 1 (expiration at the beginning of the period)

Eg: We plan to design a pension plan that will pay us 500€ monthly during 15 years. The plan costs us 35.000€ and the money earns an annual interest of 10%. We use the VA function to calculate whether it is worth it to make a retirement plan.

We therefore write the PV(10%/12;15*12;500), which should return -46.528,72 € which appears in negative because it is the money that will be payed. And we can now see that it would be worthwhile as the money invested was 35.000 € and we have a return of 46.528,72 €.

FV function(rate;nper;payment;type)

Returns the future value of an investment based on periodic payments and constants, with a fixed interest rate.

Observations

Maintains uniformity in the use of the units with which the rate and nper arguments are specified. If monthly payments are made to a 5 year loan with an annual interest of 10%, it will use 10/12% for the rate argument and 5*12 for the nper argument. If annual payments are made to the same loan, it will use 10% for the rate argument and 5 for the nper argument.

FV syntax(rate:nper;va;type)

Rate = the interest rate for the period

Nper = is the total amount of payments of an annuity

Payment = is the payment made at every period, and can not be changed during the validity of the annuity.

Va = is the actual value of the total quantity of a series of future payments. If the argument is omitted, it is considerred 0 (zero)

Type = indicates when the pyments are overcome(0 final of the period, 1 beginning of the period). If the argument is omitted, it is considerred 0 (zero)

Eg: We are planning to save money until a limited date and with a starting date. With an initial deposit of 2000€ we know that the interest returned by our savings account is 7%, every month we will deposit 100€ and we will wait for one year to see what result we will be offered.

We use the FV(7%/12;12;-100;-2000) function and we obtain a result of 3.383,84 € which is not bad as we have already profited 183,84 € in one year by doing nothing, just saving.

NPV(rate;value1;value2;...)

Returns the actual net value of the investment from a discount rate and a series of future payments.

NPV syntax (rate;value1; value 2;.......)

Rate = is the discount rate during the period

Value 1; value 2....are the arguments 1 to 29 that represent the payments and earnings. Value 1; value 2...should have the same duration and should occur at the end of each period.

NPV uses the value 1; value 2... to interprete the order of the box flows. Payment and earnings values need to be introduced in the correct order.

Arguments consist of numbers, empty cells, logical values, are calculated, the arguments that consist of error or text values that can not be translated to numbers are transferred upward.

Observations

The NPV investment starts one period before the box flow date of value 1, and ends with the final box flow of the list. The NPV calculation is based on future flow boxes. If the first cash flow occurs at the beginning of the first period, the fisrt value should be added to the NPV result, which is not included in the value arguments.

Eg: Let us consider an investment of 55.000 € and we will wait to receive the ingresses in the next 5 years, the annual discount rate is 7%.

We have the following earnings table:

We write the NPV(7%;B3:F3)+A3 function and we obtain -4.657 €, which tells us that we have still not started to receive profits even though the profits have surpassed the initial investent.