part D need help

(d) [10] Compute the WAL, modified duration, and convexity for the base-case MPT (last paragraph in part (a)). Also compute the static- and z-spread assume a treasury spot rate term structure given by z(T)=0.03ln[0.038(T+40)] where T is measured in months. [45pts total] Dollar Rolls. Should there be any missing input data/information use (and justify) any reasonable assumption. To show work, all derived quantities should contain cell formulae, and solver ought to be set up if you need it to numerically solve a non-linear equation. Pay attention to layout and clarity of presentation at the risk of losing points. Make sure there are no circular references, nor any links to external files. Answer each part in a single, separate tab (worksheet). (a) [20] Create a dollar roll matrix of breakeven rates for an agency MBS with gross and deal coupons of 7.785% and 7.25%, respectively, and settlement dates 6/14/23 and 8/15/23. Assume standard fully amortizing fixed rate mortgages with a term of 30:0, a WAM of 29:3 and an immediate price of 97-16. MBS CF's are due the owner of record on the first of each month, but are paid on the 256 of that month. Assume standard PSA benchmarks. The spreadsheet should correctly calculate the dollar advantage for any input above (though only for a twomonth roll - not 1 , or 3 or more). I do suggest you try to recreate the 1-month dollar roll in the lecture pdfs first, to get a feel for how it's done (and you can submit this "test run" on a separate workshect along with your 2 -month answer for partial eredit if warranted). Do not hardeode inputs in formulae, e.g., rates/coupons, dates, terms or WAMs, prices, reinvestment rates (though you can stick to act/360), balances, PSA convention, etc.) Calculate the breakeven matrix for PSAs of 100,150 , and 200 , and forward drops of 40,35, and -30 (32's) for the inputs listed in the first paragraph (these, or course, will be fixed numbers). Submit your dollar roll screen for a forward drop of 35/32, a PSA of 150 , and a reinvestment rate of 2% act 360 . Rubric: the dollar roll screen works if you change the inputs within reason. (b) [5] Discuss the risks of roll vs, hold from the perspective of the investor. Use textbox. (c) [10] Why does the dollar roll market exist? Explain this using all participants in both the primary and secondary markets, from the prospective mortgagor to the mortgage bank, to the dealer and investor in the secondary market. Pay attention to hedging motives of each participant, as well as their goals. Use textbox and tables/diagrams as needed. (d) [10] Compute the WAL, modified duration, and convexity for the base-case MPT (last paragraph in part (a)). Also compute the static- and z-spread assume a treasury spot rate term structure given by z(T)=0.03ln[0.038(T+40)] where T is measured in months