Spreadsheet: 239159d1369835985-question-about-addin-and-a-special-function-52vasicek-model-v3
Sheet: ex1,2

[NAME] |  |  | © copyright THC 2003. All rights reserved by [NAME], NY, NY. |  |  |  |  |  | www.thomasho.com |  | [EMAIL]
reference number | 16 | classification# | 50302 |  | Instructions
Version | 1 | level | basic |  | Use "data-table" to generate the discount function and the term structure.
publication date | 2003-05-19 00:00:00
author | [NAME].
affiliation | Hanyang University |  |  |  | input | current short term interest rate, long-term mean, mean reverting speed, the volatility of short rate, maturity date, current date
email address | [EMAIL]
last revised date | 2003-12-07 00:00:00 |  |  |  | output | discount function and term structure
references | [NAME], O., 1977, An Equilibrium Characterization of the Term Structure, Journal of Financial Economics, [NAME], 177-188.
 | Ch.5 of "The Oxford Guide to Financial Modeling" by [NAME] and [NAME], 2004, Oxford University Press
Descriptions
financial model class | Interest Rate Derivatives: Interest Rate Models
issuer/[NAME]
model type | Equilibrium Models
risk sources | interest rate
risk distribution | normal
economic assumptions | equilibrium
technical assumptions | continuous compounding
key words
Links
data
financial models
Inputs
the level of short rate (r(t)) | 0.04
the long-term mean (b) | 0.04011156290266146
the mean reverting speed (a) | 0.1132428445104057
the volatility of short rate (σ) | 0.002037813678417425 | changing cells for solver
bond maturity (T) | 1
current date (t) | 0
option expiration (OT) | 0.6
bond face value (F) | 100
exercise price (K) | 92
Outputs
European bond option value |  | 6.253684729792972
 |  | Monte-carlo simulation
Interim Calculations
time to maturity |  | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30
P(t,T) | 0.9607842041242853 | 1 | 0.9607842041242853 | 0.9230990151649241 | 0.8868882173927692 | 0.8520965672229399 | 0.8186700935262898 | 0.7865562998491157 | 0.7557042935694163 | 0.726064860955111 | 0.6975905024643888 | 0.6702354391051247 | 0.6439555979904076 | 0.618708583191825 | 0.5944536364486517 | 0.5711515911225964 | 0.5487648219048089 | 0.527257192116082 | 0.5065939999404032 | 0.4867419245563884 | 0.4676689728503474 | 0.4493444271856649 | 0.4317387945482707 | 0.4148237572738745 | 0.3985721254792029 | 0.3829577912590413 | 0.3679556846676161 | 0.3535417314723764 | 0.3396928126472213 | 0.3263867255581888 | 0.3136021467856849 | 0.3013185965220997
r(t,T) | 0.0400054486884291 | 0.04 | 0.0400054486884291 | 0.04000938743229697 | 0.04001210928221868 | 0.04001385419113918 | 0.0400148184977052 | 0.04001516271251325 | 0.04001501791319762 | 0.04001449099867251 | 0.04001366900740502 | 0.04001262266747847 | 0.04001140931587421 | 0.04001007529960038 | 0.04000865795101617 | 0.04000718721310208 | 0.04000568697684245 | 0.04000417618176041 | 0.04000266972152829 | 0.04000117918910507 | 0.03999971348972351 | 0.03999827934502185 | 0.03999688170748773 | 0.03999552410099271 | 0.03999420890041037 | 0.03999293756102266 | 0.03999171080653594 | 0.03999052878297951 | 0.03998939118448451 | 0.03998829735589095 | 0.03998724637626545 | 0.03998623712669967
f(t,T) |  |  | 0.0400054486884291 | 0.04001332617616488 | 0.04001755298206206 | 0.04001908891790076 | 0.04001867572396926 | 0.04001688378655341 | 0.04001414911730382 | 0.04001080259699673 | 0.04000709307726495 | 0.04000320560813964 | 0.03999927579983144 | 0.0399954011205883 | 0.03999164976800583 | 0.03998806762021876 | 0.03998468366920759 | 0.03998151425552986 | 0.03997856635781436 | 0.03997584013791017 | 0.03997333090085557 | 0.03997103059569009 | 0.03996892895680558 | 0.03996701436459724 | 0.0399652744875989 | 0.03996369675510523 | 0.03996226869885474 | 0.03996097819406863 | 0.03995981362361466 | 0.03995876398386464 | 0.03995781894675134 | 0.03995696888929205
observed discount function: P*(t,T) |  |  | 0.9607894391523232 | 0.9231163463866358 | 0.8869204367171575 | 0.8521437889662113 | 0.8187307530779818 | 0.7866278610665535 | 0.7557837414557255 | 0.7261490370736909 | 0.697676326071031 | 0.6703200460356393 | 0.6440364210831414 | 0.6187833918061408 | 0.5945205479701944 | 0.5712090638488149 | 0.5488116360940264 | 0.5272924240430485 | 0.5066169923655895 | 0.4867522559599717 | 0.4676664270099092 | 0.4493289641172216 | 0.4317105234290797 | 0.4147829116815814 | 0.3985190410845141 | 0.3828928859751121 | 0.3678794411714423 | 0.3534546819587802 | 0.3395955256449391 | 0.3262797946230395 | 0.3134861808826053 | 0.3011942119122021
Σ(P(t,T)-P*(t,[NAME] | 1.372483183283351e-07 |  | 2.740551855699342e-11 | 3.003712460186578e-10 | 1.03808486403454e-09 | 2.229893037592899e-09 | 3.679581211473005e-09 | 5.121007841178339e-09 | 6.31196663900004e-09 | 7.085618939179916e-09 | 7.365691457078737e-09 | 7.158332691101827e-09 | 6.532372319061003e-09 | 5.596328775858754e-09 | 4.477151715159306e-09 | 3.303114258984025e-09 | 2.191568312090128e-09 | 1.241288677774762e-09 | 5.286516159489842e-10 | 1.067379000000418e-10 | 6.481303536692672e-12 | 2.391064856821778e-10 | 7.992561803125512e-10 | 1.668362409779912e-09 | 2.817952959472337e-09 | 4.212695881932334e-09 | 5.813070708795807e-09 | 7.577617817339677e-09 | 9.4647608130521e-09 | 1.143422489190304e-08 | 1.344809067705587e-08 | 1.547153117938057e-08
 | target cells for solver
B(t,T) | 0.9454567372242384
A(t,T) | 0.9978152109340981
P(t,T) | 0.9607842041242853
r(t,T) | 0.0400054486884291
B(t,OT) | 0.5800702132786834
A(t,OT) | 0.9992010465539123
P(t,OT) | 0.9762836778061892
σP | 0.0005969169374144629
h | 112.8773795698024
European call option | 6.26032205425912 | closed-form
the number of periods (n) | 5
dt=T/n | 0.2
time to maturity |  | 0 | 0.2 | 0.4 | 0.6000000000000001 | 0.8 | 1
Generate interest rate paths | 1 | 0.04 | 0.03971528337227555 | 0.03850655405113947 | 0.03652650619419331 | 0.03603726184364048 | 100
 | 2 | 0.04 | 0.04099101001805464 | 0.04181514664712178 | 0.04306164535009892 | 0.04213130830215429 | 100
 | 3 | 0.04 | 0.03862578706945108 | 0.03947130096941159 | 0.03968488005585903 | 0.0392441976863664 | 100
 | 4 | 0.04 | 0.04104171487339582 | 0.04115161910870942 | 0.04107724855592813 | 0.04038944312992892 | 100
 | 5 | 0.04 | 0.0416017292074504 | 0.04170816333961816 | 0.04250600681114052 | 0.04187823609984185 | 100
 | 6 | 0.04 | 0.03893453981116302 | 0.0379745119519303 | 0.03805746782941607 | 0.0367385739735377 | 100
 | 7 | 0.04 | 0.03961314663215383 | 0.04091298949042441 | 0.04142038496549945 | 0.04219411005505925 | 100
 | 8 | 0.04 | 0.03835551203236498 | 0.03946493827484451 | 0.0394936873302714 | 0.03834535221017893 | 100
 | 9 | 0.04 | 0.03979560130911665 | 0.03858568904100301 | 0.0386881887090187 | 0.03883664801513063 | 100
 | 10 | 0.04 | 0.04174334950373617 | 0.04300891599253624 | 0.04399769707512338 | 0.04326320163508048 | 100
 | 11 | 0.04 | 0.04042547118170223 | 0.04143443144362119 | 0.04027094225058141 | 0.03997795236525298 | 100
 | 12 | 0.04 | 0.03888711440278496 | 0.03983361564405176 | 0.03885781120665538 | 0.03814337294055983 | 100
 | 13 | 0.04 | 0.03925307749397631 | 0.03892861379764239 | 0.04115651912306435 | 0.04084695105769077 | 100
 | 14 | 0.04 | 0.04122630708671491 | 0.04166927287278955 | 0.04063700757438023 | 0.04090457291666988 | 100
 | 15 | 0.04 | 0.03840937323145509 | 0.03807390018401267 | 0.0376794505331586 | 0.03625631449693903 | 100
 | 16 | 0.04 | 0.04113746103876734 | 0.04281668598630088 | 0.04326852518913355 | 0.04396697791585234 | 100
 | paths | r(t) |  |  |  |  | F
calculate bond option value |  | PV(Pay-offs) |  |  | Pay-offs
 |  | 6.405935745521017 |  |  | 6.559204879801527
 |  | 6.157466984472472 |  |  | 6.310574513404291
 |  | 6.283630421465846 |  |  | 6.433812740061327
 |  | 6.229745731045427 |  |  | 6.383868012030419
 |  | 6.172361533355533 |  |  | 6.326476762919526
 |  | 6.36464217574067 |  |  | 6.515212475368415
 |  | 6.190576891694824 |  |  | 6.341615247783508
 |  | 6.304939938893955 |  |  | 6.455274402626145
 |  | 6.31027517659824 |  |  | 6.461461581926429
 |  | 6.115420795640731 |  |  | 6.269922747203807
 |  | 6.253549398881717 |  |  | 6.407833247631828
 |  | 6.319917282766325 |  |  | 6.471774041548827
 |  | 6.224431245627144 |  |  | 6.373306510040521
 |  | 6.22743304797075 |  |  | 6.382394453000614
 |  | 6.381745362930904 |  |  | 6.5321640034403
 |  | 6.116883944081994 |  |  | 6.270421873303945