CS计算机代考程序代写 finance Excel City University of Hong Kong Department of Economics and Finance

City University of Hong Kong Department of Economics and Finance
Course EF 5213 Assignment #5 (due May 2, 2021) 1. Use EXCEL and VBA to implement the binomial tree with recombining lattice for random normal
short rate given by
drt  (t)dt  (t)dzt
For the calibration of the binomial tree (see note), you can consider as input market data: (1) current zero-coupon bond prices {P0(1), P0(2), ….} and (2) current bond price volatility {0(1), 0(2), ….} with maturities i  it exactly match the time nodes of the tree. Use the binomial tree to price an interest rate caplet defined below using current market bond prices and volatilities as
An interest rate caplet provides a ceiling of Rcap on LIBOR rate at future time T with borrowing term  and notional principle M. The current price of the caplet under risk-neutral pricing can be written as
0
C0  Ê( e ∫T rt dt max{ M  BT(T  ) , 0 }| r0 )
where BT(T  ) is the forward zero-coupon bond price with face value being (1  Rcap)M and maturity at T  . Use the binomial tree to determine the current price of a caplet written on a LIBOR with the following option parameters:
Zero-Coupon Bond Data :
Number of Bonds = Maturity Terms = Prices = Volatilities =
8 0.083 0.9963 0.08%
0.250 0.9887 0.20%
0.500 0.9771 0.40%
1.000 0.9533 0.70%
2.000 0.9055 1.10%
4.000 0.8164 1.30%
7.000 18.000 0.6948 0.4667 1.40% 1.50%
(year) (par = 1) (per year)
T – Maturity of the caplet (e.g. three years)
Rcap – Caplet rate (e.g. 4.5%)
 – Borrowing term of the underlying LIBOR rate (e.g. 6-month)
M – Notional principle of the underlying LIBOR rate (e.g. $10,000)
2. Consider the Ho-Lee model with constant volatility given by drt  ( F0(t)  2t)dt  dzt
( 40 points )
t
where F0(t) is the value of forward short rate. Use Monte Carlo pricing to determine the current price of the caplet in Question 1 based on the above Ho-Lee model. Under discrete time ti  it with increment t, we can calibrate the model using the two current term structures given in Question 1 as,
(30 points)
[  F0(t)] (1)2ln( P20(ti1) ), (1)0(t) , r0 (1)ln(P0(t))
t t  ti t P0(ti)P0(ti  2) t t

__________________________________________________________________________________ Note 1: Consider the risk-neutral short rate given by the following random normal process as
drt  (t)dt  (t)dzt
The binomial lattice are not recombining in this model, but the error is suppressed by t√t relative to r  √t. Thus, we can consider a recombining lattice as in the Ho-Lee Model, and the binomial tree
should converge to the above short rate process as t  0. Since ri1j rij (ti)t(ti)√t
ri  1 j  1  ri j  (ti)t  (ti)√t  ri  1 j  i  1 , where i  1   2(ti)√t
ri  1 j rij
1⁄2 1⁄2
1⁄2
The branching rule for rij should satisfy the condition that ri  1 j  1  ri  1 j  i  1 ri j  1 Similarly, the branching rule for rij  1 is given by ri  1 j  2  ri  1 j  1  i  1 Therecombiningconditionthenrequiresthat ri1j2 ri1j 2i1
Running down from top to bottom nodes, we should have ri  1 j  i  1  j i  1 , j  0, 1, … , i  1
Model Calibration : Calibrating the model parameters { 0, (1, 1) ,… , (Ntree, Ntree) } using current yield curve { P0(t) , P0(2t) , …. , P0( (Ntree  1)t ) }
and current bond price volatility { 0(2t) , 0(3t) , …. , 0( (Ntree  1)t ) } Form1, P0,0(t)er0,0t [12 ($1)12 ($1)]($1)e0t
solve for 0 from e 0t  P0(t)/($1) to give 0   (1/t)lnZ0(t)
For m2, P1,0(2t)er1,0t [12 ($1)12 ($1)]($1)e1t
P1,1(2t)  e r1,1t [ 12 ($1)  12 ($1) ]  ($1) e r1,1t  ($1) e (1 1)t
P0,0(2t)  e r0,0t [ 12 P1,0(2t)  12 P1,1(2t) ]  e 0t [ 12 ($1) e 1t  12 ($1) e (1 1)t ]
0(2t)√t  1 log(P11(2t))   1 1t 2 P10(2t) 2
Solve for 1 and 1 from P0(2t)  e 0t [ 12 ($1) e 1t  12 ($1) e (1 1)t ] 0(2t)√t   12 1t
For m  k  1, determine k and k based on {0, 1, … , k  1} and {1, … , k  1}
We can backward iterate Pij(mt)  e (i  ji)t [ 12 Pi  1 j(mt)  12 Pi  1 j  1(mt) ]
to give P10(mt), P11(mt), and P00(mt) , starting with Pm j(mt)  $1
Solve for k and k from P0(mt)  P00(mt) and 0(mt)√t  1 log(P11(mt)) 2 P10 (mt)
ri  1 j  1 ri  1 j  2
1⁄2
__________________________________________________________________________________