# Excel Vba Finance - Excel

Document Sample

```					Interest Rate One-Factor Equilibrium Models
Source: Hull, John C., Options, Futures & Other Derivatives. Fourth edition (2000). Prentice-Hall. P. 567.
Models:
Vasicek, O. 1977 "An Equilibrium Characterization of the term structure." Journal of Financial Economics 5: 177
Cox, Ingersoll, and Ross. "A Theory of the Term Structure of Interest Rates". Econometrica, 53 (1985). 385

Vasicek Model (discrete version)

Dr  a b  r Dt  se Dt                                                             Simulation of short

12.0%
Stochastic process for short-term interest rate r:
a: "strength" at which r is pulled back to g
b: long-term equilibrium of short-term rates                                            10.0%
s: volatility superimposed (annualized)

e: is a random drawing from a standardized normal distribution, F(0,1)
Cox, Ingersoll, Ross Model (discrete version)

Dr  ab  rDt  rse Dt
8.0%

6.0%
Parameters as for the Vasicek model.
Because the volatility is proportional to the square root of r, r cannot
become negative. As the rates increase, their volatility increases. At the
same time, the model has the same mean-reverting or "pull-back"
4.0%
properties as the Vasicek model.
Numerical examples (press F9 to generate new random numbers)
Vasicek    CIR                                               2.0%
Rate r0 at t=0                  8.00%       8.00%
Total simulation time (T)             2          2 year(s)
"Pullback" a                      0.07       0.07
Equilibrium b                   6.00%       6.00%                                         0.0%
Volatility s                    3.00%     10.61%
Dt    0.0067
on (2000). Prentice-Hall. P. 567.

" Journal of Financial Economics 5: 177 -188.
tes". Econometrica, 53 (1985). 385 -407.

Simulation of short-term interest rates

12.0%

10.0%

8.0%

6.0%

4.0%

2.0%

0.0%
0              0.5   1          1.5   2   2.5
Time
Vasicek    Cox et al.   Equilibrium line b
Period         Time          e       r + Dr     r + Dr                 0
0          0                   8.00%      8.00%             2
1   0.006667   -1.01528        7.75%      7.75%
2   0.013333    1.10613        8.02%      8.02%   Rate at t=0
3       0.02   0.281191        8.09%      8.08%             0
4   0.026667   0.351288        8.17%      8.17%         0.05
5   0.033333   -0.64449        8.01%      8.01%
Vasicek                 6       0.04   0.142704        8.05%      8.04%
7   0.046667   2.150234        8.57%      8.57%
8   0.053333   -0.80218        8.38%      8.37%
9       0.06   -2.16806        7.84%      7.82%
10   0.066667   0.434859        7.95%      7.93%
Cox et al.             11   0.073333   -0.70097        7.78%      7.75%
12       0.08   -0.95292        7.54%      7.52%
13   0.086667   0.107772        7.57%      7.55%
14   0.093333   -1.58964        7.18%      7.17%
15        0.1   1.211004        7.47%      7.45%
Equilibrium line      b16   0.106667   -0.80693        7.28%      7.26%
17   0.113333    0.57215        7.42%      7.39%
18       0.12   2.066851        7.92%      7.88%
19   0.126667   0.222435        7.98%      7.93%
20   0.133333   0.255689        8.04%      7.99%
21       0.14   2.838007        8.73%      8.69%
Rate at t=0
22   0.146667   -1.32751        8.40%      8.35%
23   0.153333   -1.57906        8.02%      7.95%
24       0.16   -0.88119        7.80%      7.73%
25   0.166667   0.789203        7.99%      7.92%
26   0.173333   -0.19775        7.94%      7.87%
2.5                          27       0.18   0.847611        8.15%      8.08%
28   0.186667   -0.78308        7.96%      7.89%
29   0.193333   0.265498        8.02%      7.95%
30        0.2    0.31393        8.10%      8.02%
31   0.206667   -1.92801        7.62%      7.55%
32   0.213333   -1.15936        7.34%      7.27%
33       0.22   -1.00134        7.09%      7.04%
34   0.226667   0.579033        7.23%      7.17%
35   0.233333   -0.10538        7.21%      7.15%
36       0.24   1.524462        7.58%      7.50%
37   0.246667   -0.24031        7.52%      7.44%
38   0.253333   0.165005        7.56%      7.48%
39       0.26   -1.29404        7.24%      7.17%
40   0.266667   -0.43466        7.14%      7.07%
41   0.273333   -0.43289        7.03%      6.97%
42       0.28   -0.57515        6.89%      6.84%
43   0.286667   1.181382        7.18%      7.11%
44   0.293333   0.334312        7.26%      7.18%
45        0.3   1.186117    7.55%   7.46%
46   0.306667   0.761528    7.73%   7.64%
47   0.313333   -1.64104    7.33%   7.24%
48       0.32   -0.20165    7.28%   7.20%
49   0.326667   0.068606    7.30%   7.21%
50   0.333333   0.250983    7.36%   7.27%
51       0.34   1.245566    7.66%   7.56%
52   0.346667   -0.53959    7.53%   7.43%
53   0.353333   0.716565    7.71%   7.60%
54       0.36   0.177848    7.75%   7.64%
55   0.366667   0.464334    7.86%   7.75%
56   0.373333   -0.03673    7.85%   7.74%
57       0.38   1.484269    8.21%   8.10%
58   0.386667   0.256705    8.28%   8.16%
59   0.393333   -0.57977    8.13%   8.02%
60        0.4   -0.75267    7.95%   7.83%
61   0.406667   0.635688    8.10%   7.98%
62   0.413333   0.029676    8.11%   7.99%
63       0.42   1.041196    8.36%   8.24%
64   0.426667   -0.12311    8.33%   8.21%
65   0.433333   -3.07638    7.58%   7.45%
66       0.44   1.150188    7.86%   7.72%
67   0.446667     -1.6679   7.45%   7.32%
68   0.453333   -1.33265    7.12%   7.00%
69       0.46   -0.23787    7.06%   6.95%
70   0.466667   1.193776    7.35%   7.22%
71   0.473333   -0.45804    7.24%   7.11%
72       0.48   0.201099    7.29%   7.16%
73   0.486667   -0.94244    7.06%   6.94%
74   0.493333   -2.09267    6.55%   6.46%
75        0.5   -1.03013    6.29%   6.24%
76   0.506667   1.159547    6.58%   6.49%
77   0.513333   0.156674    6.61%   6.52%
78       0.52   -0.07082    6.60%   6.51%
79   0.526667   -1.50027    6.23%   6.17%
80   0.533333   0.328768    6.31%   6.24%
81       0.54   1.615693    6.71%   6.59%
82   0.546667   2.247516    7.26%   7.09%
83   0.553333   0.138294    7.29%   7.12%
84       0.56   1.298942    7.61%   7.42%
85   0.566667   0.072172    7.62%   7.44%
86   0.573333   -0.05565    7.61%   7.43%
87       0.58   -0.02314    7.60%   7.42%
88   0.586667   0.943349    7.83%   7.64%
89   0.593333   -2.01787    7.34%   7.16%
90        0.6   -0.01299    7.33%   7.16%
91   0.606667   -1.01794    7.08%   6.92%
92   0.613333   0.897519    7.30%   7.12%
93       0.62   -0.16571    7.26%   7.08%
94   0.626667   1.138484    7.54%   7.35%
95   0.633333   -1.66549    7.13%   6.95%
96       0.64   -0.77034    6.94%   6.78%
97   0.646667   -0.73266    6.76%   6.61%
98   0.653333   0.132016    6.79%   6.64%
99       0.66   1.349352    7.12%   6.94%
100   0.666667    0.52219    7.25%   7.06%
101   0.673333     -0.2776   7.18%   7.00%
102       0.68   -0.13926    7.15%   6.96%
103   0.686667   -0.94978    6.92%   6.75%
104   0.693333    0.63403    7.07%   6.89%
105        0.7   0.935377    7.30%   7.10%
106   0.706667   1.083159    7.56%   7.35%
107   0.713333   0.335566    7.65%   7.43%
108       0.72   -0.20069    7.60%   7.38%
109   0.726667    0.02854    7.60%   7.39%
110   0.733333    0.33509    7.68%   7.47%
111       0.74   -0.65172    7.52%   7.31%
112   0.746667   -0.11512    7.49%   7.28%
113   0.753333   0.234109    7.55%   7.34%
114       0.76   -0.34918    7.46%   7.25%
115   0.766667   0.234595    7.52%   7.31%
116   0.773333   1.015985    7.77%   7.55%
117       0.78    0.55507    7.90%   7.68%
118   0.786667   0.825703    8.11%   7.87%
119   0.793333   -0.54108    7.97%   7.74%
120        0.8   0.408163    8.07%   7.84%
121   0.806667   -0.28051    8.00%   7.77%
122   0.813333   -0.37667    7.91%   7.68%
123       0.82   -0.36594    7.82%   7.59%
124   0.826667   0.912394    8.04%   7.81%
125   0.833333   -1.30105    7.72%   7.49%
126       0.84   1.379076    8.06%   7.82%
127   0.846667   -1.68825    7.64%   7.41%
128   0.853333   0.140442    7.68%   7.44%
129       0.86   0.457016    7.79%   7.55%
130   0.866667   -0.38566    7.69%   7.46%
131   0.873333     -1.6274   7.29%   7.07%
132       0.88   0.115386    7.32%   7.10%
133   0.886667   -1.91502    6.85%   6.65%
134   0.893333   0.141963    6.89%   6.69%
135        0.9   1.203781    7.18%   6.95%
136   0.906667   0.438891    7.29%   7.05%
137   0.913333   -0.09122    7.26%   7.03%
138       0.92   2.850516    7.96%   7.69%
139   0.926667   0.169046    8.00%   7.73%
140   0.933333   0.247204    8.06%   7.79%
141       0.94     -0.6248   7.91%   7.63%
142   0.946667   1.199866    8.20%   7.92%
143   0.953333   -0.90116    7.98%   7.70%
144       0.96   1.938064    8.45%   8.16%
145   0.966667   -0.32619    8.37%   8.08%
146   0.973333   0.750049    8.55%   8.27%
147       0.98   0.003646    8.55%   8.27%
148   0.986667    0.32128    8.63%   8.35%
149   0.993333   -1.87632    8.17%   7.87%
150          1   0.575036    8.31%   8.01%
151   1.006667   -0.16932    8.27%   7.97%
152   1.013333   0.584567    8.41%   8.11%
153       1.02   -0.92601    8.18%   7.88%
154   1.026667   1.104617    8.45%   8.15%
155   1.033333   0.292747    8.52%   8.22%
156       1.04   -0.70626    8.35%   8.05%
157   1.046667   -1.12378    8.07%   7.77%
158   1.053333   -0.10258    8.05%   7.74%
159       1.06   -0.72124    7.87%   7.57%
160   1.066667   0.542707    8.00%   7.70%
161   1.073333   0.579998    8.14%   7.84%
162       1.08   -0.02731    8.13%   7.83%
163   1.086667   0.420208    8.24%   7.93%
164   1.093333   -0.62525    8.08%   7.78%
165        1.1   0.713275    8.26%   7.95%
166   1.106667   -0.16808    8.21%   7.91%
167   1.113333   -0.54739    8.08%   7.77%
168       1.12   1.953491    8.56%   8.24%
169   1.126667   1.799403    8.99%   8.69%
170   1.133333   -1.71858    8.57%   8.25%
171       1.14   0.507177    8.70%   8.37%
172   1.146667   -0.26754    8.63%   8.31%
173   1.153333   -1.11761    8.35%   8.03%
174       1.16   -1.16809    8.07%   7.74%
175   1.166667   0.992289    8.31%   7.98%
176   1.173333   0.571634    8.45%   8.12%
177       1.18   0.942827    8.68%   8.35%
178   1.186667   -1.77133    8.24%   7.90%
179   1.193333   1.537206    8.62%   8.28%
180        1.2   0.777394    8.81%   8.47%
181   1.206667     -0.2647   8.74%   8.40%
182   1.213333   -1.86077    8.28%   7.93%
183       1.22   0.657887    8.44%   8.09%
184   1.226667   1.654335    8.85%   8.50%
185   1.233333   1.197203    9.14%   8.80%
186       1.24     -0.5615   9.00%   8.65%
187   1.246667    0.98604    9.24%   8.90%
188   1.253333   0.379512    9.33%   9.00%
189       1.26    0.35015    9.42%   9.09%
190   1.266667   0.286754    9.49%   9.16%
191   1.273333   -0.05104    9.47%   9.15%
192       1.28     -0.7529   9.29%   8.95%
193   1.286667   -0.83994    9.08%   8.73%
194   1.293333   1.053953    9.33%   9.00%
195        1.3    0.38054    9.43%   9.10%
196   1.306667   -0.57234    9.28%   8.95%
197   1.313333   -0.64898    9.12%   8.78%
198       1.32   0.320222    9.20%   8.86%
199   1.326667   -0.27468    9.13%   8.79%
200   1.333333   -0.27798    9.06%   8.71%
201       1.34   1.984484    9.55%   9.22%
202   1.346667     -0.0582   9.53%   9.20%
203   1.353333   -0.14615    9.49%   9.16%
204       1.36   0.306816    9.57%   9.24%
205   1.366667   1.083587    9.83%   9.53%
206   1.373333   -1.72035    9.41%   9.06%
207       1.38   -0.41795    9.30%   8.95%
208   1.386667   -0.44408    9.19%   8.84%
209   1.393333   0.659257    9.35%   9.01%
210        1.4   0.685577    9.52%   9.18%
211   1.406667   -0.17833    9.47%   9.13%
212   1.413333   -0.47125    9.36%   9.01%
213       1.42   -0.82943     9.15%    8.79%
214   1.426667   1.013259     9.40%    9.05%
215   1.433333   -0.20356     9.35%    9.00%
216       1.44   -0.17452     9.30%    8.95%
217   1.446667   -1.60808     8.91%    8.53%
218   1.453333   1.202037     9.20%    8.83%
219       1.46   -0.17339     9.16%    8.79%
220   1.466667   -0.00309     9.16%    8.79%
221   1.473333   1.176483     9.44%    9.09%
222       1.48   1.245296     9.75%    9.41%
223   1.486667    1.51118    10.11%    9.81%
224   1.493333     -1.6593    9.71%    9.36%
225        1.5   -2.31617     9.14%    8.74%
226   1.506667   -1.11043     8.86%    8.46%
227   1.513333   0.335822     8.94%    8.54%
228       1.52    0.31104     9.02%    8.62%
229   1.526667   -0.36464     8.93%    8.53%
230   1.533333    0.40635     9.03%    8.63%
231       1.54   -0.46593     8.91%    8.51%
232   1.546667     -0.4215    8.81%    8.40%
233   1.553333   0.964539     9.04%    8.64%
234       1.56   1.208246     9.34%    8.95%
235   1.566667    1.05506     9.59%    9.22%
236   1.573333   -0.24591     9.53%    9.15%
237       1.58   0.412152     9.63%    9.26%
238   1.586667     -1.4526    9.27%    8.87%
239   1.593333   0.228651     9.33%    8.93%
240        1.6   0.085542     9.35%    8.95%
241   1.606667   0.663165     9.51%    9.12%
242   1.613333   -0.00775     9.50%    9.12%
243       1.62   2.038657    10.00%    9.65%
244   1.626667   -0.55985     9.86%    9.50%
245   1.633333   -1.47511     9.50%    9.10%
246       1.64   -0.20843     9.45%    9.05%
247   1.646667   1.277513     9.76%    9.38%
248   1.653333   1.966537    10.24%    9.90%
249       1.66   0.265131    10.30%    9.97%
250   1.666667   0.525818    10.43%   10.11%
251   1.673333   0.778229    10.62%   10.32%
252       1.68   -0.52208    10.49%   10.18%
253   1.686667   0.146726    10.52%   10.22%
254   1.693333   1.183353    10.81%   10.54%
255        1.7   0.610986    10.95%   10.71%
256   1.706667   -1.09401    10.68%   10.40%
257   1.713333   0.304151    10.76%   10.48%
258       1.72   -1.94688    10.28%    9.93%
259   1.726667   1.821376    10.72%   10.43%
260   1.733333   0.923884    10.95%   10.69%
261       1.74   0.084109    10.96%   10.71%
262   1.746667   -0.12382    10.93%   10.67%
263   1.753333     -1.5557   10.55%   10.23%
264       1.76   1.760472    10.98%   10.71%
265   1.766667   0.234328    11.03%   10.78%
266   1.773333   0.148524    11.07%   10.82%
267       1.78   0.011847    11.07%   10.82%
268   1.786667      -0.635   10.91%   10.63%
269   1.793333   -1.20522    10.61%   10.29%
270        1.8   -1.60041    10.22%    9.85%
271   1.806667   0.761699    10.40%   10.05%
272   1.813333   -0.44372    10.29%    9.93%
273       1.82   1.307507    10.61%   10.28%
274   1.826667   -1.33603    10.28%    9.91%
275   1.833333   -0.75982    10.09%    9.70%
276       1.84   0.828616    10.29%    9.92%
277   1.846667   -0.65094    10.13%    9.74%
278   1.853333   -0.32111    10.05%    9.65%
279       1.86   -0.43695     9.94%    9.53%
280   1.866667   -0.21963     9.89%    9.47%
281   1.873333   -0.58961     9.74%    9.32%
282       1.88   0.933798     9.97%    9.56%
283   1.886667   -0.19389     9.92%    9.51%
284   1.893333     -0.7499    9.73%    9.31%
285        1.9   1.301583    10.05%    9.65%
286   1.906667   -0.00652    10.05%    9.64%
287   1.913333   -0.10138    10.02%    9.62%
288       1.92   -0.70256     9.85%    9.42%
289   1.926667   0.293467     9.92%    9.50%
290   1.933333   0.126657     9.94%    9.53%
291       1.94   1.276052    10.26%    9.87%
292   1.946667   1.424002    10.60%   10.26%
293   1.953333   1.500905    10.97%   10.67%
294       1.96   -0.93049    10.74%   10.41%
295   1.966667     -0.9484   10.50%   10.14%
296   1.973333   -0.65045    10.34%    9.96%
297       1.98      1.4285   10.69%   10.35%
298   1.986667   -1.00449    10.44%   10.07%
299   1.993333     -0.4051   10.34%    9.95%
300          2   0.051834    10.35%    9.97%
Equilibrium line b
6.00%
6.00%

Rate at t=0
8.00%
8.00%
Term structure in Vasicek Model                                       Have a a look at the formulas
t                                                              0                                            Vasicek Term Structure of Inte
Rate r0 at t=0                                              8.0%         16
Maturity time (T)                                               2.0      20
9%
"Pullback" a                                                0.15         15                           8%
Equilibrium b                                               6.0%         60                           7%
Instanteanous StDev. of short rate (s                      2.0%         20                           6%
5%
Results:
B in Vasicek Model (Hull)                                    1.73                                     4%
A in Vasicek Model (Hull)                                0.984227                                     3%
Infinitely-long Rate (Y)                                  5.11%
2%
Vasicek Discount Factor                                  0.857161
Solution with VBA Function                               #NAME?                                       1%
Vasicek Zero Rate                                          7.706%
0%
Vasicek volatitility of zero rate sY(t,T)                  1.728%
0
Long-term distribution of r (Steady State Probability Density Function)
r                                                       5.00%
Vasicek Discount Function
P                                                     10.523         10.523
Mean of P                                                 6.00%                                      1.0
StDev of P                                                3.65%                                      0.9
0.8
0.7
0.6
Vasicek Model: Steady State Probability Density Function                                     0.5
for Spotrate r                                                          0.4
6.00%                                               0.3
0.2
2.35%                  9.65%
0.1
-
Mean
Spotrate (r)                0

-SD           +SD
-20%            -10%              0%                 10%              20%          30%

Formulas
with constants

dr  a b  r dt  s dz
b: long-term equilibrium of mean revertin
Interest rate process:                                                                                a: "pull-back" factor - speed of adjustme
s: spot rate volatility

P (t , T )  A(t , T )e  B ( t ,T ) r ( t )
Value of zero=coupon bond:                                                                            dz standard Wiener process

with

1  e  a (T  t )                                                         a 2b  s                            2

                                         2  
B (t , T )                                 A ( t , T )  exp   B ( t , T )  T  t                                             
a                                                                         a 2                                  

                                                                    
1  e  a (T  t )                                                    a 2b  s           2

                        2  
B (t , T )                            A ( t , T )  exp   B ( t , T )  T  t                            
a                                                                    a 2                 

                                                   
Long-term distribution of r (Steady State Probability Density Function)

     s 
a 1 
a (r b)2        Thus P is normally distributed with
P ~   b ,    
P                              s
     2a 
2
e
 s 2
Infinitely-long Rate (Y)                     CIR volatitility of zero rate sY(t,T)

Y  b  s
2

s                          1 eaTt     B(t,T)
sY t,T )  s              s
aT t        T t
2a 2       a
Back to Top

Source:
Hull, John C., Options, Futures & Other Derivatives. Fourth edition (2000). Prentice -Hall. p. 567.
Model:
Vasicek Term Structure of Interest                                                 Data Table
0.857161
0.001        0.99992
0.5 0.961149
Vasicek Zero Rate
1  0.92449
2 0.857161
Long-term
equilibrium rate       3    0.796909
4    0.742768
r at t=0               5    0.693903
6    0.649605
7 0.609269
Infinitely long rate
8   0.572385
9   0.538524
10    0.50732
15   0.381871
5         10        15          20        25         30   35
Time to maturity                                                              20   0.291715
25   0.224452
30   0.173295
Vasicek Discount Function

Vasicek Discount
Factor
-4.5          -10.43%
-4           -8.61%
-3.5           -6.78%
-3           -4.95%
-2.5           -3.13%
-2           -1.30%
-1.5            0.52%
-1            2.35%
-0.5            4.17%
0            6.00%
0.5            7.83%
5         10         15          20         25       30   35
Time to maturity                                                       1            9.65%
1.5           11.48%
2           13.30%
2.5           15.13%
3           16.95%
3.5           18.78%
4           20.61%
4.5           22.43%

with constants
b: long-term equilibrium of mean reverting spot rate process
a: "pull-back" factor - speed of adjustment
s: spot rate volatility
dz standard Wiener process

 a 2b  s          2
                               
                        2   s
2               2
)  T  t 
B (t, T )        
                               
      a 2                                 4a
                                                         

 a 2b  s        2
                               
                     2   s
2               2
)  T  t 
B (t, T )       
                               
      a 2                               4a
                                                      


     s 
P ~   b ,    
     2a 

T t                      where r0 spotrate at t=0
B(t,T)
s
a: "pull-back" factor - speed of adjustment

t          T t
s: spot rate volatility

Hall. p. 567.
Long-term equilibrium rate
8.000%               0     6.00%
7.925%               30    6.00%
7.851%
7.706%    r at t=0
7.567%               0     8.00%
7.434%
7.308%
7.190%    Infinitely long rate
7.079%               0     5.11%
6.974%               30    5.11%
6.877%
6.786%
6.418%
6.160%
5.976%
5.843%

10.52337
0.000438
0.003665   Mean
0.023899      6.00%           -
0.121371      6.00%        12.018
0.480032
1.478604   + StDev
3.546985      9.65%           -
6.626641      9.65%        12.018
9.641706
10.92548   - StDev
9.641706       2.35%          -
6.626641       2.35%       12.018
3.546985
1.478604
0.480032
0.121371
0.023899
0.003665
0.000438
Term structure CIR Model                                         Have a a look at the formulas
t (nowyear)                                                  0                                             CIR Term Structure of Int
Rate r0 at t=0                                            8.0%        16
Maturity time (T)                                          2.0        20
9%
"Pullback" a                                              0.15        15                         8%
Equilibrium b                                             6.0%        60                         7%
Instanteanous StDev. of short rate (s                    5.0%        50                         6%
Results:
5%
g in CIR Model (Hull)                                 0.16583
B in CIR Model (Hull)                                 1.7254                                     4%
A in CIR Model (Hull)                                 0.9838                                     3%
Infinitely-long Rate (Y)                                5.70%
2%
CIR Discount Factor                                 0.856974
Solution with VBA Function                          #NAME?                                       1%
CIR Zero Rate                                         7.717%
0%
CIR volatitility of zero rate sY(t,T)                 1.220%
0
Long-term distribution of r (Steady State Probability Density Function)
r                                                       6.00%
k = 2ab/s
2
7.20                                                CIR Discount Function
P                                                     17.636           17.636                   1.0
Mean of P                                               6.00%                                   0.9
StDev of P                                              2.24%                                   0.8
0.7
0.6
0.5
CIR Model: Steady State Probability Density Function for                        0.4
Spotrate r                                                0.3
0.2
6.00%
0.1
3.76%                  8.24%
-
0
Spotrate (r)
Mean

-SD            +SD
-5%               0%                  5%             10%           15%              20%

Formulas CIR Interest Rate Model
with constants

dr  a b  r dt  s r dz
b: long-term equilibrium of mean reve
Interest rate process:                                                                           a: "pull-back" factor - speed of adjust
s: spot rate volatility

P (t , T )  A(t , T )e  B ( t ,T ) r ( t )
Value of zero=coupon bond:                                                                       dz standard Wiener process

with

B(t,T) 
2 eg Tt  1                  
A (t , T ) 
                                       2g e  a 
g  a eg T t 
1  2g                                                                        g  a  e g T
                
B(t,T) 
2 eg Tt  1           
A (t , T ) 
                                                2g e  a 
g  a eg T t 
1  2g                                                                                   
 g  a  e g T

Long-term distribution of r (Steady State Probability Density Function) is gamma distributed
k
 2a                                                           G(.) is Gamma Function

 2                                                            Excel worksheetfunction is GAMMAL

 s  r k 1e 2 ar s 2   2a  r k 1e 2 ar s 2 ln G k  Mean & standard deviation gamma d
k

P                             2
Gk                     s                                  G Mean   k
s2
b
2a
2ab
with k  2                                                           G Stdev   k
s2

s                                                                                     2a
Gamma distribution in Excel notation

f x,a, b   a
1                                                                            with
xa1ex b                                                                a=k

b Ga                                                                          b = (s )/2a
2

Back to top

Infinitely-long Rate (Y)                   CIR volatitility of zero rate sY(t,T)

Y  2ab                                                                            B(t , T )
a  g                     s Y t ,T )   s r0
T  t 

Source:
CIR Term Structure of Interest                                                     Data Table
0.856974
0.001        0.99992
0.5 0.961145
CIR Zero Rate
1 0.924463
2 0.856974
Long-term equilibrium rate
3 0.796375
4  0.74169
5 0.692105
r at t=0
6 0.646938
7 0.605619
Infinitely long rate     8    0.567673
9      0.5327
10    0.500364
15    0.369636
5         10        15         20         25     30          35
Time to maturity                                                                 20     0.27579
25    0.206686
30      0.1552
CIR Discount Function

CIR Discount Factor
-4.5     0.00%
-4     0.00%
-3.5     0.00%
-3     0.00%
-2.5     0.41%
-2     1.53%
-1.5     2.65%
-1     3.76%
-0.5     4.88%
0     6.00%
0.5     7.12%
5         10         15         20         25    30           35
Time to maturity                                                                  1     8.24%
1.5     9.35%
2    10.47%
2.5    11.59%
3    12.71%
3.5    13.83%
4    14.94%
4.5    16.06%

with constants
b: long-term equilibrium of mean reverting spot rate process
a: "pull-back" factor - speed of adjustment
s: spot rate volatility
dz standard Wiener process

 a  g T  t                2 ab s   2

      2g e                                 2
A (t , T )  
                    
g  a  e g T  t   1  2g 
            
 a  g T  t          2 ab s   2

      2g e                                2
A (t , T )  
                   
g  a  e g T  t   1  2g 
          
G(.) is Gamma Function
Excel worksheetfunction is GAMMALN(.) which LN of G(.)

Mean & standard deviation gamma distribution

s   2
G Mean   k               b
2a
s2
G Stdev  
b
k                  s
2a           2a

where r0 spotrate at t=0
a: "pull-back" factor - speed of adjustment
s: spot rate volatility
Long-term equilibrium rate
8.000%               0      6.00%
7.926%               30     6.00%
7.854%
7.717%    r at t=0
7.590%               0      8.00%
7.471%
7.360%
7.258%    Infinitely long rate
7.164%               0      5.70%
7.078%               30     5.70%
6.998%
6.924%
6.635%
6.441%
6.306%
6.210%

17.63607
0
0   Mean
0      6.00%            -
0      6.00%         21.163
0.000857716
0.783173416   + StDev
6.163377694      8.24%            -
14.32772814      8.24%         21.163
18.78532093
17.63606626   - StDev
13.29931079       3.76%           -
8.589998899       3.76%        21.163
4.943965332
2.602638762
1.276100276
0.590449441
0.260350609
0.110222426
0.04506799

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 307 posted: 2/3/2011 language: English pages: 29
Description: Excel Vba Finance document sample