Spread sheet assignment Real Estate and Finance I certify that I alone completed this assignment and that I received no assistance. Student's Name (Must be turned in with assignment) Take the last three digits of Social Security Number _X 1000 - Price of house. (If first digit is zero, replace it with a 5, for example: 089 would become 589x1000=589,000-price of house.) A borrower wants to evaluate the loans listed below and anticipates owning the new home for years. Calculate the payments, loan balance at the end of year 8 and yield for each mortgage for the 8 year period using spread sheets. Based on estimated forward rates, the index to which the ARM is tied is forecast as follows: EOY six months: 5.5% EOY 1: 4.10% EOY 18months: 5.75% EOY 2:6.15% EOY 3months: 8.5% EOY 3:9.25% EOY 42months: 9.5% EOY 4: 10.2% EOY 54months: 11% EOY 5: 13.0% EOY 66months: 10.0% EOY 6: 8.0% EOY 78months: 7.5% EOY7: 6.0% The five different mortgages are listed below: Mortgage A: FRM @6.30 % for 30 years with 20% down payment, 1.25 point Mortgage B: ARM @ 5.5% for 30 years with 20% down payment, 1.5 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 1.50%. This loan has an annual interest rate cap of 1% and 4% interest cap over the life of the loan and no negative amortization Mortgage C ARM @ 4.0% for 30 year with 20% down payment,.75 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 1.25%. This Ioan has a payment cap of 8% and allows negative amortization. Mortgage D: ARM @ 3.23% for 30 years with 20% down payment, 0.5 points, adjustable annually based on the index of one-year Treasures plus a margin of 10% This loan has no caps. From your answers above which loan would you choose and WHY? Explain why the initial rate for each of the mortgages is different. Make sure to discuss the risk. Spread sheet assignment Real Estate and Finance I certify that I alone completed this assignment and that I received no assistance. Student's Name (Must be turned in with assignment) Take the last three digits of Social Security Number _X 1000 - Price of house. (If first digit is zero, replace it with a 5, for example: 089 would become 589x1000=589,000-price of house.) A borrower wants to evaluate the loans listed below and anticipates owning the new home for years. Calculate the payments, loan balance at the end of year 8 and yield for each mortgage for the 8 year period using spread sheets. Based on estimated forward rates, the index to which the ARM is tied is forecast as follows: EOY six months: 5.5% EOY 1: 4.10% EOY 18months: 5.75% EOY 2:6.15% EOY 3months: 8.5% EOY 3:9.25% EOY 42months: 9.5% EOY 4: 10.2% EOY 54months: 11% EOY 5: 13.0% EOY 66months: 10.0% EOY 6: 8.0% EOY 78months: 7.5% EOY7: 6.0% The five different mortgages are listed below: Mortgage A: FRM @6.30 % for 30 years with 20% down payment, 1.25 point Mortgage B: ARM @ 5.5% for 30 years with 20% down payment, 1.5 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 1.50%. This loan has an annual interest rate cap of 1% and 4% interest cap over the life of the loan and no negative amortization Mortgage C ARM @ 4.0% for 30 year with 20% down payment,.75 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 1.25%. This Ioan has a payment cap of 8% and allows negative amortization. Mortgage D: ARM @ 3.23% for 30 years with 20% down payment, 0.5 points, adjustable annually based on the index of one-year Treasures plus a margin of 10% This loan has no caps. From your answers above which loan would you choose and WHY? Explain why the initial rate for each of the mortgages is different. Make sure to discuss the risk