IMPORTANT INSTALLATION INFORMATION The SOLVER macros in this workbook will only run if your Excel is set up as follows. You must have SOLVER installed with your Excel. Go to Tools Menu and see whether item Solver appears there. If it does not, go to Tools - Add-ins and tick "Solver Add-in". This 1st step will allow you to use SOLVER from Excel but because SOLVER is also called by a VBA macro, you will also need to establish a reference to the Solver add-in in the VBA editor: With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder.
Programmed by and copyright Kurt Hess March 2004,
[email protected]
Illustration of Extended Nelson & Siegel Spot Rate Model Fitting Extended Nelson & Siegel Spot Rate with Solver
Kurt Hess,
[email protected]
25891251.xls Introduction 12/02/2009
Trusted by over 1 million members
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
Illustration of Extended Nelson & Siegel Spot Rate Model programmed by Kurt Hess May 2004,
[email protected] Time to maturity m 3.0 30.00 Long-run levels of interest rates \u03b20
5.0%
50
Short-run component
2.0%
120
Medium-term component
8.0%
80
Decay parameter 1 Decay parameter 2
\u03c42
Spot rate at time t
rt,i
determines magnitude and the direction of the hump
1.000
100
determines decay of short-term component, must be > 0
1.200
120
determines decay of medium-term component, must be > 0
7.9141% #VALUE! with VBA Function
Components of N&S spot rate Comp 1
5.000%\u03b20
Comp 2
1.903%
Comp 3
0.315%
10.0%
9.0%
9.0% 8.0%
8.0%
7.91%
7.0%
7.0% 6.0%
6.0%
5.0%
5.0%
4.0%
4.0% 3.0%
3.0%
2.0%
2.0%
1.0%
1.0%
0.0% 0
2
4
6
8
10
12
0.0% 0
2
4
6
8
Trusted by over 1 million members
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
Fitting Extended Nelson & Siegel Spot Rate with Solver
Time to maturity
m
3.0
30
programmed by Kurt Hess May 2004,
[email protected] Before using the minimization macros, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools
\u03b20 Long-run levels of interest rates \u03b21 Short-run component
-2.80%
71.98
\u03b22
-1.40%
-14.02
determines magnitude and the direction of the hump determines decay of short-term component, must be > 0
Medium-term component
7.30%
73.02
Decay parameter 1
\u03c41
0.412
41.19
Decay parameter 2
\u03c42
2.905
290.47 determines decay of medium-term component, must be > 0
Spot rate at time t
rt,i
6.5429%
see formulas Objective Functions Non-weighted objective function x103 Inverse duration weighted function x 105
Bond Data
Short-term rate Settlement date Issuer NZ Government NZ Government NZ Government NZ Government NZ Government NZ Government NZ Government NZ Government
Set Random Values
#VALUE! Minimize #VALUE! Minimize
t
ep hrough
o p tim iz a tio n
4.50% 14-Feb-99
120.0%
8.0%
Initial Guess Values: Default Values
9.0%
100.0%
7.0%
6.54%
6.0%
0.82178
80.0%
5.0%
60.0%
4.0% 3.0%
40.0%
2.0%
20.0%
1.0% 0.0% 0
1
2
3
4
5
Coupon Maturity Bid Ask Mid Clean Mid Dirty 6.50% 15-Feb-00 100.563 100.583 100.57% #VALUE! 8.00% 15-Feb-01 102.786 102.854 102.82% #VALUE! 10.00% 15-Mar-02 108.406 108.526 108.47% #VALUE! 5.50% 15-Apr-03 96.673 96.827 96.75% #VALUE! 8.00% 15-Apr-04 105.034 105.234 105.13% #VALUE! 8.00% 15-Nov-06 106.518 106.809 106.66% #VALUE! 7.00% 15-Jul-09 100.549 100.903 100.73% #VALUE! 6.00% 15-Nov-11 91.666 92.049 91.86% #VALUE!
6
7
8
9
10
0.0% 0
1
2
Model Price Duration Weights (wi) (cheap) / rich #VALUE! 0.96 0.36 #VALUE! #VALUE! 1.82 0.19 #VALUE! #VALUE! 2.65 0.13 #VALUE! #VALUE! 3.71 0.09 #VALUE! #VALUE! 4.25 0.08 #VALUE! #VALUE! 5.88 0.06 #VALUE! #VALUE! 7.54 0.05 #VALUE! #VALUE! 8.77 0.04 #VALUE!
3
4
5
6
7
8
9
Trusted by over 1 million members
Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.
−
N
w iε i
min i =1
w i=
2
1 / Di
1
1/ D j
Θ=
Subject to:
mm i n
0≤ r
m =∞
exp − r
j
m,Θ
=β
with ε t , j ~N
ε i = P i −P i
0≤ r
r
0
1− e
β1
m
N: number of bonds in universe
N
∑ j=
Pi: Price of bond i ^Pi: Model price of bond i
0, σ
τ
1
τ1
β2
1−e
m
τ2
2
β 0,β 1,β 2,τ 1,τ 2
Rate r at time 0 must remain positive (mmin is a value just slightly larger than 0) Rate at the end of the estimation horizon must remain positive
mk
mk
≥ exp
−r
mk 1
mk 1
∀ m k mm a x Discount functions must be non-increasing
References: Nelson, C. R. & Siegel, A. F. (1987). Parsimonious modeling of yield curves, Journal of Business 60(4): 473—489.
m τ
2
−
−e
m
τ2