PLEASE SHOW EXCEL FORMULAS

MPTS Excel Assignment The Blue Algae Mortgage Company has originated a pool containing 75 ten-year fixed rate mortgages with an average balance of $100,000 each with 1% origination fee. The annual interest rate on all loans in the pool is 12% and all loans require monthly payments. Initial Pool Balance Annual Interest Rate % Maturity (years) Payments per year Question 1: What price should Blue Algae obtain if the pool is sold immediately to an investor with a required annual return of 11% and no prepayment is expected? Payments from Borrowers Prepaid Principal Principal Service Fee Cash Flow to Investor Interest Pool Balance Required Return Price Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Question 2: What price should Blue Algae obtain if the pool is sold immediately to an investor with a required annual return of 11% and the monthly prepayment rate is 0.1%? Payments from Prepaid Service Cash Flow to Month Borrowers Interest Principal Principal Fee Pool Balance Investor 0 1 2 3 Required Return Price 4 Jau 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Question 3: What price should Blue Algae obtain if the pool is carved into MPTS and the investors require a 9% annual return after a monthy service fee of 0.1% is deducted (with no prepayment)? Payments from Borrowers Prepaid Service Interest Principal Principal Fee Cash Flow to Investors Pool Balance Required Return Price cm + V Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Question 4: What price should Blue Algae obtain if the pool is carved into MPTS and the investors require a 9% annual return after a monthy service fee of 0.1% is deducted and the monthly prepayment rate is 0.1%? Payments from Borrowers Month 0 Prepaid Service Interest Principal Principal Fee Cash Flow to Investors Pool Balance Required Return Price 2 3 On+000 6 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Question 5: Suppose 5 years have passed since the MPTS in Question 4 was originated and the prepayment expectation was correct and will continue to be correct for the next 5 years. What is the value of the pool if investors today require a return of 6% annual return after a monthy service fee of 0.1% is deducted and the monthly prepayment rate is 0.1%? Required Return Price Question 6: What is Blue Green Algae's net profit in Question 1 above if the originator's operating expenses total $150,000? Cash from investor Cash to borrowers Operating expenses Net profit MPTS Excel Assignment The Blue Algae Mortgage Company has originated a pool containing 75 ten-year fixed rate mortgages with an average balance of $100,000 each with 1% origination fee. The annual interest rate on all loans in the pool is 12% and all loans require monthly payments. Initial Pool Balance Annual Interest Rate % Maturity (years) Payments per year Question 1: What price should Blue Algae obtain if the pool is sold immediately to an investor with a required annual return of 11% and no prepayment is expected? Payments from Borrowers Prepaid Principal Principal Service Fee Cash Flow to Investor Interest Pool Balance Required Return Price Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Question 2: What price should Blue Algae obtain if the pool is sold immediately to an investor with a required annual return of 11% and the monthly prepayment rate is 0.1%? Payments from Prepaid Service Cash Flow to Month Borrowers Interest Principal Principal Fee Pool Balance Investor 0 1 2 3 Required Return Price 4 Jau 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Question 3: What price should Blue Algae obtain if the pool is carved into MPTS and the investors require a 9% annual return after a monthy service fee of 0.1% is deducted (with no prepayment)? Payments from Borrowers Prepaid Service Interest Principal Principal Fee Cash Flow to Investors Pool Balance Required Return Price cm + V Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Question 4: What price should Blue Algae obtain if the pool is carved into MPTS and the investors require a 9% annual return after a monthy service fee of 0.1% is deducted and the monthly prepayment rate is 0.1%? Payments from Borrowers Month 0 Prepaid Service Interest Principal Principal Fee Cash Flow to Investors Pool Balance Required Return Price 2 3 On+000 6 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Question 5: Suppose 5 years have passed since the MPTS in Question 4 was originated and the prepayment expectation was correct and will continue to be correct for the next 5 years. What is the value of the pool if investors today require a return of 6% annual return after a monthy service fee of 0.1% is deducted and the monthly prepayment rate is 0.1%? Required Return Price Question 6: What is Blue Green Algae's net profit in Question 1 above if the originator's operating expenses total $150,000? Cash from investor Cash to borrowers Operating expenses Net profit