The Real Estate Settlement Procedures Act mandates that conventional mortgage loans have a clearly stated interest rate. In spite of the rate transparency, it can be difficult to properly calculate a yield on a loan. Fees and closing costs complicate the yield calculation.
Identify the total amount of fees and closing costs for your conventional mortgage loan. Fees and closing costs are detailed in the mandatory "Truth in Lending" disclosure provided to all mortgage applicants.
Determine the amount borrowed and the interest rate. Both are highlighted in bold at the top of the "Truth in Lending" disclosure. The amount borrowed will be different from the purchase price of the home after down payment because fees and closing costs are added to the principal.
Note the monthly mortgage payment. Include any processing or service fees.
Using a spreadsheet or online calculator, enter the appropriate values for principal balance, number of monthly payments, stated interest rate, and balloon payment amount (if any). For a Microsoft Excel-compatible spreadsheet, first enter each of these values into a single blank cell. Then, enter the formula to determine yield into a different blank cell. To select an individual value within the formula, simply click on the cell containing the value. Avoid typing values directly into the formula.
The formula (as provided by Microsoft) to determine yield is:
"=YIELD(settlement,maturity,rate,pr,redemption,frequency)"
where:
"settlement" is the loan's settlement date. The settlement date is the date when the loan funds are disbursed to the borrower. Type the date in MM/DD/YYYY format
"maturity" is the loan's maturity date. The maturity date is the date when the loan is paid off. Type the date in MM/DD/YYYY format
"rate" is the loan's stated interest rate. Type the rate as a decimal. 0.05 equals 5 percent.
"pr" is the principal amount borrowed. Type the amount in U.S. dollars.
"redemption" is the balloon payment amount due upon maturity. For conventional 15 or 30-year fixed loans, this amount is 0. Type the amount in U.S. dollars.
"frequency" is the number of mortgage payments per year. For most mortgages, this number is 12.
Compare the yields on different loans. The loan with the lowest yield is the cheapest financial option assuming non-financial factors are equal.
