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
30year 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)
or
$465,272
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
=FV(0.06/12,60,2997.75,500000,0)
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 OneYear 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
oneyear holding period.
Mortgage
Yields OneYear Holding Period With Two Points


0

$490,000

1

($2,997.75)

2

($2,997.75)

3

($2,997.75)

4

($2,997.75)

5

($2,997.75)

6

($2,997.75)

7

($2,997.75)

8

($2,997.75)

9

($2,997.75)

10

($2,997.75)

11

($2,997.75)

12

($496,857.69)

IRR*12

8.100%

Effect

8.408%

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