Tuesday, July 5, 2022

New Excel Workbook Includes Multiple Home Loan Affordability Estimating Tools.

Independence Day (appropriately) marked the start of my 16th year of retirement; I started this blog the week after I retired and 3,500+ posts later here we are. 

I've noticed recent news stories that project how many potential homebuyers will be priced out of the market because of increasing interest rates. 

While rising interest rates clearly decrease the pool of eligible buyers there are a variety of  loan level and program specific adjustments that can be made that would increase home loan affordability. 

Down payments and variable rate loans are two examples, but the use of compensating factors (FHA) and residual income (VA) are two less than obvious variables that can impact affordability calculations. 

Figuring out the impact of those adjustments can be complex so I compiled a unique new Excel workbook HERE that allows users to estimate home loan affordability using a wide range of variables and loan programs. 

There are a total of 5 worksheets currently in this Loan Qualification Tools workbook. 

1. Side by Side Loan Estimator: 

This worksheet uses as defaults the variables used for the 2022 Harvard Joint Center for Housing Metro affordability map. A total of 5 side by side scenarios are available for the user.

Each scenario allows different variables for purchase price, interest rate, down payment, taxes and insurance, mortgage insurance rate and the side-by-side structure allows easy comparisons of qualifying income as individual variables are changed.

Graph (Pasted below)

The worksheet includes a graph showing Portland metro comparisons for all 5 scenarios. If you change the variable inputs you can relabel the scenario names and than also change the title text in the graph. (To also keep the Portland data simply make a copy of the original worksheet and make changes to the copied worksheet). 

Note that the default entries include a variable rate loan rate (4.5%) in Scenarios 3-5, a  20% down payment in Scenario 5, and a $200 monthly recurring debt in Scenario 2 and 4.  All of these variables impact the maximum loan amount and the borrower ability to qualify. 

2. FHA Loan Estimator, Includes "Compensating Factor" Analysis

This unique workbook shows that qualifying income for a given FHA purchase price can vary significantly based on FHA specific “compensating factors”. 

A total of four side by side scenarios with different compensating factors are shown. The underwriting ratios used for each scenario are locked as they represent ratios permissible with specific compensating factors. 

The base loan amount in the left column can be compared with the loan amount permitted via three different “compensating factor” scenarios. The worksheet includes not only the monthly mortgage insurance amount but also the up front mortgage insurance fees and adds it to the loan amount.

Unlike the Side by Side and VA Estimator the default down payment is 3.5% not 5%.

The qualifying income required for the monthly expense is calculated and also compared to the median family income for the Oregon county for 2022; the worksheet also incorporates the 2022 FHA mortgage limits by county. 

The results showing the income required to qualify using the front and back ratios are displayed in a graph below the table. 

3. VA Loan Estimator

This was the most challenging estimator to create because of multiple inputs required, including a complicated calculation of residual income that varies by home size and family size and an FICA cost input. 

This estimator is for 1st time users of VA loans, not for prior VA borrowers who still have some, but not complete access, to their VA loan entitlement). This estimator is also NOT for Oregon Department of Veterans Affairs loans--those require mortgage insurance and limit LTV to 95%. 

Unlike the other estimators there is no mortgage insurance variable since VA charges an upfront fee in lieu of on going mortgage insurance. This fee however is waived for veterans who receive disability compensation (10% or above) or those in service who have a purple heart. [The 5 year ACS 2020 (Table B21100) estimate is that 66,000+/25% of Oregon veterans have a 10% or higher disability rating]. 

The target back ratio for VA loans is 41%, not 43%. This may be exceeded with compensating factors but unlike FHA loans,  I have not located a matrix with specific factors that would justify a higher than 41% DTI. (When DTI>41% there is a higher requirement for residual income, and "high residual income" is one permissible compensating factor). 

While I have entered a default 5% down payment VA loans can be zero down  which would then increase the income required to qualify. 

Because this estimator focuses on one loan at a time it ALSO includes a unique loan equity/appreciation estimator that shows as of  the end of year 1 and year 5 the projected amount of home equity using user variable input of the estimated annual appreciation rate. 

For the default entries, assuming 1% annual price appreciation I calculate that total increase in equity at the end of year 5 would be $70,110,including $29,331 in home price appreciation. 

4. Input and Output Matrix 

This worksheet shows for each of the three estimators the inputs made and the calculations that result from those inputs. While there are the same inputs for multiple line items, there are also inputs and outputs unique to specific FHA and VA loan programs. 

5. Links

This worksheet lists a variety of reference sources I used to create these estimators.

It also includes an ACS table showing distribution of Oregon veterans receiving service related compensation by their rating level. 


For the side by side and VA estimators, I've set as the default entry a $575,000 sales price, the May 2022 RMLS median sales price for the Portland metro area and a fixed interest rate of 5.7% and variable rate of 4.5% using the latest Freddie Mac primary mortgage survey rates as of June 30, 2022.  The FHA estimator uses a 3.5% down estimate but all other default entries are the same as the Side by Side estimator (This graph does not include a specific VA loan estimate).

I have added a note with the version number for each of the three estimators and have locked cells where appropriate to avoid inadvertent data entry. 

Originally created and posted on the Oregon Housing Blog


No comments:

Post a Comment