please provide answers & excel formulas
AutoSave BESU. MPTS Excel Assignment Module 9 Attempt 1 Home Insert Draw Page Layout Formulas Data Review View Tell me Times New Roman 12 "" Il General LO Paste BIU !! CA $ % ) Conditions Formatting 1 D10 x & fe A B D E G 11 1 MPTS Excel Assignment 2 The Blue Algue Mortgage Company has originated a pool containing 75 ten-year fred rate mortgages with us 3 average balance of $100,000 each with 1% origination fee. The annual interest rate on all loans in the pool's 4 12% and all loans require monthly payments. 5 6 7 Initial Pool Balance 8 Annual Interest Rate 9 Maturity (years) 10 Payments per year 11 12 13 Question 1: Whut price should Blue Algae obtain the pool is sold immediately to an investor with a required 14 annual return of 11% and no prepayment is expected? 15 Payments from Prepaid Service Cash Flow to 16 Month Borrowers Interest Principal Principal Fee Pool Balance Investor 0 18 1 19 2 20 3 21 4 22 5 6 24 7 25 B 26 9 10 = Required Return Price 98 alasa SARRASSA 30 32 12 13 14 15 16 17 18 19 20 21 22 23 MPTS 36 39 + Ready Required Retam Price B D G H 139 140 Question 2: What price should Blue Algae obtain if the pool is sold immediately to an investor with a required 141 Laual return of 11% and the monthly prepayment rate 0.1%! 142 Payments from Prepaid Service Cash Flow to 143 Month Borrowers Taat Principal Principal Fee Pool Balance Investor 144 0 145 1 146 2 147 3 148 4 149 $ 150 6 151 7 152 8 153 9 154 10 155 11 156 12 157 13 ISS 14 159 15 160 16 ISI 17 162 18 19 164 20 165 21 166 22 167 23 1 24 16 25 170 26 171 27 172 28 172 29 174 30 175 31 176 32 177 33 IT 34 MPTS + esc 80 ODD FA F1 F2 F3 F5 # ER $ % & 10 X fx B D L A E F G H 1 Required Retum Price Quation 3t What price should Bloe Algue obtale of the pool is carved Bato MPTS and the lovestors requires -29% annual return after a monthy service fee of 0.1% be deducted (with no prepayment)? 0 1 Payment from Prepaid Service Cash Flow to 2 Month Borrowers Inst Principal Principal Fee Pool Balance Investors 73 0 74 1 75 2 176 3 277 4 273 5 279 6 280 7 281 8 282 9 283 10 24 11 333 256 13 287 14 238 15 29 16 290 17 291 18 250 19 294 295 21 23 23 24 25 26 391 293 22 100 301 300 101 304 23 29 SHAHR 31 MITS 44 esc 8 0 20 000 000 F6 72 F4 FI $2 F3 A & # 3 $ 4 % 5 1 6 Pasta BI a. A 19 IM % 9 I 010 Ax fe A B D E F G H $23 524 Question 5: Suppose 5 years have passed since the MPTS in Question 4 was originated and the prepayment 525 expectation was correct and will continue to be correct for the next 5 yain. What is the value of the pool #f 526 Investors today require a return of 6% annual return after a monthy service fee of 0.1% deducted and the 527 monthly prepayment rate is 0.1%? 528 529 Required Retur 530 Price 531 $32 533 Question 6 What he Bar Green Algae's net profit in Question 1 above If the originator's operating apenses 534 total $150,000? $35 536 Cash from investor $32 Cash to borrowers 538 Operating expenses 539 Net profit 540 581 543 544 545 546 547 5431 549 550 551 592 $50 554 559 556 557 55 960 361 52 SA MPTS + esc 000 2013 44 000 F2 F4 FS 76 7 >
