This is one whole question that requires excel, please show excel formulas to answer the problems. thanks

Ann wants a mortgage to buy a house. Ann gives the following information to the bank: Income: $240k/ year or 20k/mon th 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? \begin{tabular}{|c|c|c|} \hline \multicolumn{2}{|l|}{ Borrowe info: } & \multirow{3}{*}{\begin{tabular}{l} Green text \\ represent inputs \end{tabular}} \\ \hline Income (annual) & $240,000,00 & \\ \hline Income (monthly) & $20,000.00 & \\ \hline Monthly debt (average) & 52,000.00 & \multirow{2}{*}{\begin{tabular}{l} Purple text represent \\ outputs \end{tabular}} \\ \hline Monthly Taxes + Inserance & $700.00 & \\ \hline Down Payment & $50,000.00 & \\ \hline \multicolumn{3}{|l|}{ Loan terms: } \\ \hline Annual Interest fate & 4.00x & \\ \hline Periodic Interest Rate & 0.3336 & \\ \hline maximum ft monthly ori & 2t oax & \\ \hline maximum BE menthly on & 36.90x & \\ \hline maximum LTV & 95 oox & \\ \hline maximum loan/DP & 19.00 & \\ \hline Mortgage Closing Costs & 55,000.00 & \\ \hline \multirow[t]{2}{*}{ Mortgage Duydown Points } & 1.75x & \\ \hline & & \\ \hline & & \\ \hline Term frears) & 30 & \\ \hline Perments per vear & 12 & \\ \hline Total Number of Parments & 3603 & \\ \hline Balioon & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} Maximum PMT given FE DT limit Maximum PMT piven BE DTI limit Maximum PMT given both FE \& aE DT limits Maximum loan given FE $ BE DT limits Maximum loan piven LTV limit and down-payment Maximum loan given incoms 8 coltateral tests Maximum payment given income \& collateral tests Hew much house can Ann afford? Total Mortgage Costs