Question
Please use Excel and show formulas as well as results. I will upvote the correct answer. Please follow instructions below image Use MS Excel to
Please use Excel and show formulas as well as results. I will upvote the correct answer. Please follow instructions below image
Use MS Excel to set up a 10-year amortization schedule including the following columns: NPER (that is the number of period remaining to maturity), Beginning Balance, Interest Paid, Principal Paid, Principal Prepaid, and Ending Balance. Remember that interest paid does not reduce the loan balance. Also, the total mortgage payment amount will decrease each year due to the prepayments, so it may be helpful to create an extra column in which to calculate the total mortgage payment for each year. If your amortization schedule is set-up correctly the loan balance after ten years will be $0.
2. The Green Mortgage Company has originated a pool containing 75 ten-year fixed interest rate mortgages with an average balance of $100,000 each. All mortgages in the pool carry a coupon of 12 percent. (For simplicity, assume that all mortgage payments are made annually at 12% interest.) Green would now like to sell the pool to FNMA. a. Assuming a constant annual prepayment rate of 10 percent (for simplicity, assume that prepayments are based on the pool balance at the end of each year), what will be the price that Green should obtain on the date of issuance if market interest rates were (1) 11 percent? (2) 12 percent? (3) 9 percent? b. Assume that five years have passed since the date in (a). What will the pool factor be? If market interest rates are 12 percent, what price can Green obtain then? 2. The Green Mortgage Company has originated a pool containing 75 ten-year fixed interest rate mortgages with an average balance of $100,000 each. All mortgages in the pool carry a coupon of 12 percent. (For simplicity, assume that all mortgage payments are made annually at 12% interest.) Green would now like to sell the pool to FNMA. a. Assuming a constant annual prepayment rate of 10 percent (for simplicity, assume that prepayments are based on the pool balance at the end of each year), what will be the price that Green should obtain on the date of issuance if market interest rates were (1) 11 percent? (2) 12 percent? (3) 9 percent? b. Assume that five years have passed since the date in (a). What will the pool factor be? If market interest rates are 12 percent, what price can Green obtain thenStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started