Please use the spreadsheet to solve and show equations

PLEASE ENTER ANSWERS IN BLUE BOLD INK! Ann wants a mortgage to buy a house. Ann gives the following information to the bank: Income: $240k/ year or 20k/ month Average monthly debt: $2k Estimated monthly Taxes + Insurance: $700 Down-payment: $50k saved -Ann's down-payment will be $50k, she will take out a mortgage for the remainder Ann qualifies for a 30 year FA-CPM-FRM (monthly payments \& monthly compounding) with: Annual interest rate: 4% Income test: (28%/36%) Collateral test: LTV95%B019DP0 Closing costs + buy-down points: $5,000+1.75% of the balance at origination. -Example: if Ann gets a $100k mortgage, she will pay $5k+$1.75k=$6.75k at origination. 1 Underwriting 1.1: Fill in the spreadsheet (sheet "Underwriting") for Ann. 1.2: based on the FE DTI what is the biggest payment Ann can make? 1.3: based on the BE DTI what is the biggest payment Ann can make? 1.4: based on both the FE \& BE DTI what is the biggest payment Ann can make? 1.5: based on both the FE \& BE DTI what is the biggest loan Ann can get? 1.6: based the LTV test and Ann's $50k down-payment, what is the biggest loan Ann can get? 1.7: based on the income \& collateral tests, what is the biggest loan Ann can get? 1.8: based on the income \& collateral tests, what is the biggest payment Ann can make? 1.9: if Ann makes a 50k down-payment, how much house can Ann afford? 1.10: how much are Ann's total mortgage closing costs? Borrower info: Income (annual) Income (monthly) $240,000.00representinputsGreentext Maximum PMT given FE DTI limit Maximum PMT given BE DTI limit Monthly debt (average) Monthly Taxes + Insurance $2,000.00 Purple text represent Maximum PMT given both FE \& BE DTI limits Down Payment $50,000.00 outputs Maximum loan given FE \& BE DTI limits Maximum loan given LTV limit and down-payment Maximum loan given income \& collateral tests Loan terms: Maximum payment given income \& collateral tests Annual Interest Rate How much house can Ann afford? Periodic Interest Rate Total Mortgage Costs maximum FE monthly DTI maximum BE monthly DTI maximum LTV maximum loan/DP Mortgage Closing Costs Mortgage Buydown Points $5,000.00 1.75% Term (years) Payments per year Total Number of Payments Balloon 30123607