## Monday, August 28, 2017

### Prepayment and 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.

Prepayment and Mortgage Yields

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?

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 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 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: