Step by step exercise. Financial functions


Step by step exercise.

 

We are now going to calculate the interest payed over a period of time to a loan, eg the interest in the first example.

 

To perform this exercise we need to use the IPMT function

This function has the following syntax IPMT(rate;per;nper;pv;fv;type)

rate = is the type of interest of the loan.

Per = specifies the period and must be in the range 1 to nper.

nper = is the total number of payment periods in an annuity.

Pv = is the present value, or the lump-sum amount that a series of future payments is worth right 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 (zero), that is, the future value of a loan is 0.

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

( type = 0 ) --> the end of the period

( type = 1 ) --> the start of the period

 

23 Situate yourself in cell E1 and write Loan

24 Situate yourself in cell E2 and write Annual rate

25 Situate yourself in cell E3 and write Interest on the quota no.

26 Situate yourself in cell E4 and write Amount of quotas

27 Situate yourself in cell E5 and write Interest

28 Situate yourself in cell F1 and write 100.000 €

29 Situate yourself in cell F2 and write 6%

30 Situate yourself in cell F3 and write 1

31 Situate yourself in cell F4 and write 360

32 Situate yourself in cell F5 and write PMT(F2/12;F3;F4)

This function should return to us -500,00 € which is the interest payed in the first quota of the loan. Changing the value in F3 you can see the interest payed in each case.

We are now going to calculate the quotas amortized for a loan, we will again base it on the first exercise.

 

To create this exercise we will use the PPMT function

This function has the following syntax PPMT(rate;per;nper;pv;fv;type)

rate = is the type of interest of the loan.

Per = specifies the period and must be in the range 1 to nper.

nper = is the total number of payment periods in an annuity.

pv = is the actual value of the total payments

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 (zero), that is, the future value of a loan is 0.

type =indicates the expiration of the payments

( type = 0 ) --> at the end of the period

( type = 1 ) --> at the start of the period

 

33 Situate yourself in cell A8 and write Loan

34 Situate yourself in cell A9 and write Annual rate

35 Situate yourself in cell A10 and write calculate amortization in quota no.

36 Situate yourself in cell A11 and write Total quotas

37 Situate yourself in cell A12 and write Amortized

38 Situate yourself in cell B8 and write 100.000

39 Situate yourself in cell B9 and write 6%

40 Situate yourself in cell B10 and write 1

41 Situate yourself in cell B11 and write 360

42 Situate yourself in cell B12 and write PPMT(B9/12;B10;B11;B8)

You should obtain a result of -99,55 € in cell B12

 

43 Save the workbook in the My documents folder of the hard drive, naming it Financial functions

44 Close the workbook.

 
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.