Get documents about "Excel Vba Finance - Excel
Description
Excel Vba Finance document sample
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 ab rDt 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 eaTt B(t,T)
sY t,T ) s s
aT 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 Tt 1
A (t , T )
2g e a
g a eg T t
1 2g g a e g T
B(t,T)
2 eg Tt 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
Gk 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
xa1ex b a=k
b Ga 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
Related docs
