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
Anns 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%B_019*DP_0
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 Anns $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 Anns total mortgage closing costs?
1 Borrower info: 2 Income (annual) 3 Income (monthly) 4 Monthly debt (average) 5 Monthly Taxes + Insurance 6 Down Payment $240,000.00 Green text $20,000.00 represent inputs $2,000.00 Purple text $700.00 represent outputs $50,000.00 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 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 4.00% 0.33% 28.00% 36.00% 95.00% 19.00 $5,000.00 1.75% 19 Term (years) 20 Payments per year 21 Total Number of Payments 22 Balloon 30 12 360 $2,500,000.00 $50,000.00 Green text represent inputs Purple text represent outputs $5,000.00 1.75% House Price Down Payment Mortgage Mortgage Closing Costs Mortgage Buydown Points Total Mortgage Costs Annual Contract Rate Term (years) Payments per year Total Number of Payment Balloon 4.00% 30 12 360 $0.00 Home Equity in 30 years HPA Mortgage cashflow IRR Monthly IRR Annualized IRR Scenario : Optimistic Base Pessimistic Very Bad Home Equity Jin 10 years 4.50% 2.50% 0.00% -6.00% Month Principal Payment Loan Balance (after pm Mortgage Payment Interest $0.00 $0.00 $0.00 $ - Cash inflow to borrowe Cash outflow from borrowe Net Cash Flow to Borrower $0.00 $0.00 $0.00 0.00 $0.00 Optimistic $2,500,000.00 $2,509,375.00 House Price Base Pessimistic $2,500,000.00 $2,500,000.00 $2,505,208.33 $2,500,000.00 Very Bad $2,500,000.00 $2,487,500.00 Home Equity Optimistic Base P essimistic Very Bad $2,500,000.00 $2,500,000.00 $2,500,000.00 $2,500,000 $2,509,375.00 $2,505,208.33 $2,500,000.00 $2,487,500 $0.00 2 5 6 8 10 11 12 13 16 Green text represent inputs Purple text represent outputs House Price 2 Down Payment 3 Mortgage # Mortgage Closing Costs 5 Mortgage Buydown Points 5 Total Mortgage Costs - Annual Contract Rate 3 Term (years) Payments per year 0 Total Number of Payments 1 Balloon $2,500,000.00 $50,000.00 $2,450,000.00 $5,000.00 1.75% $47,875.00 4.00% 30 12 360 $500,000.00 Home Equity Home Equity in 10 years in 30 years HPA Mortgage cashflow IRR Monthly IRR Annualized IRR Scenario: Optimistic Base Pessimistic Very Bad 4.50% 2.50% 0.00% -6.00% 9 Month House Price Pessimistic Loan Balance (after pmt) Mortgage Payment Interest Principal Payment Cash inflow to borrower Cash outflow from borrower Net Cash Flow to Borrower Optimistic Base Very Bad Optimistic om in oonm 1 Borrower info: 2 Income (annual) 3 Income (monthly) 4 Monthly debt (average) 5 Monthly Taxes + Insurance 6 Down Payment $240,000.00 Green text $20,000.00 represent inputs $2,000.00 Purple text $700.00 represent outputs $50,000.00 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 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 4.00% 0.33% 28.00% 36.00% 95.00% 19.00 $5,000.00 1.75% 19 Term (years) 20 Payments per year 21 Total Number of Payments 22 Balloon 30 12 360 $2,500,000.00 $50,000.00 Green text represent inputs Purple text represent outputs $5,000.00 1.75% House Price Down Payment Mortgage Mortgage Closing Costs Mortgage Buydown Points Total Mortgage Costs Annual Contract Rate Term (years) Payments per year Total Number of Payment Balloon 4.00% 30 12 360 $0.00 Home Equity in 30 years HPA Mortgage cashflow IRR Monthly IRR Annualized IRR Scenario : Optimistic Base Pessimistic Very Bad Home Equity Jin 10 years 4.50% 2.50% 0.00% -6.00% Month Principal Payment Loan Balance (after pm Mortgage Payment Interest $0.00 $0.00 $0.00 $ - Cash inflow to borrowe Cash outflow from borrowe Net Cash Flow to Borrower $0.00 $0.00 $0.00 0.00 $0.00 Optimistic $2,500,000.00 $2,509,375.00 House Price Base Pessimistic $2,500,000.00 $2,500,000.00 $2,505,208.33 $2,500,000.00 Very Bad $2,500,000.00 $2,487,500.00 Home Equity Optimistic Base P essimistic Very Bad $2,500,000.00 $2,500,000.00 $2,500,000.00 $2,500,000 $2,509,375.00 $2,505,208.33 $2,500,000.00 $2,487,500 $0.00 2 5 6 8 10 11 12 13 16 Green text represent inputs Purple text represent outputs House Price 2 Down Payment 3 Mortgage # Mortgage Closing Costs 5 Mortgage Buydown Points 5 Total Mortgage Costs - Annual Contract Rate 3 Term (years) Payments per year 0 Total Number of Payments 1 Balloon $2,500,000.00 $50,000.00 $2,450,000.00 $5,000.00 1.75% $47,875.00 4.00% 30 12 360 $500,000.00 Home Equity Home Equity in 10 years in 30 years HPA Mortgage cashflow IRR Monthly IRR Annualized IRR Scenario: Optimistic Base Pessimistic Very Bad 4.50% 2.50% 0.00% -6.00% 9 Month House Price Pessimistic Loan Balance (after pmt) Mortgage Payment Interest Principal Payment Cash inflow to borrower Cash outflow from borrower Net Cash Flow to Borrower Optimistic Base Very Bad Optimistic om in oonm
