Step by step exercise. Financial functions
Practise the use of Financial Functions in the creation of worksheets with Excel2003.
Step by step exercise.
We are going to develop a practical example to calculate the payment of a loan based on constant payments and a fixed interest rate.
1 If Excel2003 is not open, open it in order to perform the exercise.
To carry out this exercise we are going to use the PAYMENT function.
2 Click on the button on the formula bar.
The Insert function dialogue box will appear.
3 Select the Financial option fom the Select a category box.
4 Select PMT from the Select a function box.
The syntax of the PAYMENT function is PMT(rate;nper;pv;fv;type), we are going to describe each one of its parameters.
rate = is the interest type of the loan.
nper = is the total number of payments of the loan.
pv = is the actual value of the total of the payments.
fv = is the future value or balance in cash that you wish to obtain after the final payment. If fv is omitted, it is assumed to be 0, that is, after the final payment there is no outstanding balance, which accurs when processing a loan.
type = indicates the expiration of the payments.
( type = 0 ) --> at the end of the period
( type = 1 ) --> at the beginning of the period
Now that we know the parameters needed by the function, we can create the example:
We are going to request a loan of 100,000 € over a period of 30 years, that is 30*12=360 no. of monthly payments at an interest of 6%.
So then, we already have an example of a loan.
5 Situate yourself in cell A1 and write Loan.
6 Situate yourself in cell A2 and write Rate
7 Situate yourself in cell A3 and write No. of payments
8 Situate yourself in cell A4 and write Type
9 Situate yourself in cell A5 and write Monthly quota
10 Situate yourself in cell B1 and write 100.000€
11 Situate yourself in cell B2 and write 6%
12 Situate yourself in cell B3 and write 360
13 Situate yourself in cell B4 and write 0
14 Situate yourself in cell B5 and write =PMT(B2/12;B3;B1;0;B4)
With this function we indicate that at the expiration of payments is realised at the end of the period, and that once these payments are complete no more outstanding quotas exist.
We should obtain -599,55 €
as a result ie. the monthly quota. The numbers appears as negative
as it is an amount being payed, eg deposits into savings accounts, loan
quotas, are represented by negative numbers; the cash that it receives
is represented by positive numbers.
With the PMT function we can also calculate how much money we need to deposit each month in order to save X amount of money in X amount of years.
We will now calculate how we can save 30.0000€ in 5 years, with an interest rate of 6%.
15 Situate yourself in cell C1 and write Save
16 Situate yourself in cell C2 and write Annual rate
17 Situate yourself in cell C3 and write Years
18 Situate yourself in cell C4 and write Monthly deposit
19 Situate yourself in cell D1 and write 30.000 €
20 Situate yourself in cell D2 and write 6%
21 Situate yourself in cell D3 and write 5
22 Situate yourself in cell D4 and write PMT(D2/12;D3*12;0;D1)
As a result , we should obtain the amount of -429,98 €. in cell D4.
Continue to the next page...
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.