Your task for this question is this: write an excel spreadsheet program to take as an input any 4×4
square matrix S and do the following calculations:
1. Check whether the matrix is symmetric around the main diagonal and report an error message is this is
not the case.
2. Compute the cholesky square root of the matrix by following the procedure explained below.
3. In applying this procedure, the entry in row i and column i of the square root matrix is
1
2
1
T S T ii ii ki ik ( ) and in calculating it we have to first compute S T ii ki ik 1 1( )2 .
If this quantity is negative then we can’t compute the square root of the matrix. If this happens it means that
the matrix we are trying to compute the cholesky square root of is not a valid covariance matrix. Your
spreadsheet should report an error message if you find that 1 2
1
S T ii ki ik ( ) is negative for some value of i.
4. Check that the cholesky square root matrix C you calculated does satisfy the property C C S T and
report whether or not this is true
5. Write some user documentation for the spreadsheet explaining to the user how to use the spreadsheet.
The cholesky square root of a matrix is often used in multivariate monte carlo simulations to take account of
multiple random variables which may be correlated rather than independent.
To get started on this problem, try applying it to the matrix
1 2 3 2 3 0
2 3 1 1 2 0
2 3 1 2 1 0
0 0 0 1
S
You need to look at the algorithm explained below and then create excel code to calculate the 16 entries in
the cholesky square root of the matrix. Your job is to create the spreadsheet regardless of your understanding
of the mathematics involved. Programmers often have to write computer code to implement calculations
even if they don’t understand the theory behind the calculations. You should carefully work through the
calculations for the worked example set out below.
2
Background Information about Cholesky Matrix
A vector valued random variable is a vector with n components
1 2
…
n
x x
x
x
where each of the components of the vector is a 1-dimensional random variable
Sometimes it is notationally convenient to write vectors as row vectors.
The tranpose of the above column vector is x’ x x x 1 2 … n which is a row vector
A vector valued random variable has
a vector of means (a “mean vector”) and
a matrix as its variance (the ” covariance matrix”)
the transpose of the mean vector is the row vector ‘ 1 2 … n where
1 1 2 2 E X E X E X ( ) ( ) … ( ) n n are the expectations (averages)
The covariance matrix of the random variable x Rn is a nn matrix, usually denoted by
X
S
This covariance matrix is
11 12 1n
21 22 2n
X
n1 n2 nn
σ σ … σ
σ σ … σ
S =
… … … …
σ σ … σ
The entry in row i and column j is ij cov( , ) xi j x which is the covariance between the ith and jth
components of the vector X. This is the product of
the standard deviation of
xi ,
the standard deviation of
x j and
the correlation
ij between variables xi and x j
Note that
ij ji i j cov( , ) x x i j ij . The covariance matrix must be symmetric around the main
diagonal.
3
Important result
Suppose that y y y y ‘ 1 2 … n is a row vector of constants, Then the sum
1
.
n
i i
i
S y x
is also a random variable (a one dimensional random variable) and we can write down formulae for the
mean of the sum S and the variance of the sum S
1 1
. .
n n
i i i i
i i
E S y E x y
2 2
1 1 1
var 2
n n n
i ij j i i i ij j
i j i i j
S y y y y y
These formulae can be written more compactly in matrix notation as follows
Means: E S y y T T X X
We can compute the expectation as matrix product of
1. the transpose yT of the vector y of constants 2 and
2. the vector
X of means
Variances:
We can write the variance formula in matrix notation as var S =y ×S ×y T X
The variance of S is the product of the transpose of vector y (with n elements) times the covariance matrix of
X (which is a n×n matrx) times the vector y (with n elements). This type of expression is called a “quadratic
form”
Definition: positive definiteness of a matrix
A variance of any random variable is a sum of squares. A sum of squares cannot be negative. It follows that
var 0 S y S y ‘ X for all vectors y R n
If the matrix
X
S is such that the matrix expression var 0 S y S y ‘ X for all vectors y except the
“zero vector” which has all components equal to zero, then we say that S is positive definite. Basically it
means that every linear combination of components of the vector x has a variance which is strictly positive.
4
Definition Cholesky Square Root of a Positive Definite Matrix
Theorem: Let S be a n-by-n positive definite matrix
11 12 13 1
21 22 23 2
31 32 33 3
1 2 3
…
…
…
… … … … …
…
n n n
n n n nn
S S S S
S S S S
S S S S S
S S S S
Then there exists a n-by-n lower triangular matrix
11
21 22
31 32 33
1 2 , 1
0 0 … 0
0 … 0
… 0
… … … … …
…
n n n n nn
C
C C
C C C C
C C C C
such that S C C T where
11 21 31 1
22 32 2
33
, 1
,
…
0 …
0 0 … …
… … … …
0 0 … 0
n n
T
n n
n n
C C C C
C C C
C C
C C
is upper triangular
for example let ࡿൌ
then ൌ is the cholesky square root matrix of S and is lower triangular
and ࢀൌ is the transpose of the cholesky square root and is upper triangular
it is easily verified that ൌ ൈ ൌ
5
The procedure for computing C from S is as follows:
Let the matrix T be the transpose of C, so that S T T T
Note that the matrix T = CT is upper triangular so all elements below the main diagonal are zero and do not
need to be calculated.
To be “upper triangular means that all numbers in the matrix below the main diagonal (from the top left
corner to the bottom right corner) are zeros.
For example
1 0.9 0.81
0 1 0.9
0 0 0
T
is an “upper triangular” 3×3 matrix
We apply equations 1 and 2 in turn as we work from i = 1 to n in order
Use equation 1 to compute the diagonal entries in the matrix
Equation 1: Tii = 1 2
1
S T ii ki ik ( )
Use equation 2 to compute the remaining entries in each row, after computing the diagonal
Equation 2: Tij = 1
1
1
ij ki kj i k ( )( )
ii
S T T
T
for j = (i+1) to n
Example: compute the cholesky square root of the matrix
16 8 12 4
8 5 11 4
12 11 70 31
4 4 31 63
S
solution
First we compute the row 1 of the matrix T
Apply Equation 1:
Tii = 1 2
1
S T ii ki ik ( ) for i=1 to get T S T S 11 11 1 11 1 1 k 1( ) 0 4 k 2
Note: in the sum 1 1 2
1
k ( ) Tki ,
which is part of the expression 11 11 1 1 1 2
1
T S T k ( ) k ,
the lower limit of summation is k = 1 and
the upper limit of summation is k = 0.
In this situation we define the sum to be zero, so 1 1 k 1( ) 0 Tki 2
Now apply Equation 2: Tij = 1
1
1
ij ki kj i k ( )( )
ii
S T T
T
6
for j = 2 to 4 for the case where i=1, to compute the other entries in row 1 of T
12 12 1 2 1 1 1
11
1 1
( )( ) 8 0 2
T S T T T k k k 4
13 13 1 3 1 1 1
11
1 1
( )( ) 12 0 3
T S T T T k k k 4
14 14 1 4 1 1 1
11
1 1
( )( ) 4 0 1
T S T T T k k k 4
Note that in the expression 12 12 1 2 1 1 1
11
1 1
( )( ) 8 0 2
T S T T T k k k 4
We have the sum 1 1
1 2
1
k ( )( ) T T k k and in this sum
the lower limit of summation is k=1 and
the upper limit of summation is k=1-1=0.
In this situation we define the sum to be zero.
Now we have worked out all of the entries in the first row of T
Next we compute the second row of T:
Since T is upper triangular we know that T21 0.
This is because
T21 0is the entry in row 2, column 1, so it is below the main diagonal of the matrix.
apply Equation 1: Tii = 1 2
1
S T ii ki ik ( ) for i=2
to get
2 1
2 2 2
22 22 2 22 12
1
T S T S T k ( ) 5 2 1 k
apply Equation 2: Tij = 1
1
1
ij ki kj ik ( )( )
ii
S T T
T
for j = 3 to 4 for the case where i=2,
to compute the other entries in row 2 of T
23 23 2 3 23 12 13 2 1 1
22 22
1 1 1
( )( ) ( )( ) 11 2 3 5
T S T T S T T T T k k k 1
24 24 24 12 14 2 1 1
22 22
1 1 1
( )( ) ( )( ) 4 2 1 2
T S T T S T T T T k ki kj 1
Now we have computed the second row of T
Next we compute the third row of T:
since T is upper triangular we know that T T 31 32 0
apply Equation 1: Tii = 1 2
1
S T ii ki ik ( ) for i=3 to get
T S T S T S T T 33 33 33 3 33 13 23 3 1 3 1 k k 1 1 ( ) ( ) 70 3 5 6 ki k 2 2 2 2 2 2
7
Apply Equation 2: Tij = 1
1
1
ij ki kj ik ( )( )
ii
S T T
T
for j = 4 to 4 for the case where i=3, to compute the other entries in row 3 of T
34 34 34 13 14 23 24 3 1 1
33 33
1 1
T S T T S T T T T k ( )( ) ki kj
T T
34 1 31 3 1 5 2 3
6
T
Next we compute the fourth (last) row of T:
Since T is upper triangular we know that T T T 41 42 44 0 . This gives us the first 3 out of the 4 numbers on
the last row of the matrix T.
apply Equation 1: Tii = 1 2
1
S T ii ki ik ( ) for i=4 to get
T S T S T T T 44 44 44 14 24 34 4 1 k 1( ) ki 2 2 2 2
T44 63 1 2 3 7 2 2 2
Hence the matrix T is
4 2 3 1
0 1 5 2
0 0 6 3
0 0 0 7
T
T C
And the matrix C is
4 0 0 0
2 1 0 0
3 5 6 0
1 2 3 7
T
C T
It is easily verified that C C S T
Question 2: 30 marks
You are considering taking out a 10 year lease on a piece of specialised farming equipment.
The lease will
have an interest rate of 6% p.a. convertible monthly
be for an amount financed of $1,000,000
have a residual payment of 20% of the amount financed paid at the end of the term (time 10 years) and
by making this payment the lessee can buy the equipment. However the lessee doesn’t have to buy the
equipment, and they can decide to not make the payment of the residual value and let the lessor keep the
equipment instead.
be taken out on 1 january 2017
be structured so as to have lease rental payments of level amount only in the months of September,
October, November, and December of each year, paid on the first day of those months. The lease
payment months coincide with when the farmer is harvesting the crop and receiving the revenues from
selling it.
8
You may decide to exit the lease contract early, at any time from exactly 5 years after the lease contract is
entered into. However if you do, you must make a penalty payment equal to the present value of the future
lease cashflows, This present value is computed at an interest rate of 3% p.a. convertible monthly.
(i) Assuming that the residual payment will definitely be paid, show that the equation of value for the lease
rentals payable in the months of September, October, November, and December is
R 1 1 0.20 1 i j L L j 7 1 1 1 1 i j 4 n n
i j
where
R is the amount of the lease payment made in the months of September, October, November, and
December, and
L is the amount financed (cost of the asset being leased) and
i is the interest rate per month and
j is the effective annual rate of interest equivalent to a rate of i per month
n is the the term of the lease in years
6 marks
(ii) What excel function could you use to compute j from i? Show that i = 0.5% and j = 6.1678% to 4
decimal places.
2 marks
(iii) Re-arrange the above equation to make R the subject of the equation (the thing on the left hand side of
the equals sign). Check whether or not the following excel code would correctly implement the calculation
of R when i=6% p.a. convertible monthly. In doing this identify which parts of the excel code compute
which parts of the above equation of value for R. The excel code is
=1e6*(1-0.2*PV(0.061678, 10,0,-1,0))/(PV(0.06/12,4,-1,0,0)*PV(0.06/12,7,0,-1,0)*PV(0.061678,10,-1,0,1))
6 marks
(iv) Check if it is true that R = $43,024.54 is the monthly payment made in the months of September,
October, November, and December. If this result is incorrect, what is the correct result for the monthly
payment due in those months?
4 marks
(v) Write down a mathematical formula (in terms of the rental R from part (iv)) for the penalty payment you
would need to make at time 5 years if you wanted to terminate the lease early.
Use this information to compute the penalty payment made at time 5 years if you decide to terminate the
lease at that time. Do this in a spreadsheet
6 marks
(vi) The above formulae assume that the residual payment will definitely be paid at the end of the lease term.
If instead the lessee can choose whether or not to make the payment how would that impact on the validity
of the formulae? Would the formulae still be valid? give reasons for your answer.
6 marks
9
Question 3: 40 marks
Part A
You work for a bank. Your employer pays a percentage c=20% of your pretax salary of S= $90,000 into
a superannuation fund. The contribution into the super fund would thus be $18,000 per year, and it is
paid in monthly. The superannuation fund pays tax on this money at a rate of t1=15%, so after the
contributions tax, it receives an annual contribution after tax of C1 = $15,300, paid as a monthly
contribution after tax of $1,275 per month in arrears in the first year.
Assuming that
your salary grows at rate g1 = 4% per year, once a year in arrears
the contributions are paid into the fund at the end of each month
the contributions are constant in each year but increase once a year at the end of the year
the superannuation fund invests the money till you reach retirement at age T=60, at a fund earning
rate of i=8% p.a. effective. Ignore tax on the super fund investment earnings, or equivalently,
assume this is the after tax rate of return on the investment earnings.
you are aged exactly t=35 at entry to the superannuation fund
We want to compute the amount of money accumulated in the superfund at the age of T
(i) explain how you could use the NOMINAL function to compute the interest rate per month that is
equivalent to i=8% p.a. effective. What are the inputs to this function?
2 marks
(ii) Explain how you could use the FV function to compute the annual contribution received at the end of
1 year that has the same present value at rate i as the monthly contributions paid during the year.
What are the inputs to the FV function?
3 marks
(iii)Explain how you would compute the real interest rate that corresponds to a nominal rate of i=6% and
an inflation rate of g1=4%
1 marks
(iv)Explain how you could use the PV function to compute the present value of annual contributions
which are paid annually in arrears, which starts at $1 in year 1, and which grow at rate g per annum,
valued at rate i per annum. What are the inputs to the PV function?
3 marks
(v) Explain how you could use the FV function to compute a factor to accumulate $1 invested at time 0
at rate i per annum effective to time n in the future. What are the inputs to the FV function?
3 marks
Part B: Write a spreadsheet to do this calculation.
(i) Make
the entry age, (a whole number)
retirement age, (a whole number)
salary per year at the start,
employer contribution rate as a % of salary
annual salary growth rate and
the fund earning rate
the super fund’s tax rate on contributions
into “input variables” that can be entered by the user of the spreadsheet and so that these can be changed
to other values.
The spreadsheet should be able to do similar calculations for any valid set of inputs
10
Hence compute the amount accumulated in the super fund by retirement age
What difference would it make to your result if the contributions were paid into the fund at the start
of each year instead of at the end of each month during the year for each year? compute the
accumulated lump sum in this case
14 marks
(ii) Using the following assumptions do a sensitivity analysis of the ratio of the accumulated lump
sum achieved at retirement to the salary received in the final year before retirment
Variable Base case Pessimistic value Optimistic value
Entry age (t) 35 40 30
Planned retirement age (T) 60 55 65
Annual salary at entry to super fund (S) $90,000.00 $75,000.00 $108,000.00
Employer contribution rate (c) 20% 15% 25%
Salary growth rate (g) 4% 2% 6%
Fund earning rate (i) 8% 4% 12%
create a brief table setting out the results of the sensitivity analysis, and comment on which variables
the ratio is most sensitive to
7 marks
create a 2 way table showing the combined effect of changing the fund earning rate (15%, 20%,
25%) and changing the term of superfund membership (15 years, 25 years and 35 years)
on the ratio of the lump sum received at retirement to the salary received in the final year before
retirement. This should be a table with 3 rows and 3 columns. Comment on the results
7 marks
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!