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.
