Prepayment and Mortgage Yields
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?
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)
Note from a previous post that
loan balance at a point in time can also be obtained from the FV function.
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%
Bonus Question:
The borrower sells the house
and pays off the loan after 12 monthly payments. What is the APR and the effective interest
rate on the loan? What are the
implications of this result for people with uncertain circumstances who are on
the fence between renting or buying?
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 will 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.
I am continuing to add
material to my tutorial on Excel finance functions. Go to link below for list of problems.
Excel Finance Function
Problem List:
No comments:
Post a Comment