Please see attached pdf and excel file. The last few questions i need the most guidance with.

PROF. DAVID SCHREINDORFER FIN 421 - SPRING 2016 Assignment 7 Due: Wednesday, 04.20.16 at 11:59 pm In this assignment you will estimate the yield curve based on over-the-counter (OTC) price quotes for Treasury bonds. Estimating the Yield Curve In Lecture 12, we saw how to create and price synthetic zero coupon bonds based on the prices of traded coupon bonds. In particular, we formed replicating portfolios whose cash ows were identical to those of a zero. We then argued that the prices of such replicating portfolios must equal the fair value of the corresponding zeros by the law of one price. In this assignment, your task is to extract zero prices from a set of actual price quotes for T-bonds, and to convert them into an estimate of the yield curve. However, rather than nding the solution algebraically (which is painful), you will nd it \"brute force\" by using Solver. On Blackboard, you will nd a spreadsheet that shows how to do this based on the example we studied in class. Another spreadsheet contains OTC price quotes for T-bond with maturities between about 6 months and 10 years that were downloaded on 4/8/2015 from http://online.wsj.com/mdc/public/page/2_3020-treasury.html I have also set up some tables go guide your analysis. Steps: 1. Compute \"dirty prices\Today 4/8/2015 Days to next coupon 127 Maturity Coupon 08/15/15 02/15/16 08/15/16 02/15/17 08/15/17 02/15/18 08/15/18 02/15/19 08/15/19 02/15/20 08/15/20 02/15/21 08/15/21 02/15/22 08/15/22 02/15/23 08/15/23 02/15/24 08/15/24 02/15/25 08/15/25 0.2500 0.3750 0.6250 0.6250 4.7500 1.0000 4.0000 2.7500 3.6250 3.6250 2.6250 3.6250 2.1250 2.0000 1.6250 2.0000 2.5000 2.7500 2.3750 2.0000 6.8750 Bid 100.0625 100.1250 100.3281 100.1953 109.5625 100.5000 110.1641 106.0469 110.0469 110.7969 106.1328 111.7656 103.1172 102.2969 99.4453 101.9063 105.5313 107.4688 104.2813 100.9297 146.8047 Asked Price Fitted Price Error Cash Flows 08/15/15 02/15/16 08/15/16 02/15/17 08/15/17 02/15/18 08/15/18 02/15/19 08/15/19 02/15/20 08/15/20 02/15/21 08/15/21 02/15/22 08/15/22 02/15/23 08/15/23 02/15/24 08/15/24 02/15/25 08/15/25 100.0781 100.1406 100.3438 100.2109 109.5781 100.5156 110.1797 106.0625 110.0625 110.8125 106.1484 111.7813 103.1328 102.3125 99.4609 101.9219 105.5469 107.4844 104.2969 100.9453 146.8203 SSE = 0.0000 Yield Curve TTM (in years) discount factor spot rate 0.353 0.853 1.353 1.853 2.353 2.853 3.353 3.853 4.353 0.500 0.340 1.000 0.540 2.000 0.700 3.000 0.840 5.000 1.160 7.000 1.470 10.000 1.720 Yield Curve reported by the Treasury TTM (in years) spot rate 0.083 0.200 0.250 0.230 4.853 5.353 5.853 6.353 6.853 7.353 7.853 8.353 8.853 9.353 9.853 10.353 Today Days to next coupon Maturity 4/8/2015 127 Coupon 08/15/15 02/15/16 08/15/16 02/15/17 08/15/17 02/15/18 08/15/18 02/15/19 08/15/19 02/15/20 08/15/20 02/15/21 08/15/21 02/15/22 08/15/22 02/15/23 08/15/23 02/15/24 08/15/24 02/15/25 08/15/25 0.2500 0.3750 0.6250 0.6250 4.7500 1.0000 4.0000 2.7500 3.6250 3.6250 2.6250 3.6250 2.1250 2.0000 1.6250 2.0000 2.5000 2.7500 2.3750 2.0000 6.8750 Bid 100.0625 100.1250 100.3281 100.1953 109.5625 100.5000 110.1641 106.0469 110.0469 110.7969 106.1328 111.7656 103.1172 102.2969 99.4453 101.9063 105.5313 107.4688 104.2813 100.9297 146.8047 Asked 100.0781 100.1406 100.3438 100.2109 109.5781 100.5156 110.1797 106.0625 110.0625 110.8125 106.1484 111.7813 103.1328 102.3125 99.4609 101.9219 105.5469 107.4844 104.2969 100.9453 146.8203 Price Fitted Price 100.0781 100.1406 100.3438 100.2109 109.5781 100.5156 110.1797 106.0625 110.0625 110.8125 106.1484 111.7813 103.1328 102.3125 99.4609 101.9219 105.5469 107.4844 104.2969 100.9453 146.8203 Error Cash Flows 08/15/15 02/15/16 08/15/16 02/15/17 08/15/17 02/15/18 08/15/18 02/15/19 08/15/19 02/15/20 08/15/20 02/15/21 08/15/21 02/15/22 08/15/22 02/15/23 08/15/23 02/15/24 08/15/24 02/15/25 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 0.25 0.375 0.625 0.625 4.75 1 4 2.75 3.625 3.625 2.625 3.625 2.125 2 1.625 2 2.5 2.75 2.375 2 100.0781 100.1406 100.3438 100.2109 109.5781 100.5156 110.1797 106.0625 110.0625 110.8125 106.1484 111.7813 103.1328 102.3125 99.4609 101.9219 105.5469 107.4844 104.2969 100.9453 146.8203 SSE = ### Yield Curve TTM (in years) discount factor spot rate 0.353 0.853 1.353 1.853 2.353 2.853 3.353 3.853 4.353 0.500 0.340 1.000 0.540 2.000 0.700 3.000 0.840 5.000 1.160 7.000 1.470 10.000 1.720 Yield Curve reported by the Treasury TTM (in years) spot rate 0.083 0.200 0.250 0.230 4.853 5.353 5.853 6.353 6.853 7.353 7.853 8.353 8.853 9.353 9.853 08/15/25 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 6.875 10.353