Excel Vba Finance - Excel

Document Sample
Excel Vba Finance - Excel Powered By Docstoc
					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