NelsonSiegel w Svensson

IntroductionIMPORTANT INSTALLATION INFORMATIONProgrammed by and copyright Kurt Hess March 2004, kurthess@waikato.ac.nzIllustration of Extended Nelson & Siegel Spot Rate ModelFitting Extended Nelson & Siegel Spot Rate with Solver

&LKurt Hess, kurthess@waikato.ac.nz&R&F &A &DThe SOLVER macros in this workbook will only run if your Excel is set up as follows.

You must have SOLVER installed with your Excel.Go to Tools Menu and see whether item Solver appears there.If it does not, go to Tools - Add-ins and tick "Solver Add-in".

This 1st step will allow you to use SOLVER from Excel but because SOLVER is also called by a VBA macro, you will also need to establish a reference to the Solver add-in in the VBA editor:With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder.Kurt Hess:Nelson, C. R. & Siegel, A. F. (1987). Parsimonious modeling of yield curves, Journal of Business 60(4): 473489.Kurt Hess:Nelson, C. R. & Siegel, A. F. (1987). Parsimonious modeling of yield curves, Journal of Business 60(4): 473489.

as discussed in Bliss, R. R. (1997). Testing Term Structure Estimation Methods. Advances in Futures and Options Research(9), 197-231.Illustration of Extended Nelson & Siegel Spot Rate ModelFitting Extended Nelson & Siegel Spot Rate with Solver

Nelson Siegel with SvenssonIllustration of Extended Nelson & Siegel Spot Rate Model with Svensson 1994 ExtensionData Table for ChartSupport Line for Chartprogrammed by Kurt Hess May 2004, kurthess@waikato.ac.nz8.965%5.200%0.670%2.116%0.979%3.30Time to maturitym3.333.000.17.794%5.200%2.098%0.362%0.00133766063.38.965%Long-run levels of interest ratesb05.2%520.28.137%5.200%2.003%0.680%0.00253667073.310.758%Short-run componentb12.2%1220.48.688%5.200%1.829%1.202%0.0045661759Medium-term componentb28.1%81determines magnitude and the direction of the hump0.69.089%5.200%1.676%1.596%0.0061738468Decay parameter 1t11.050105determines decay of short-term component, must be > 00.89.371%5.200%1.540%1.888%0.0074314201Long-run componentDecay parameter 2t21.240124determines decay of medium-term component, must be > 019.557%5.200%1.419%2.098%0.00839915110.15.200%Svensson 1994 Extensionb30.035351.29.668%5.200%1.311%2.244%0.0091276034105.200%Spot rate at time trt,i8.9650%1.49.719%5.200%1.215%2.339%0.00965916268.9650%with VBA Function1.69.725%5.200%1.129%2.393%0.0100293182Components of N&S spot rate1.89.694%5.200%1.052%2.415%0.0102677468Comp 15.200%b029.637%5.200%0.983%2.414%0.0103992286Comp 20.670%b1*((1-EXP(-m/tau1))/(m/tau1))2.29.559%5.200%0.921%2.394%0.0104444226Comp 32.116%b2*((1-EXP(-m/tau1))/(m/tau1)-EXP(-m/tau1))2.49.466%5.200%0.865%2.360%0.0104205204Svensson (1994) ExtensionComp 40.979%b3*((1-EXP(-m/tau2))/(m/tau2)-EXP(-m/tau2))2.69.363%5.200%0.814%2.315%0.01034179982.89.253%5.200%0.768%2.264%0.010220091239.139%5.200%0.726%2.207%0.01006517193.29.023%5.200%0.688%2.147%0.00988509823.48.907%5.200%0.653%2.085%0.00968648593.68.791%5.200%0.621%2.023%0.0094747463.88.678%5.200%0.592%1.961%0.00925428448.567%5.200%0.565%1.900%0.00902866684.28.460%5.200%0.540%1.840%0.00880076374.48.355%5.200%0.517%1.781%0.00857286444.68.255%5.200%0.496%1.724%0.00834677834.88.158%5.200%0.476%1.670%0.008123917958.066%5.200%0.458%1.617%0.00790536845.27.977%5.200%0.441%1.567%0.00769194625.47.892%5.200%0.425%1.518%0.00748424735.67.811%5.200%0.411%1.472%0.00728268885.87.734%5.200%0.397%1.428%0.007087541967.660%5.200%0.384%1.386%0.00689896096.27.589%5.200%0.372%1.346%0.00671700626.47.522%5.200%0.360%1.308%0.0065416646.67.458%5.200%0.349%1.271%0.00637286196.87.397%5.200%0.339%1.236%0.006210482977.338%5.200%0.330%1.203%0.00605437547.27.282%5.200%0.320%1.171%0.00590436277.47.229%5.200%0.312%1.141%0.00576025027.67.178%5.200%0.304%1.112%0.00562183087.87.130%5.200%0.296%1.085%0.005488890287.083%5.200%0.289%1.059%0.00536121048.27.039%5.200%0.282%1.033%0.00523857288.46.996%5.200%0.275%1.009%0.00512076058.66.956%5.200%0.269%0.986%0.00500756038.86.917%5.200%0.262%0.964%0.004898763896.879%5.200%0.257%0.943%0.00479416879.26.843%5.200%0.251%0.923%0.00469357939.46.809%5.200%0.246%0.904%0.00459680749.66.776%5.200%0.241%0.885%0.00450367229.86.744%5.200%0.236%0.867%0.0044140006106.714%5.200%0.231%0.850%0.0043276274

&LKurt Hess, Waikato Management School&CPage &P&R&F &A &DNelson, C. R. & Siegel, A. F. (1987). Parsimonious modeling of yield curves, Journal of Business 60(4): 473489.

Nelson Siegel with Svensson

Fitting Bond Universe

TotalComp 2Comp 1c3Comp 4

Fitting Nelson & Siegel / Svensson Spot Rate with Solverprogrammed by Kurt Hess May 2004, kurthess@waikato.ac.nzData Table for ChartSupport Line for ChartModel Load AreasSolver OptionsInitial ValuesRandom ValuesTime to maturitym4.9496.664%Discount Factors4.9005.6027E+042.3347E+046056.028200572971.791961528500.000014.500%0.999999554.96.664%0.7214057794weightednon-weighted85118.31706693228.2068354749Long-run levels of interest ratesb07.31%73.1214427960.14.692%0.99531912470.00468042534.97.997%0.865686935315097.2735792629172.0353217571Short-run componentb1-2.81%71.8785572040.45.175%0.9795108920.015808232825055.9843173389271.434230482Medium-term componentb219.30%193.0421908236determines magnitude and the direction of the hump0.65.433%0.96792818980.01158270217027.97310770873.6301420599Decay parameter 1t11.585158.5459237735determines decay of short-term component, must be > 00.85.648%0.95582442380.0121037660.10.1841374280.5Decay parameter 2t21.773177.2825409044determines decay of medium-term component, must be > 015.826%0.94340120340.0124232204Svensson 1994 Extensionb3-0.1762450812-176.2450812374optional parameter proposed by Svensson (1994)1.25.975%0.93081234640.012588857Spot rate at time trt,i6.6644%1.46.098%0.91817328720.01263905921.66.199%0.90556896720.01260432Objective Functionssee formulas1.86.283%0.89306036030.012508607Non-weighted objective function x1030.233526.352%0.88068980570.0123705545Inverse duration weighted function x 1050.0560272.26.409%0.86848532520.01220448052.46.456%0.85646408520.01202124Initial Guess Values:2.66.494%0.84463515150.01182893372.86.526%0.83300166250.011633488936.552%0.82156252990.0114391327Bond Data3.26.573%0.81031375560.0112487743Short-term rate4.50%3.46.591%0.7992494430.0110643125Settlement date14-Feb-993.66.605%0.78836256240.0108868806IssuerCouponMaturityBidAskMid CleanMid DirtyModel PriceDurationWeights (wi)(cheap) / rich3.86.618%0.77764552140.010717041NZ Government6.50%15-Feb-00101.563100.583101.07%104.30%103.802%0.95645511580.36130162680.50%46.629%0.76709058290.0105549385NZ Government8.00%15-Feb-01102.786102.854102.82%106.80%106.888%1.82132159280.189735185(0.09%)4.26.638%0.75669016220.0104004207NZ Government10.00%15-Mar-02108.406108.526108.47%112.60%113.403%2.64752622540.1305251619(0.80%)4.46.646%0.74643703150.0102531307NZ Government5.50%15-Apr-0396.67396.82796.75%98.57%97.429%3.70689908710.09322314451.14%4.66.654%0.73632445180.0101125796NZ Government8.00%15-Apr-04105.034105.234105.13%107.78%108.011%4.25364840120.0812405626(0.23%)4.86.661%0.72634625140.0099782004NZ Government8.00%15-Nov-06106.518106.809106.66%108.64%108.918%5.88420767390.0587281769(0.28%)56.668%0.7164968620.0098493894NZ Government7.00%15-Jul-09100.549100.903100.73%101.29%101.196%7.53770781070.04584534160.09%5.26.674%0.7067713260.0097255361NZ Government6.00%15-Nov-1191.66692.04991.86%93.34%93.321%8.77060324970.03940080050.02%5.46.680%0.69716528110.00960604495.66.686%0.68767493080.0094903503Total15.86.693%0.67829700440.009377926466.699%0.66902871120.00926829326.26.705%0.65986769210.00916101916.46.711%0.65081197010.0090557226.66.718%0.64185990180.00895206836.86.725%0.63301013120.008849770676.731%0.6242615460.00874858527.26.738%0.61561323760.00864830857.46.745%0.60706446390.0085487736Formula Objective Functionback to topExtended Nelson Siegel Model with Svensson (1994) beta3 extension (parameters explained on top)7.66.752%0.59861461720.00844984677.86.759%0.59026319410.0083514232D: Duration86.766%0.58200976980.0082534242Pi: Price of bond i8.26.773%0.57385397620.0081557936^Pi: Model price of bond i8.46.780%0.56579548210.0080584941N: number of bonds in universe8.66.787%0.55783397680.00796150538.86.794%0.54996915670.007864820196.801%0.54220071340.00776844339.26.808%0.5345283250.00767238849.46.815%0.52695164820.0075766768Subject to:9.66.822%0.5194703130.00748133529.86.829%0.51208391820.0073863948Rate r at time 0 must remain positive (mmin is a value just slightly larger than 0)106.836%0.50479202840.0072918898Rate at the end of the estimation horizon must remain positiveDiscount functions must be non-increasing

Kurt Hess:Basic Nelson & Siegel set tau1 = tau2&LKurt Hess, Waikato Management School&CPage &P&R&F &A &D

N&S Svensson Zero Rate

N&S Svensson Discount Factors

MinimizeMinimizeDefault ValuesSet Random ValuesBefore using the minimization macros, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder.References:Nelson, C. R. & Siegel, A. F. (1987). Parsimonious modeling of yield curves, Journal of Business 60(4): 473489.as discussed in Bliss, R. R. (1997). Testing Term Structure Estimation Methods. Advances in Futures and Options Research(9), 197-231.Svensson, L. (1994). Estimating and interpreting forward interest rates: Sweden 1992-4. Discussion paper, Centre for Economic Policy Research(1051).Anderson, N., Breedon, F., Deacon, M., Derry, A., & Murphy, G. (1996). Estimating and interpreting the yield curve. Chichester: John Wiley Series in Financial Economics and Quantitative Analysis. Chapter 2.4.6, pgs. 36-41.Kurt Hess:Basic Nelson & Siegel set tau1 = tau2Kurt Hess:Set so beta1 + beta2 = shortrateObserved PricesModel PricesTime to MaturityBond PriceGoodnes of Fit365713657136937369373733037330377263772638092380923903639036400094000940862408621.04304644140.99764566311.06051594140.9775314521.11697844140.9955304510.99981794140.81672222911.08365994140.89145318861.09895294140.87056468511.03958294140.79931358940.95089844140.7409955012

MBD033E3F38.unknown

MBD0002BE81.unknown

MBD0002C361.unknown

MBD033E35FA.unknown