I was recently asked to provide a spreadsheet that would calculate the add-on interest rate (APR) that would allow the originator of a mortgage to pay for the origination expenses (title, escrow, etc.) of the borrower. Typically, the borrower would just add the expenses to the loan balance, but there may be reasons why the borrower does not want to increase his/her debt load.
This approach can obviously be risky to the originator, if the right prepayments are not put in place. The linked spreadsheet allows for numerous combinations for a fixed rate loan. The default example is a $450,000 mortgage that is originated at 4.50% to be amortized over 30 years. The total expenses the borrower wants the originator to finance, by increasing the APR, is $6,285.
The originator decides to give the borrower a 30-year loan, with a prepayment period of 60 months (5 Years). At or after 5 years, the borrower can refinance or sell the property without a prepayment penalty. It is important that the loan agreement states that any reduction in principal value, including additional payments, will trigger a prepayment penalty over the prepayment period. The calculation shows that increasing the APR from 4.50% to 4.824% will allow the lender recover the $6,285, plus earn 4.50% interest on the expenses. This is an increase of 32.4 basis points. It should be noted that the calculated new rate is actually carried out more decimal places and not rounded for purposes of this spreadsheet.
In addition, the monthly prepayment penalty is calculated. The example shows the prepayment on month 40 would be $2,204.12:
In addition, a proof on this input sheet shows that the repayment of the loan, including the prepayment penalty at month 40, will have returned enough over the 40 months to equate, on a present value basis, the original $450,000 in principal, plus the $6,285 in expenses, discounted at the original loan rate of 4.50%:
There are two additional proofs on the “Calcs and Proofs” sheet.
After recouping the expenses with the higher interest rate, you might want to include in the loan agreement that the interest rate will revert back to the original market rate (in the case of this example 4.50%).
This spreadsheet has not been tested in the real world. If you find problems let me know.