please show how to do it in excel

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: LTV 95% Bo 19*DP 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? View + Underwriting A 1 Borrower info: 2 Income (annual) 3 Income (monthly) 4 Monthly debt (average) 5 Monthly Taxes + Insurance 6 Down Payment 7 8 Loan terms: 9 Annual Interest Rate 10 Periodic Interest Rate 11 maximum FE monthly DTI 12 maximum BE monthly DTI 13 maximum LTV 14 maximum loan/DP 15 Mortgage Closing Costs 16 Mortgage Buydown Points 17 19 Term (years) 20 Payments per year 21 Total Number of Payments 22 Balloon 23 24 SSELT85833 18 505 Zoom Media Comment Insert Table Chart Text Shape FA AMORTIZATION SCHEDULE FA CHARTS B D $240,000.00 $20,000.00 Green text represent inputs $2,000.00 Purple text represent $700.00 outputs $50,000.00 4.00% 0.33% 28.00% 36.00% 95.00% 19.00 $5,000.00 1.75% 30 12 360 0 10 Collaborate PA AMORTIZATION SCHEDULE PA CHARTS E Maximum PMT given FE DTI limit Maximum PMT given BE DTI limit Maximum PMT given both FE & BE DTI limits Maximum loan given FE & BE DTI limits Maximum loan given LTV limit and down-payment Maximum loan given income & collateral tests Maximum payment given income & collateral tests How much house can Ann afford? Total Mortgage Costs 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: LTV 95% Bo 19*DP 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? View + Underwriting A 1 Borrower info: 2 Income (annual) 3 Income (monthly) 4 Monthly debt (average) 5 Monthly Taxes + Insurance 6 Down Payment 7 8 Loan terms: 9 Annual Interest Rate 10 Periodic Interest Rate 11 maximum FE monthly DTI 12 maximum BE monthly DTI 13 maximum LTV 14 maximum loan/DP 15 Mortgage Closing Costs 16 Mortgage Buydown Points 17 19 Term (years) 20 Payments per year 21 Total Number of Payments 22 Balloon 23 24 SSELT85833 18 505 Zoom Media Comment Insert Table Chart Text Shape FA AMORTIZATION SCHEDULE FA CHARTS B D $240,000.00 $20,000.00 Green text represent inputs $2,000.00 Purple text represent $700.00 outputs $50,000.00 4.00% 0.33% 28.00% 36.00% 95.00% 19.00 $5,000.00 1.75% 30 12 360 0 10 Collaborate PA AMORTIZATION SCHEDULE PA CHARTS E Maximum PMT given FE DTI limit Maximum PMT given BE DTI limit Maximum PMT given both FE & BE DTI limits Maximum loan given FE & BE DTI limits Maximum loan given LTV limit and down-payment Maximum loan given income & collateral tests Maximum payment given income & collateral tests How much house can Ann afford? Total Mortgage Costs