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.