Tuesday, July 9, 2019

Impact of Prepayment and Points on Mortgage Yields

Post uses PMT, CUMIPMT, IRR and EFFECT to calculate a mortgage yield when there are points and the mortgage is paid off early.   Calculations presented here suggest that people who are highly likely to move in a short period of time rent rather than buy because if they do move their mortgage costs will be quite high.

Question:   What is the yield on a 30-year FRM mortgage with a loan balance of $500,000 where 2 points are charged if the mortgage is paid off after five years?   What is the yield on this mortgage if it is paid off after one year?   What are the implications of this result on whether people who may or may not move for a new job should purchase a house or rent?

Analysis:   The calculation on the mortgage yield is obtained taking the IRR of the cash flows and then using the EFFECT function to obtain the effective yield.

The initial flow from lender to creditor is $490,000 (The initial loan balance of $5000,000 minus the $10,000 value of points.)

The monthly flows from borrower to lender are obtained from the PMT function.  ($2,997.75) =PMT(0.06/12,360,500000)

The final monthly payment from borrower to lender includes both the final mortgage payment and the payoff balance on the loan after 60 months of payments.    The loan balance after 60 months is the initial loan balance minus the cumulative interest over the 60 months.

($500,000 minus CUMPRINC(0.06/12,360,500000,1,60,0)



This loan balance can also be obtained from the FV function.

One of the inputs of the FV function is the monthly payment, which is obtained from the PMT function.

=+PMT(0.06/12,360,-500000,0)  gives a monthly payment of $2997.75

The ending balance from the FV function is


or once again $465.272.

Next, take the IRR of 61 cash flows, the initial payment to the borrower, the 60 monthly mortgage payments and the loan payoff on the same date as the final mortgage payment.

The IRR of the 61 payments is 6.484 %.

This is higher than the IRR of a loan held for 30 years.   We found in a previous post the APR of a 6.0% FRM with 2 points held 30 years was 6.189 %.

The effective yield on the loan can be obtained with the EFFECT function.  

EFFECT(6.484%,12) = 6.680%

Mortgage Yields with One-Year Holding Period:  

Find the loan balance after 12 months by placing 12 as end period in the CUMPRINC function.   This gives us CUMPRINC $6,140.  Subtract from $500,000 to get the loan balance. 

Here is a view of the IRR calculation for mortgage yields with one-year holding period.

Mortgage Yields One-Year Holding Period With Two Points

The APR of the 6.0 % loan with two points is 8.10 % if the mortgage is paid back after one year.   The effective interest rate is 8.4%.

The bottom line here is that people who move after one year in a new house for whatever reason, a divorce or a new job, pay a higher effective rate on their mortgage than people who stay put when points are involved.

No comments:

Post a Comment