I need solution on excel

Dollar Rolls. Should there be any missing input data/information use (and justify) any reasonable assumption, Test your analysis by reproducing the pdf of the Bloomberg screen (last slide of LN4, also posted with this HW), which, however is for a one month roll only. Also, read BoA whitepaper of Rolls. NO derived quantity should be hardcoded into a formula - I should be able to get the correct dollar advantage if I change inputs (however, I will not change the settlement dates) Create a dollar roll matrix of breakeven rates for an agency MBS with gross and deal coupons of 8.035% and 7.5%, respectively, and settlement dates 6/14/18 and 8/15/18. Assume standard fully amortizing fixed rate mortgages with a term of 30:0, a WAM of 29:5 and an immediate price of 96-16. MBS CF's are due the owner of record on the first of each month, but are paid on the 25th of that month. Calculate a breakeven rate matrix for PSAs of 100, 150, and 200, and forward drops of 40,-35, and-30 (32's) NOTE: See LN5 on prepayment. For this HW, the following information will tell you how to get the dollar prepayment amount (aka Unscheduled Principal or USP) each month, from: the age of the pool since origination (t, in months), the outstanding principal at the time of payment (8), the scheduled principal due at the time of payment (SP), and the inputted prepayment speed (PSA). First, compute the annualized conditional prepayment rate (CPR) from t and the PSA CPR (PSA/100) 0.06 min(1, t/30) SMM-1-(1-CPR) (1/12) USP (B-SP) SMM Next, convert this into a monthly prepayment rate (SMM): Finally, the unscheduled principal expected that month is The connection between the age of the pool, t, and the remaining term (inputted) should be carefully checked against the Bloomberg pdf (is there a +1 or not?). This simply concerns the definition of "Rem Term on the Bloomberg screen. Dollar Rolls. Should there be any missing input data/information use (and justify) any reasonable assumption, Test your analysis by reproducing the pdf of the Bloomberg screen (last slide of LN4, also posted with this HW), which, however is for a one month roll only. Also, read BoA whitepaper of Rolls. NO derived quantity should be hardcoded into a formula - I should be able to get the correct dollar advantage if I change inputs (however, I will not change the settlement dates) Create a dollar roll matrix of breakeven rates for an agency MBS with gross and deal coupons of 8.035% and 7.5%, respectively, and settlement dates 6/14/18 and 8/15/18. Assume standard fully amortizing fixed rate mortgages with a term of 30:0, a WAM of 29:5 and an immediate price of 96-16. MBS CF's are due the owner of record on the first of each month, but are paid on the 25th of that month. Calculate a breakeven rate matrix for PSAs of 100, 150, and 200, and forward drops of 40,-35, and-30 (32's) NOTE: See LN5 on prepayment. For this HW, the following information will tell you how to get the dollar prepayment amount (aka Unscheduled Principal or USP) each month, from: the age of the pool since origination (t, in months), the outstanding principal at the time of payment (8), the scheduled principal due at the time of payment (SP), and the inputted prepayment speed (PSA). First, compute the annualized conditional prepayment rate (CPR) from t and the PSA CPR (PSA/100) 0.06 min(1, t/30) SMM-1-(1-CPR) (1/12) USP (B-SP) SMM Next, convert this into a monthly prepayment rate (SMM): Finally, the unscheduled principal expected that month is The connection between the age of the pool, t, and the remaining term (inputted) should be carefully checked against the Bloomberg pdf (is there a +1 or not?). This simply concerns the definition of "Rem Term on the Bloomberg screen