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

[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.0367
the long-term mean (b) | 0.01048255736200978
the mean reverting speed (a) | 0.1747185279677665
the volatility of short rate (σ) | 0.02444670601947044
maturity date (T) | 1
current date (t) | 0
Outputs
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.9661367734007699 | 1 | 0.9661367734007699 | 0.9374419284786423 | 0.9131884335667797 | 0.8927152582742994 | 0.8754403019533216 | 0.8608607025073528 | 0.8485471778309048 | 0.8381359860943903 | 0.8293203675917786 | 0.8218423789298035 | 0.8154855153650138 | 0.8100682465506791 | 0.8054384553789714 | 0.8014687073401368 | 0.7980522544417502 | 0.7950996742045496 | 0.7925360501953843 | 0.7902986104223126 | 0.7883347508275895 | 0.7866003816622161 | 0.7850585440903799 | 0.7836782527458389 | 0.7824335271438859 | 0.7813025809354956 | 0.7802671431040034 | 0.7793118894844088 | 0.7784239665581948 | 0.7775925924554108 | 0.7768087225782845 | 0.776064769329593
r(t,T) | 0.03444986742076751 | 0.0367 | 0.03444986742076751 | 0.03230023300611511 | 0.03027101007790437 | 0.02837190216685903 | 0.02660566339118842 | 0.02497042889147852 | 0.02346137062898229 | 0.02207186463618799 | 0.02079430531623674 | 0.01962066554396982 | 0.01854287443094083 | 0.01755306498867229 | 0.01664372959790061 | 0.01580781073675922 | 0.01503874679463905 | 0.01433048723930249 | 0.01367748735645716 | 0.01307468983689698 | 0.01251749834925602 | 0.01200174668897962 | 0.01152366597721338 | 0.01107985158051599 | 0.01066723084839631 | 0.01028303235828654 | 0.009924757071049199 | 0.009590151601093642 | 0.009277183669247792 | 0.008984019715878254 | 0.008709004593388813 | 0.008450643221702048
f(t,T) |  |  | 0.03444986742076751 | 0.03015059859146273 | 0.02621256422148292 | 0.02267457843372301 | 0.01954070828850605 | 0.01679425639292905 | 0.01440702105400495 | 0.01234532268662785 | 0.01057383075662679 | 0.009057907593567496 | 0.00776496330065087 | 0.006665161123718304 | 0.005731704908640561 | 0.004940865541921106 | 0.004271851604956617 | 0.003706593909254081 | 0.003229489230931864 | 0.00282713200437391 | 0.002488051571718899 | 0.002202465143728141 | 0.00196205174188845 | 0.001759749249870826 | 0.001589574741763289 | 0.001446467085761857 | 0.001326150177353141 | 0.001225014852204699 | 0.001140017441255751 | 0.001068592974900708 | 0.001008581163684512 | 0.0009581634427858142
Interim Calculations
B(t,T) | 0.9175138101553582
A(t,T) | 0.999223191284166
P(t,T) | 0.9661367734007699
r(t,T) | 0.03444986742076751
the long-term mean (b) | 0.01048255736200978
the mean reverting speed (a) | 0.1747185279677665
volatility | 0.001288454538501254
Monthly Treasury Yield
Date | 1mo (%) | 1mo | r(t+1)-r(t) | a{b-r(t)} | r(t+1)-r(t)-a{b-r(t)} |  | pdf | ln(pdf)
2001-07-01 00:00:00 | 3.67 | 0.0367
2001-08-01 00:00:00 | 3.53 | 0.0353 | -0.001399999999999998 | -0.004580672984789007 | 0.003180672984789009 |  | 14.70800025320925 | 2.688391580654775
2001-09-01 00:00:00 | 2.68 | 0.0268 | -0.008499999999999997 | -0.004336067045634134 | -0.004163932954365863 |  | 1.670870076230743 | 0.5133444944843268
2001-10-01 00:00:00 | 2.27 | 0.0227 | -0.004099999999999999 | -0.00285095955790812 | -0.001249040442091879 |  | 193.5422061731786 | 5.265495608443209
2001-11-01 00:00:00 | 1.99 | 0.0199 | -0.0028 | -0.002134613593240277 | -0.0006653864067597231 |  | 270.9752211433473 | 5.602027381803879
2001-12-01 00:00:00 | 1.72 | 0.0172 | -0.002700000000000001 | -0.001645401714930531 | -0.00105459828506947 |  | 221.4957202146716 | 5.400403267468596
2002-01-01 00:00:00 | 1.68 | 0.0168 | -0.000400000000000001 | -0.001173661689417561 | 0.0007736616894175599 |  | 258.5525358885789 | 5.555098906910855
2002-02-01 00:00:00 | 1.74 | 0.0174 | 0.0005999999999999998 | -0.001103774278230454 | 0.001703774278230454 |  | 129.1639405532922 | 4.861082454508703
2002-03-01 00:00:00 | 1.79 | 0.0179 | 0.0005000000000000004 | -0.001208605395011114 | 0.001708605395011115 |  | 128.5242053644503 | 4.856117255191083
2002-04-01 00:00:00 | 1.72 | 0.0172 | -0.0006999999999999992 | -0.001295964658994997 | 0.0005959646589949982 |  | 278.2167850340736 | 5.628400612083651
2002-05-01 00:00:00 | 1.74 | 0.0174 | 0.0001999999999999988 | -0.001173661689417561 | 0.00137366168941756 |  | 175.3975869015599 | 5.167055322155645
2002-06-01 00:00:00 | 1.72 | 0.0172 | -0.0001999999999999988 | -0.001208605395011114 | 0.001008605395011115 |  | 227.9174323148754 | 5.428983424396444
2002-07-01 00:00:00 | 1.72 | 0.0172 | 0 | -0.001173661689417561 | 0.001173661689417561 |  | 204.4856697900756 | 5.320497898651374
2002-08-01 00:00:00 | 1.68 | 0.0168 | -0.000400000000000001 | -0.001173661689417561 | 0.0007736616894175599 |  | 258.5525358885789 | 5.555098906910855
2002-09-01 00:00:00 | 1.67 | 0.0167 | -9.99999999999994e-05 | -0.001103774278230454 | 0.001003774278230455 |  | 228.5857805339263 | 5.431911547037288
2002-10-01 00:00:00 | 1.62 | 0.0162 | -0.000499999999999997 | -0.001086302425433678 | 0.0005863024254336806 |  | 279.175645864 | 5.631841138802807
2002-11-01 00:00:00 | 1.26 | 0.0126 | -0.003600000000000003 | -0.0009989431614497947 | -0.002601056838550208 |  | 40.35496182957881 | 3.697714356909919
2002-12-01 00:00:00 | 1.2 | 0.012 | -0.0005999999999999998 | -0.0003699564607658348 | -0.0002300435392341651 |  | 304.7325829669052 | 5.719434614855813
2003-01-01 00:00:00 | 1.17 | 0.0117 | -0.0003000000000000017 | -0.0002651253439851749 | -3.487465601482678e-05 |  | 309.5151266314363 | 5.735006965345315
2003-02-01 00:00:00 | 1.2 | 0.012 | 0.0003000000000000017 | -0.0002127097855948446 | 0.0005127097855948463 |  | 286.0597364137459 | 5.656200657589379
2003-03-01 00:00:00 | 1.18 | 0.0118 | -0.0002000000000000005 | -0.0002651253439851749 | 6.512534398517435e-05 |  | 309.2332551672208 | 5.734095863217195
2003-04-01 00:00:00 | 1.16 | 0.0116 | -0.0002000000000000005 | -0.0002301816383916215 | 3.018163839162095e-05 |  | 309.5435892700761 | 5.735098919914176
2003-05-01 00:00:00 | 1.08 | 0.0108 | -0.0007999999999999986 | -0.0001952379327980681 | -0.0006047620672019306 |  | 277.3330473275733 | 5.625219120881797
2003-06-01 00:00:00 | 0.97 | 0.0097 | -0.0011 | -5.546311042385507e-05 | -0.001044536889576145 |  | 222.9091615655861 | 5.40676434123217
2003-07-01 00:00:00 | 0.9 | 0.009000000000000001 | -0.0006999999999999992 | 0.0001367272703406882 | -0.0008367272703406874 |  | 250.7635922158256 | 5.524510631617509
2003-08-01 00:00:00 | 0.95 | 0.0095 | 0.0004999999999999987 | 0.0002590302399181246 | 0.0002409697600818741 |  | 304.2606098036741 | 5.717884603251978
2003-09-01 00:00:00 | 0.91 | 0.0091 | -0.0003999999999999993 | 0.0001716709759342416 | -0.0005716709759342409 |  | 280.6038945220399 | 5.636944046838045
2003-10-01 00:00:00 | 0.91 | 0.0091 | 0 | 0.0002415583871213481 | -0.0002415583871213481 |  | 304.2345829409809 | 5.71779905824515
2003-11-01 00:00:00 | 0.94 | 0.009399999999999999 | 0.0002999999999999982 | 0.0002415583871213481 | 5.84416128786501e-05 |  | 309.3101847422176 | 5.734344607510415
2003-12-01 00:00:00 | 0.89 | 0.0089 | -0.0004999999999999987 | 0.0001891428287310184 | -0.0006891428287310172 |  | 268.3616882762247 | 5.592335653762336
2004-01-01 00:00:00 | 0.85 | 0.0085 | -0.0003999999999999993 | 0.0002765020927149015 | -0.0006765020927149008 |  | 269.7606053523676 | 5.597534918851482
2004-02-01 00:00:00 | 0.92 | 0.0092 | 0.0006999999999999992 | 0.000346389503902008 | 0.0003536104960979913 |  | 298.1847089087051 | 5.697713123043728
2004-03-01 00:00:00 | 0.96 | 0.0096 | 0.0003999999999999993 | 0.0002240865343245715 | 0.0001759134656754278 |  | 306.7561020591173 | 5.726052976006424
2004-04-01 00:00:00 | 0.91 | 0.0091 | -0.0004999999999999987 | 0.000154199123137465 | -0.0006541991231374637 |  | 272.182727502353 | 5.606473633032631
 |  |  |  | average | 3.834906766005269e-08 |  |  | 172.766877891609
 |  |  |  | variance | 1.712039374811947e-06
 |  |  |  | std | 0.001308449225156233
Date |  | 1mo | 3mo | 6mo | 1yr | 2yr | 3yr | 5yr | 7yr | 10yr | 20yr
time to maturity |  | 0.08333333333333333 | 0.25 | 0.5 | 1 | 2 | 3 | 5 | 7 | 10 | 20
Vasicek yield curve | 0.03444986742076751 | 0.03650937735754722 | 0.03612963680311813 | 0.03556412835048842 | 0.03444986742076751 | 0.03230023300611511 | 0.03027101007790437 | 0.02660566339118842 | 0.02346137062898229 | 0.01962066554396982 | 0.01200174668897962
observed yield curve (2001/07/01) |  | 0.0367 | 0.0359 | 0.0356 | 0.0362 | 0.0404 | 0.0431 | 0.0476 | 0.0506 | 0.0524 | 0.0575
 |  | 3.67 | 3.59 | 3.56 | 3.62 | 4.04 | 4.31 | 4.76 | 5.06 | 5.24 | 5.75
time to maturity |  | 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
spot rate volatility structure | 0.02243019038567226 | 0.02243019038567226 | 0.02063233590792698 | 0.01902662013060119 | 0.01758994221837266 | 0.01630216391016464 | 0.01514571375215491 | 0.01410524571142952 | 0.01316734454239885 | 0.01232027136502755 | 0.01155374384404508 | 0.01085874615445379 | 0.01022736460033278 | 0.009652645337781327 | 0.00912847115305173 | 0.008649454675604958 | 0.008210845773329312 | 0.007808451192327269 | 0.007438564774045101 | 0.007097906814541769 | 0.006783571329877484 | 0.006492980162658545 | 0.006223843011730347 | 0.00597412259330899 | 0.005742004250429898 | 0.005525869420990284 | 0.005324272455028992 | 0.005135920341069829 | 0.004959654960934848 | 0.004794437543767859 | 0.004639335034256968
σR(t,s) = [σ/{a(s-t)}](1-e-a(s-t))