1937 photo of a storefront in Oklahoma City.

NCRC’s HMDA 2018 methodology: How to calculate loan price

In 2010, the Dodd-Frank Wall Street Reform and Consumer Protection Act instructed the Consumer Finance Protection Bureau (CFPB) to fundamentally change the data collected under the Home Mortgage Disclosure Act (HMDA). Among the changes described in the bill was guidance on collecting more detailed data about the costs associated with the loan that were paid by consumers as interest or fees at closing.

To accomplish this, the CFPB issued a final rule in 2015, amending Regulation C of HMDA to implement these changes. Prior to this time, HMDA collected very little information on the cost of the loan. This was primarily known as the ‘high-cost flag’ which indicated if the interest rate at closing was more than 1.5% above the Average Percent Offer Rate (APOR) published daily by Freddie Mac based on internal surveys of loans in their underwriting system.

High-cost loans are often used as a proxy for subprime or even predatory mortgages. More accurate data on the loan price would go a long way to confirming if these loans are in fact dangerous, or if the higher interest rate reflects other legitimate factors. In the 2018 HMDA public data file, there are several new data points, or variables, that are related to the cost of the loan including:

  • Loan amount
  • Loan-to-value ratio (this is calculated as ([loan amount/property value]*100)
  • Interest rate (this is the simpler rate quoted to the borrower and does not account for compounding)
  • Rate spread (this is the percent difference in the interest rate compared with the APOR for the day the loan closed)
  • Closing fees are collected under several categories
    • Total loan costs or Total points and fees (this includes the origination fee as well as charges for other services related to closing)
    • Origination charges (this amount is also included in total loan costs)
    • Discount points (points paid to lower the interest rate)
    • Lender credits (this is a credit to the borrower)
  • Loan term
  • Property value
  • Income
  • Debt-to-income ratio

Several of these variables offer explanatory value for future analyses but first the accurate measurement of loan costs must be assessed. To do this, two figures must be collected — the Annual Percent Yield (APY) of the loan and the closing costs.

Closing costs are paid by the borrower when the sale is complete, and may be rolled into the loan or paid in cash. Not all of the fees associated with closing are included in HMDA. Certain pass-through costs, such as the property appraisal or credit reporting fees, are paid by the lender and passed through to the borrower. Those fees included in HMDA should accurately be considered as profit to the lender.

To calculate these costs we use the following formula:

Total Charges to Borrower = Total loan costs + Total points and fees + Discount points – Lender credits

The APY is more complex but by accurately determining it we can also determine the monthly payment the borrower would be initially responsible for paying. To calculate the APY, we use the following formula:

APY = (1 + r/12 )12 – 1

Where r is the stated annual interest rate and 12 is the number of compounding periods per year. (thebalance.com)

Using this calculation, we can then determine the principal and interest (P&I) payment that the borrower would be responsible for each month. To do this, we use the following formula:


Where M is the monthly payment, P is the loan amount, APY comes from the first calculation we did and n is the number of payments (loan term).

In Tableau, due to the way in which the software processes the order of operations, we perform this calculation in three steps:

  1. The calculated field that produces the payment numerator:

(([Loan Amount])((([APY]/100))/12)((1+(([APY]/100))/12)^[Loan Term]))

2.  The calculated field that produces the payment denominator:

((1+(([APY]/100))/12)^[Loan Term])-1

  1. Finally, we divide the two together with a third calculated field:

[Payment Numerator]/[Payment Denominator]

It may be possible in a database system to use programming code to accurately include the closing costs associated with the loan into the APY.  This can be done in excel using advanced finance functions but is not possible in Tableau. In testing, this was found to produce a small increase in the APY. However, due to the large difference between most mortgage loan amounts and the closing fees, this is a very low amount. For these reasons, we have chosen to keep the APY and closing fees as separate variables to be analyzed in conjunction with each other.

The rate spread offers another option to determine an undisclosed variable from the dataset – the APY on the day of closing. Since the CFPB has chosen not to release details on the date the loan closed, one loan could theoretically have been made in a very different interest rate environment than another loan that is otherwise identical. Since this could reduce the validity of any analysis, it is important to eliminate this variation. The rate spread offers a way to accomplish this. The rate spread is the difference between the interest rate on the loan and the APOR for the day when the loan closed or when the final interest rate was set. By adding the APOR to the interest rate, we can normalize this figure. For example, see below:

Loan Loan Amount APY Rate Spread Date Adjusted APY
Loan A $255,000 4.875% .435 5.31%
Loan B $255,000 4.875% -0.445 4.430%

Loan A and Loan B are identical in nearly every way, however, since we can assume that the loans were made during different time periods within 2018 the APOR was different for each. In the case of Loan A, the borrower was offered a rate 43.5 basis points (bps) over the APOR, and the borrower in Loan B was offered a rate 44.5 bps below the APOR. Therefore, Borrower B received a better price than Borrower A, paying 88 bps (0.88%) less in interest. Over 30 years this equals a difference of $49,013.16. Price information in HMDA offers researchers the opportunity to properly examine the disparate impact on borrowers in ways other than simply noting if the loan was originated or denied. Loan closing fees and interest rates offer a powerful way to quantify disparate impact and predatory lending.

Jason Richardson is NCRC’s Director of Research and Evaluation.

Photo by The New York Public Library on Unsplash

Print Friendly, PDF & Email
Scroll to Top