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.



Come back to this page frequently because I am adding lots of new questions.  

No comments:

Post a Comment