Q04. Generating an Interest Rate Lattice (8 points) Use the par curve provided in the Excel file to create a five-year binary benchmark interest rate lattice. Use the Exp() function in Excel to model a lognormal distribution with a volatility of 17%. Assume equal probability (50%) of rates moving up and rates moving down. Format the lattice to value callable bonds under an OAS assumption (so, at least four cells per node: cash flow, value, OAS, and forward rate). [Hint: You received values for certain nodes in an Announcement.] Part A. Generate the Base Case lattice. Part B. Shift the curve +/- 25 basis points (bps) and create two more lattices, a Shift Up lattice and a Shift Down lattice. A Finance 8370 0 Fixed-Income Securities Analysis Bootstrapping Settlement Date: 22-Jan-2024 Yield Curve Shift Shift (bps) = 25.000 D E FL G H Yield Curve Data Period Maturity Coupon (% of Par) Par Value (% of Par) Price Par Rate (% of Par) (% BEY) 1 22-Jan-2025 3.000 100.000 99.500 3.518 3.518 2 22-Jan-2026 3.000 100.000 98.500 3.793 3.793 3 22-Jan-2027 3.050 100.000 97.250 4.042 4.042 4 22-Jan-2028 3.100 100.000 95.750 4.279 4.279 5 22-Jan-2029 3.150 100.000 94.000 4.517 4.517 6 22-Jan-2030 3.700 100.000 94.500 4.776 4.776 7 22-Jan-2031 3.900 100.000 93.500 5.024 5.024 8 22-Jan-2032 4.100 100.000 92.500 5.273 5.273 9 22-Jan-2033 4.250 100.000 91.500 5.471 5.471 10 22-Jan-2034 4.350 100.000 90.500 5.617 5.617 Pmt Freq (per Yr): 1.00 58 8 8 8 8 8 85 % % % + 3 + 4 + + + 4 5 Bootstrapping 28 29 3.000 Stripping the Par Yield Curve - Base Case Period Maturity Par Rate Numerator Spot Rate Denominator (Yrs) (% BEY) (Z BEY) 2.000 30 1 0 1.0 3.518 103.518 100.000 3.518 31 2 2.0 3.793 103.793 96.336 3.798 32 3 3.0 4.042 104.042 92.344 4.056 Par Ra (% BEY 33 4 4.0 4.279 104.279 88.098 4.305 5 5.0 4.517 104.517 83.617 4.563 35 6 6.0 4.776 104.776 78.859 4.850 36 7 7.0 5.024 105.024 73.978 5.134 8 8.0 5.273 105.273 68.974 5.428 38 9 9.0 5.471 105.471 64.226 5.666 39 10 10.0 5.617 105.617 59.848 5.845 Spot Rate Discount Discount Fvd Rate Sanity Period (% BEY) Factor Factor (% BEY) Check Cash Flow Fair Value 42 1 3.518 0.96602 0.96602 3.518 1.035 6.125 5.9168689 44 45 47 NM567c 2 3.798 0.92816 0.92816 4.080 1.041 6.125 5.6542573 3 4.056 0.88757 0.88757 4.573 1.046 6.125 5.3561257 4 4.305 0.84484 0.84484 5.058 1.051 6.125 5.0279008 4.563 0.80003 0.80003 5.600 1.056 106.125 80.81503 4.850 0.75265 0.75265 6.296 1.063 102.77018 5.134 0.70439 0.70439 6.851 1.069 49 8 5.428 0.65519 0.65519 7.509 1.075 apping Stripping the Par Yield Curve - Shift Up Period Maturity Up Par Rate Numerator (Yrs) (Z BEY) Denominator Up Spot Rate (* BEY) 1 1.0 3.768 103.768 100.000 3.768 2 2.0 4.043 104.043 96.104 4.049 3 3.0 4.292 104.292 91.900 4.307 4 4.0 4.529 104.529 87.462 4.557 5 5.0 4.767 104.767 82.812 4.816 6 6.0 5.026 105.026 77.908 5.104 7 7.0 5.274 105.274 72.903 5.389 8 8.0 5.523 105.523 67.799 5.686 9 9.0 5.721 105.721 62.970 5.926 10 10.0 5.867 105.867 58.528 6.106 Period Up Spot Rate Discount Discount Up Fvd Rate Sanity (% BEY) Factor Factor (Z BEY) Check Cash Flow Fair Value 1 3.768 0.96369 0.96369 3.768 1.038 6.125 5.9026138 2 4.049 0.92369 0.92369 4.330 1.043 6.125 5.6271173 3 4.307 0.88118 0.88118 4.825 1.048 6.125 5.3176001 4 4.557 0.83673 0.83673 5.312 1.053 6.125 4.9796032 5 4.816 0.79044 0.79044 5.857 1.059 106.125 79.839851 6 5.104 0.74180 0.74180 6.557 1.066 101.6667855 5 Bootstrapping 32 Stripping the Par Yield Curve-Shift Down Maturity Dn Par Rate Period Numerator Denominator Dn Spot Rate 33 (Yrs) (Z BEY) (% BEY) 34 1 1.0 3.268 103.268 100.000 3.268 35 2 2.0 3.543 103.543 96.569 3.548 36 3 3.0 3.792 103.792 92.792 3.805 87 4 4.0 4.029 104.029 88.740 4.054 38 5 5.0 4.267 104.267 84.432 4.311 39 6 6.0 4.526 104.526 79.825 4.596 80 7 7.0 4.774 104.774 75.071 4.878 91 8 8.0 5.023 105.023 70.172 5.170 92 9 9.0 5.221 105.221 65.509 5.406 93 10 10.0 5.367 105.367 61.200 5.583 94 On Spot Rate Discount Discount Dn Fvd Rate Sanity Period 95 (Z BEY) Factor Factor (% BEY) Check Cash Flow Fair Value 96 1 3.268 0.96836 0.96836 3.268 1.033 6.125 5.9311931 97 98 99 00 01 02 03 NM 56700 2 3.548 0.93265 0.93265 3.829 1.038 6.125 5.6815942 3 3.805 0.89402 0.89402 4.321 1.043 6.125 5.3950239 4 4.054 0.85304 0.85304 4.804 1.048 6.125 5.076783 4.311 0.80977 0.80977 5.344 1.053 106.125 81.804407 4.596 0.76368 0.76368 6.034 1.060 103.889 4.878 0.71650 0.71650 6.585 1.066 8 5.170 0.66815 0.66815 7.236 1.072