Let's get started with some basics. There are a number of variables that must be accounted for in residential lending. I start with residential only because it is a little more involved than commercial mortgage modeling in that the borrower's potential lending behavior is of equal to greater importance than the value of the asset. Commercial lending, on the other hand, tends to be more asset based. Commercial mortgage financial models, therefore, tend to look more like cashflow projection models with mortgage-related concerns, such as float indices, added. Residential financial models, however, have a great deal of borrower-related variables tied into them. Ultimately, however, no matter what the underlying asset maybe, the central tenant of mortgage financial modeling is to predict the cashflows of the mortgage note in a multitude of scenarios. The more "flexible" your model, i.e. the more scenarios your model is able to predict accurately, the more valuable it is.
Now let's talk specifics. The following are a list of fields necessary for the "Description" or first page of your model:
Street Address: Allows one to evaluate local specifics of the property.
City: Has informational as well as legal ramifications for the note and asset.
State: Has informational as well as legal ramifications for the note and its underlying asset.
Zip: A good way to track lending trends, pool trends and also offers property level data.
County: Has informational as well as legal ramifications for the note and asset.
Property Type: In residential, the property types are Mobile Home, Houseboat (although both are technically financed with loans that are not mortgages, unless the land on the mobile home is also being covered in the loan), Single Family Home (SFR), 2 Family or Duplex (called a 2 flat in the Midwest), 3 Family, and 4 Family.
Year Built: Key in evaluating the property.
Year Renovated: Coupled with the year built, this value tells a story about the underlying property.
Lot Size: Self-explanatory
Price PSF: This can be used for both the square feet of the property or can be combined with a "Price per acre" field so that there is a calculation for the value of the improvements as well a value for the land itself. I prefer the later approach.
Taxes: Real Estate taxes are an unavoidable reality of real estate lending and investment. They must be accounted for in order to gain a true value of the note.
Amount of Tax Delinquency: Given that tax liens supersede even first position liens, keeping tax payments current is essential to maintaining a real estate investment. Delinquent taxes must be paid and interest penalties can hover around 25%, which is a safe number to estimate for such penalties. Actual delinquency penalty amounts, however, vary between municipalities.
Current Balance: Outstanding amounts speak to the value of the note.
Current Appraisal/BPO Amount: I believe both BPO's and appraisals should be used. Since real estate agents are more active in the sales market than appraisers, BPO's offer a better idea of a resell market value. Appraisals offer a manner in which to justify the value of an asset. This field should indicate whether the value used was derived from an appraisal or BPO. This can be done through color coating. or some other data validation method.
Date of Last Appraisal/BPO: Speaks to the currentness and therefore accuracy of the value of the property.
Underwritten Loan-to-Value (LTV): Reveals the underwriter's belief of value at the time and the amount skin the borrower had in the game.
Current LTV: We all know that the market can change in Real Estate. This value should compare the current balance of the note to the current value of the property, although I have some use it to compare the downpayment to the current value of the property. The second value yields much less useful information than the first.
Paid Through Date: The date of last payment speaks to the notes performance.
Pre-payment Penalty Amount: This value will tell you how much the note has been structured to offset prepayment loss. This amount is a statement of the risk of prepayment inherent in the note.
Type of Mortgage: There are many mortgage types like fixed, ARM, I/O, floating, hybrid, etc. Within these larger categories, there are many sub-categories.
Original Interest Rate: If the mortgage is fixed, this is the interest rate. If not this may or may not be the present interest rate of the note.
Current Interest Rate: Can vary from original
Interest Only Period: The length of time that mortgage payments are applied only to the amount of interest due on the mortgage and to principal.
Loan Term: This is important for Present Value (PV), Internal Rate of Return (IRR) and other cashflow considerations.
Amortization Term: This term is frequently not the same as the loan term. This term is crucial to interest calculations.
Margin Over Prime: Not typical of today's lending, but this field may still be useful.
Capped Rate: In some mortgage products, this term may be useful.
Note Floor Rate/Note Ceiling: These 2 separate fields can also be combined into a "Capped Rate" in some cases, but not all cases.
Default Margin Over Original Rate: This field should be a calculated field that expresses the risk premium of the note expressed by the note value given its underwritten rate.
Origination Date: Necessary to understand the ideology of the note.
Seasoning Period: Necessary to weed out the financing of flips, which cuts down on inertest gained on the notes. Notes without seasoning requirements should have their first few years of cashflows discounted by prepayment risk. This will, naturally, affect the notes PV.
Lien Position: Does anything other than 1st still exist? HELOC and 2nd bought at steep discounts may still yield some juice.
Borrower's FICO Scores: This is very important in residential lending. I tend to create 3 different columns for the 3 scores. Although the average FICO is a useful number, you'll find a borrower with 3 670 scores will behave differently than a borrower with 2 scores of 615 and an artificially high score of 780 or a borrower with 2 scores of 720 and an erroneous score of 580.
Borrower's Back End Ratio: Monthly debt, plus PITI, divided by monthly gross income. Doesn't included non-property insurance, cell phone, utilities and anything not reflected on your credit report. If above 36%, there may be a problem (see http://www.fha-home-loans.com/debt_ratios_fha_loans.htm for more information on back end ratios, although its percentage guidelines should be updated).
Borrower's Front End Ratio: Monthly housing expenses divided by monthly income, If above 28% there may be a problem.
Required Reserve Capital: This is becoming more of a factor with the Congressional requirements of reserving against most private label MBS-related loans.
These are some of the fields that should be entered on the first page of your valuation model. Please feel free to post any others that I may have missed. Please stay tuned to our next topic: FICO scores.