## Sunday, August 27, 2017

### Yield on a Mortgage with Points

Yield on a Mortgage with Points

Post uses PMT and IRR

Question:   Consider a 30-year Fixed Rate Mortgage with a 6.0 % APR.   What is the yield on the loan if the lender charges 0 points, 1 point, or 2 points?   Assume the loan is held for all thirty years.

Analysis:   The yield on a loan with no points is simply the APR.   The reader can confirm this in the following manner.

Find the monthly payment for the loan (=PMT(0.06/12,360,500000)= (\$2,997.75))

Find the IRR of a series of cash flows starting at time 0 with a \$500,000 disbursement followed by 360 monthly payments of \$2967.75.

Observe the IRR is 6.0 % the same as the APR with no points.

When there are points the net disbursement at time 0 is the loan balance minus the dollar value of the points.   The dollar value of points on a \$500,000 loan is \$5,000 when points are 1.0 percent and \$10,000 when points are 2 percent.   Net disbursements at time 0 are \$495,000 at 1 point and \$490,000 at 2 points.

To get yield take the IRR of the series of cash flows where disbursement is made at time 0 and the monthly mortgage payment is made for 360 consecutive months.   The results are presented below.

 Yield on a 30-year FRM with 6.0 Percent Annual Mortgage  Rate by Level of points Points 0 1 2 IRR 6.000% 6.094% 6.189% EFFECTIVE RATE 6.168% 6.267% 6.368%

The IRR gives us the annual percentage rate.    The effective rate accounts for monthly payments as described in a previous post.  The effective rate was obtained with the EFFECT function.

Go here for a discussion of the EFFECT function.
http://www.dailymathproblem.com/2017/08/the-effect-function.html

Other exercises involving points:

How do yields of 15-year FRM loans vary with points?

What are the yields of a 30-year mortgage when the home buyer moves or refinances and repays the loan after 5 years?  What are yields when repayment occurs after 7 years?

(THIS IS MY NEXT PROBLEM.   WILL PUT LINK TO THIS PROBLEM HERE SOON.)

Authors Note:   I am continuing creating posts solving financial problems with Excel functions.  Go to the page below for the current list of problems.