Term Structure Modelings
lgdapqris
NSmodel.xlsxSheet1
| Cubic | Nelson Siegel | Cubic | NS | ||||||||||||||||
| r0 | 0.0123539777 | b1 | 0.0144406122 | 10% coupon bond | Maturity | Yields | CF | Dis CF | Yields | CF | Dis CF | ||||||||
| a | 0.0020873578 | b2 | 0.013836434 | 0.5 | 1.42% | 5 | 4.9646257273 | 1.42% | 5 | 4.9646257273 | |||||||||
| b | -0.0001102927 | b3 | 0.0105467307 | 1 | 1.60% | 5 | 4.9206366003 | 1.60% | 5 | 4.9206366003 | |||||||||
| c | 0.0000019286 | l | 0.0098931573 | 1.5 | 1.52% | 5 | 4.8869718855 | 2.83% | 5 | 4.7925342997 | |||||||||
| Target | 0.0000090169 | Target | 0.00000001 | 2 | 1.73% | 5 | 4.8299586784 | 1.73% | 5 | 4.8299586784 | |||||||||
| 2.5 | 1.69% | 5 | 4.7929925707 | 2.82% | 5 | 4.6592235951 | |||||||||||||
| Maturity | New maturity | Yield | Cubic Estimates | Squared Errors | Level | Slope | Curve | NS Model | Squared Error | 3 | 1.83% | 5 | 4.732899006 | 1.83% | 5 | 4.732899006 | |||
| 1 | 0.0833333333 | 0.0108 | 0.0125271593 | 0.0000029831 | 1 | 0.9995878984 | 0.0004119884 | 0.0282756893 | 0.0003053997 | 3.5 | 1.84% | 5 | 4.6882915839 | 2.82% | 5 | 4.5297763225 | |||
| 3 | 0.25 | 0.0129 | 0.0128689539 | 0.000000001 | 1 | 0.9987643742 | 0.0012346075 | 0.0282729706 | 0.0002363282 | 4 | 1.91% | 5 | 4.6329290433 | 1.91% | 5 | 4.6329290433 | |||
| 6 | 0.5 | 0.0142 | 0.0133703244 | 0.0000006884 | 1 | 0.9975307837 | 0.0024651482 | 0.0282688803 | 0.0001979334 | 4.5 | 1.97% | 5 | 4.5760473299 | 2.82% | 5 | 4.4040793017 | |||
| 1 | 1 | 0.016 | 0.0143329713 | 0.000002779 | 1 | 0.9950696935 | 0.0049140745 | 0.0282606557 | 0.0001503237 | 5 | 2.06% | 105 | 94.7233322156 | 2.06% | 105 | 94.7233322156 | |||
| 1.5 | 1.5 | 0.0152433647 | 1 | 0.9926166992 | 0.0073468689 | 0.028252373 | 137.7486846409 | 137.1899947899 | |||||||||||
| 2 | 2 | 0.0173 | 0.0161029511 | 0.0000014329 | 1 | 0.9901717709 | 0.0097636211 | 0.0282440328 | 0.0001197719 | ||||||||||
| 2.5 | 2.5 | 0.0169131769 | 1 | 0.9877348788 | 0.0121644201 | 0.0282356355 | |||||||||||||
| 3 | 3 | 0.0183 | 0.0176754886 | 0.00000039 | 1 | 0.985305993 | 0.0145493545 | 0.0282271816 | 0.0000985489 | ||||||||||
| 3.5 | 3.5 | 0.0183913326 | 1 | 0.9828850841 | 0.0169185127 | 0.0282186717 | |||||||||||||
| 4 | 4 | 0.0190621554 | 1 | 0.9804721224 | 0.0192719822 | 0.0282101064 | |||||||||||||
| 4.5 | 4.5 | 0.0196894034 | 1 | 0.9780670787 | 0.0216098503 | 0.028201486 | |||||||||||||
| 5 | 5 | 0.0206 | 0.0202745231 | 0.0000001059 | 1 | 0.9756699237 | 0.0239322039 | 0.0281928112 | 0.0000576508 | ||||||||||
| 5.5 | 5.5 | 0.0208189609 | 1 | 0.9732806283 | 0.0262391292 | 0.0281840824 | |||||||||||||
| 6 | 6 | 0.0213241632 | 1 | 0.9708991633 | 0.0285307122 | 0.0281753001 | |||||||||||||
| 6.5 | 6.5 | 0.0217915765 | 1 | 0.9685255 | 0.0308070382 | 0.0281664648 | |||||||||||||
| 7 | 7 | 0.0223 | 0.0222226473 | 0.000000006 | 1 | 0.9661596094 | 0.0330681923 | 0.0281575771 | 0.0000343112 | ||||||||||
| 7.5 | 7.5 | 0.022618822 | 1 | 0.9638014629 | 0.035314259 | 0.0281486375 | |||||||||||||
| 8 | 8 | 0.0229815471 | 1 | 0.961451032 | 0.0375453224 | 0.0281396463 | |||||||||||||
| 8.5 | 8.5 | 0.0233122689 | 1 | 0.9591082882 | 0.0397614661 | 0.0281306041 | |||||||||||||
| 9 | 9 | 0.023612434 | 1 | 0.956773203 | 0.0419627732 | 0.0281215115 | |||||||||||||
| 9.5 | 9.5 | 0.0238834888 | 1 | 0.9544457484 | 0.0441493267 | 0.0281123688 | |||||||||||||
| 10 | 10 | 0.0235 | 0.0241268797 | 0.000000393 | 1 | 0.9521258961 | 0.0463212087 | 0.0281031766 | 0.0000211892 | ||||||||||
| 10.5 | 10.5 | 0.0243440532 | 1 | 0.9498136182 | 0.0484785013 | 0.0280939353 | |||||||||||||
| 11 | 11 | 0.0245364557 | 1 | 0.9475088867 | 0.0506212858 | 0.0280846454 | |||||||||||||
| 11.5 | 11.5 | 0.0247055337 | 1 | 0.9452116739 | 0.0527496433 | 0.0280753074 | |||||||||||||
| 12 | 12 | 0.0248527336 | 1 | 0.942921952 | 0.0548636543 | 0.0280659217 | |||||||||||||
| 12.5 | 12.5 | 0.0249795019 | 1 | 0.9406396935 | 0.0569633992 | 0.0280564888 | |||||||||||||
| 13 | 13 | 0.025087285 | 1 | 0.938364871 | 0.0590489575 | 0.0280470092 | |||||||||||||
| 13.5 | 13.5 | 0.0251775293 | 1 | 0.9360974569 | 0.0611204087 | 0.0280374833 | |||||||||||||
| 14 | 14 | 0.0252516814 | 1 | 0.9338374242 | 0.0631778316 | 0.0280279116 | |||||||||||||
| 14.5 | 14.5 | 0.0253111876 | 1 | 0.9315847456 | 0.0652213048 | 0.0280182945 | |||||||||||||
| 15 | 15 | 0.0253574945 | 1 | 0.9293393942 | 0.0672509064 | 0.0280086325 | |||||||||||||
| 15.5 | 15.5 | 0.0253920484 | 1 | 0.9271013429 | 0.0692667139 | 0.0279989261 | |||||||||||||
| 16 | 16 | 0.0254162958 | 1 | 0.924870565 | 0.0712688047 | 0.0279891756 | |||||||||||||
| 16.5 | 16.5 | 0.0254316831 | 1 | 0.9226470337 | 0.0732572557 | 0.0279793815 | |||||||||||||
| 17 | 17 | 0.0254396568 | 1 | 0.9204307225 | 0.0752321431 | 0.0279695442 | |||||||||||||
| 17.5 | 17.5 | 0.0254416634 | 1 | 0.9182216047 | 0.0771935432 | 0.0279596643 | |||||||||||||
| 18 | 18 | 0.0254391493 | 1 | 0.9160196541 | 0.0791415316 | 0.0279497421 | |||||||||||||
| 18.5 | 18.5 | 0.0254335609 | 1 | 0.9138248442 | 0.0810761834 | 0.027939778 | |||||||||||||
| 19 | 19 | 0.0254263446 | 1 | 0.911637149 | 0.0829975736 | 0.0279297724 | |||||||||||||
| 19.5 | 19.5 | 0.025418947 | 1 | 0.9094565422 | 0.0849057765 | 0.0279197259 | |||||||||||||
| 20 | 20 | 0.0259 | 0.0254128145 | 0.0000002373 | 1 | 0.9072829979 | 0.0868008662 | 0.0279096388 | 0.0000040386 | ||||||||||
| 20.5 | 20.5 | 0.0254093935 | 1 | 0.9051164903 | 0.0886829164 | 0.0278995116 | |||||||||||||
| 21 | 21 | 0.0254101305 | 1 | 0.9029569934 | 0.0905520004 | 0.0278893446 | |||||||||||||
| 21.5 | 21.5 | 0.0254164719 | 1 | 0.9008044817 | 0.092408191 | 0.0278791382 | |||||||||||||
| 22 | 22 | 0.0254298641 | 1 | 0.8986589295 | 0.0942515608 | 0.0278688929 | |||||||||||||
| 22.5 | 22.5 | 0.0254517537 | 1 | 0.8965203114 | 0.0960821818 | 0.0278586092 | |||||||||||||
| 23 | 23 | 0.025483587 | 1 | 0.8943886019 | 0.0979001258 | 0.0278482873 | |||||||||||||
| 23.5 | 23.5 | 0.0255268106 | 1 | 0.8922637757 | 0.0997054641 | 0.0278379277 | |||||||||||||
| 24 | 24 | 0.0255828707 | 1 | 0.8901458078 | 0.1014982677 | 0.0278275308 | |||||||||||||
| 24.5 | 24.5 | 0.025653214 | 1 | 0.8880346729 | 0.1032786072 | 0.0278170969 | |||||||||||||
| 25 | 25 | 0.0257392869 | 1 | 0.8859303461 | 0.1050465529 | 0.0278066266 | |||||||||||||
| 25.5 | 25.5 | 0.0258425357 | 1 | 0.8838328025 | 0.1068021745 | 0.0277961202 | |||||||||||||
| 26 | 26 | 0.0259644069 | 1 | 0.8817420173 | 0.1085455417 | 0.027785578 | |||||||||||||
| 26.5 | 26.5 | 0.0261063471 | 1 | 0.8796579658 | 0.1102767234 | 0.0277750004 | |||||||||||||
| 27 | 27 | 0.0262698026 | 1 | 0.8775806233 | 0.1119957884 | 0.0277643879 | |||||||||||||
| 27.5 | 27.5 | 0.0264562198 | 1 | 0.8755099655 | 0.1137028051 | 0.0277537409 | |||||||||||||
| 28 | 28 | 0.0266670453 | 1 | 0.8734459677 | 0.1153978416 | 0.0277430596 | |||||||||||||
| 28.5 | 28.5 | 0.0269037255 | 1 | 0.8713886059 | 0.1170809655 | 0.0277323445 | |||||||||||||
| 29 | 29 | 0.0271677068 | 1 | 0.8693378556 | 0.1187522441 | 0.0277215959 | |||||||||||||
| 29.5 | 29.5 | 0.0274604356 | 1 | 0.8672936929 | 0.1204117443 | 0.0277108143 | |||||||||||||
| 30 | 30 | 0.0278 | 0.0277833585 | 0.0000000003 | 1 | 0.8652560936 | 0.1220595328 | 0.0277 | 0.00000001 | ||||||||||
| 0.0000090169 | 0.0012255057 |
Cubic Spline
Original 8.3333333333333329E-2 0.25 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 7 7.5 8 8.5 9 9.5 10 10.5 11 11.5 12 12.5 13 13.5 14 14.5 15 15.5 16 16.5 17 17.5 18 18.5 19 19.5 20 20.5 21 21.5 22 22.5 23 23.5 24 24.5 25 25.5 26 26.5 27 27.5 28 28.5 29 29.5 30 1.0800000000000001E-2 1.29E-2 1.4200000000000001E-2 1.6E-2 1.7299999999999999E-2 1.83E-2 2.06E-2 2.23E-2 2.35E-2 2.5899999999999999E-2 2.7799999999999998E-2 Cubic Estimates 8.3333333333333329E-2 0.25 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 7 7.5 8 8.5 9 9.5 10 10.5 11 11.5 12 12.5 13 13.5 14 14.5 15 15.5 16 16.5 17 17.5 18 18.5 19 19.5 20 20.5 21 21.5 22 22.5 23 23.5 24 24.5 25 25.5 26 26.5 27 27.5 28 28.5 29 29.5 30 1.252715934152877E-2 1.2868953947553786E-2 1.3370324444757189E-2 1.4332971311450513E-2 1.5243364713718611E-2 1.6102951098592777E-2 1.6913176913104302E-2 1.7675488604284494E-2 1.8391332619164633E-2 1.9062155404776026E-2 1.9689403408149973E-2 2.027452307631776E-2 2.0818960856310683E-2 2.1324163195160048E-2 2.1791576539897148E-2 2.2222647337553268E-2 2.2618822035159716E-2 2.2981547079747786E-2 2.3312268918348769E-2 2.3612433997993964E-2 2.3883488765714674E-2 2.4126879668542184E-2 2.4344053153507791E-2 2.4536455667642801E-2 2.47055336579785E-2 2.4852733571546184E-2 2.4979501855377159E-2 2.5087284956502715E-2 2.5177529321954144E-2 2.525168139876275E-2 2.5311187633959828E-2 2.5357494474576667E-2 2.5392048367644561E-2 2.5416295760194822E-2 2.5431683099258733E-2 2.5439656831867594E-2 2.5441663405052703E-2 2.543914926584535E-2 2.5433560861276829E-2 2.5426344638378451E-2 2.5418947044181496E-2 2.5412814525717274E-2 2.5409393530017067E-2 2.5410130504112182E-2 2.5416471895033918E-2 2.5429864149813547E-2 2.5451753715482396E-2 2.5483587039071737E-2 2.5526810567612881E-2 2.5582870748137119E-2 2.5653214027675741E-2 2.5739286853260063E-2 2.5842535671921359E-2 2.5964406930690943E-2 2.6106347076600089E-2 2.6269802556680114E-2 2.6456219817962298E-2 2.666704530747796E-2 2.6903725472258361E-2 2.7167706759334838E-2 2.746043561573866E-2 2.7783358488501116E-2
Maturity
Yields
Nelson Siegel Model
Original 8.3333333333333329E-2 0.25 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 7 7.5 8 8.5 9 9.5 10 10.5 11 11.5 12 12.5 13 13.5 14 14.5 15 15.5 16 16.5 17 17.5 18 18.5 19 19.5 20 20.5 21 21.5 22 22.5 23 23.5 24 24.5 25 25.5 26 26.5 27 27.5 28 28.5 29 29.5 30 1.0800000000000001E-2 1.29E-2 1.4200000000000001E-2 1.6E-2 1.7299999999999999E-2 1.83E-2 2.06E-2 2.23E-2 2.35E-2 2.5899999999999999E-2 2.7799999999999998E-2 NS Estimates 8.3333333333333329E-2 0.25 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 7 7.5 8 8.5 9 9.5 10 10.5 11 11.5 12 12.5 13 13.5 14 14.5 15 15.5 16 16.5 17 17.5 18 18.5 19 19.5 20 20.5 21 21.5 22 22.5 23 23.5 24 24.5 25 25.5 26 26.5 27 27.5 28 28.5 29 29.5 30 2.8275689259399581E-2 2.8272970564191653E-2 2.8268880252391489E-2 2.8260655705926382E-2 2.8252373040001767E-2 2.8244032784916707E-2 2.8235635467633417E-2 2.8227181611794407E-2 2.8218671737742573E-2 2.8210106362539306E-2 2.8201485999983077E-2 2.8192811160627902E-2 2.8184082351801726E-2 2.8175300077624491E-2 2.8166464839026562E-2 2.8157577133766539E-2 2.814863745644932E-2 2.8139646298543861E-2 2.8130604148401062E-2 2.8121511491271248E-2 2.8112368809321917E-2 2.8103176581655103E-2 2.80939352843248E-2 2.8084645390354202E-2 2.8075307369752858E-2 2.8065921689533892E-2 2.805648881373092E-2 2.8047009203414946E-2 2.8037483316711263E-2 2.8027911608816077E-2 2.8018294532013238E-2 2.8008632535690747E-2 2.7998926066357246E-2 2.7989175567658332E-2 2.7979381480392879E-2 2.7969544242529301E-2 2.7959664289221517E-2 2.7949742052825053E-2 2.7939777962913023E-2 2.7929772446291877E-2 2.791972592701724E-2 2.7909638826409535E-2 2.7899511563069627E-2 2.78893445528943E-2 2.7879138209091704E-2 2.7868892942196667E-2 2.7858609160085954E-2 2.7848287267993502E-2 2.7837927668525442E-2 2.7827530761675129E-2 2.7817096944838117E-2 2.7806626612826957E-2 2.7796120157885986E-2 2.7785577969706005E-2 2.7775000435438929E-2 2.7764387939712282E-2 2.7753740864643644E-2 2.7743059589855051E-2 2.7732344492487269E-2 2.7721595947214052E-2 2.7710814326256231E-2 2.7699999999395791E-2
Maturity
Yields
Sheet2
| a | 68.8117728651 | SUMMARY OUTPUT | ||||||||||||
| b | 18.6360920192 | |||||||||||||
| Regression Statistics | ||||||||||||||
| Y | X | hat Y | sqared Deviation | Multiple R | 0.8203649473 | |||||||||
| 100 | 1.5 | 96.7659108939 | 10.4593323465 | <=e(1) | R Square | 0.6729986468 | ||||||||
| 105 | 2 | 106.0839569034 | 1.1749625685 | <=e(2) | Adjusted R Square | 0.5912483085 | ||||||||
| 101 | 1.8 | 102.3567384996 | 1.8407393563 | Standard Error | 5.6849479983 | |||||||||
| 99 | 1.2 | 91.1750832881 | 61.2293215482 | Observations | 6 | |||||||||
| 80 | 0.99 | 87.2615039641 | 52.7294398203 | |||||||||||
| 101 | 1.8 | 102.3567384996 | 1.8407393563 | ANOVA | ||||||||||
| 129.2745349961 | df | SS | MS | F | Significance F | |||||||||
| Regression | 1 | 266.0587983611 | 266.0587983611 | 8.2323652812 | 0.0455048287 | |||||||||
| Residual | 4 | 129.2745349723 | 32.3186337431 | |||||||||||
| Total | 5 | 395.3333333333 | ||||||||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |||||||
| Intercept | 68.8120526488 | 10.3209723349 | 6.6672063848 | 0.0026296349 | 40.1564395286 | 97.4676657689 | 40.1564395286 | 97.4676657689 | ||||||
| X | 18.6359186337 | 6.4951395941 | 2.8692098705 | 0.0455048287 | 0.6025201003 | 36.6693171672 | 0.6025201003 | 36.6693171672 |
