Jim has an annual income of $240,000. Jim is looking to buy a house with monthly property taxes of $140 and monthly homeowner’s insurance of $70. Jim has $178 in monthly student loan payments. Apple bank has a maximum front end DTI limit of 28% and a maximum back end DTI limit of 36%. Both limits must be satisfied. Apple bank is offering a fully amortizing 30 year FRM at an annual rate of 4.5%, with monthly payments, compounded monthly. Apple Bank allows a maximum LTV of 80%. How much is the most expensive house Jim can buy?

The amount of the most expensive house Jim can buy is $1,329,720.81.

Explanation:

Maximum LTV = 80%.

Annual income = $240,000

Monthly total income = Annual income / 12 = $240,000 / 12 = $20,000

Maximum front end DTI limit = 28%.

Front end DTI = (Monthly mortgage payment using Front end DTI + Monthly tax + Monthly insurance) / Monthly total income

28% = (monthly mortgage payment using Front end DTI + $140 + $70) / $20,000

28% * $20,000 = Monthly mortgage payment using Front end DTI + $210

$5,600 = Monthly mortgage payment using Front end DTI + $210

Monthly mortgage payment using Front end DTI = $5,600 - $210 = $5,390

Maximum back end DTI is 36%.

Back end DTI = (monthly mortgage payment using Back end DTI + monthly tax + monthly insurance + other debt payments) / monthly gross income.

36% = (monthly mortgage payment using Back end DTI + $140 + $70 + $178) / $20,000

36% * $20,000 = Monthly mortgage payment using Back end DTI + $388

$7,200 = Monthly mortgage payment using Back end DTI + $388

Monthly mortgage payment using Back end DTI = $7,200 - $388 = $6,812

Maximum monthly mortgage payment to satisfy both Front and Back end DTI = Lower of Monthly mortgage payment using Front end DTI and Monthly mortgage payment using Back end DTI = Monthly mortgage payment using Front end DTI = $5,390

The loan amount can now be calculated using the following Excel PV function:

Loan amount = PV(rate,nper,-pmt) (1)

Where:

rate = Monthly rate = Annual rate / 12 = 4.5% / 12 = 0.045 / 12 = 0.00375

nper = Number of period or months = Numbers of years of loan tenure * 12 = 30 * 12 = 360

pmt = monthly payment = Maximum monthly mortgage payment to satisfy both Front and Back end DTI = $5,390

Substituting all the values into equation (1), we have:

Maximum loan amount = PV(0.00375,360,-5390) (2)

Inserting =PV(0.00375,360,-5390) in any cell in an Excel sheet, we have:

Maximum loan amount = $1,063,776.65

Maximum house value can be calculated using the following formula:

LTV = Maximum loan amount / Maximum house value ……..……….. (3)

Substituting the relevant values into equation (2), we have:

80% = $1,063,776.65 / Maximum house value

Maximum value of house = $1,063,776.65 / 80%

Maximum value of house = $1,329,720.81

Since the Maximum value of house is $1,329,720.81, this implies that the amount of the most expensive house Jim can buy is $1,329,720.81.