logo reporter

Mortgage Analysis Worksheet

by Arnold Kling

Why use a worksheet?

In general, you will find that the 30-year fixed-rate mortgage is ideal for someone living on a fixed income who plans to stay in the same house for 25 years. Otherwise, for the informed consumer, an alternative mortgage product can be superior. Glink lists "choosing the wrong mortgage" as one of the top ten mistakes made by first-time homebuyers.

(Many of the ideas in this article are implemented in The Intelligent Mortgage Agent.)

Is there a software package that a borrower can use to help choose a mortgage? Actually, any spreadsheet program is more than adequate. Using the instructions below, it takes no more time to set up your own spreadsheet than to figure out someone else's data entry screen, and once you have your spreadsheet you can adapt it to answer your particular questions.

There are two types of analysis one does regarding mortgages. One type of analyis looks at affordability of the payment relative to your budget. For example, see the worksheet at the end of the article on How much can you afford?.

Another type of analysis is economic analysis. When should you choose a mortgage with higher points but a lower rate? Should you select a fixed-rate or an adjustable mortgage? etc.

Even if you do not employ this spreadsheet to analyze which type of mortgage to use, you ought to set it up to track your own mortgage and make certain that your lender does not commit any errors. Sad to say, the error rate, particularly with adjustable-rate mortgages (ARMs) is quite high in the industry.

Below is a worksheet that you can use to start to do some economic analysis that compares mortgages. The first 7 lines of the worksheet reproduce the mortgage calculator. The remaining lines track the balance of the mortgage from the initial loan balance until the loan is paid off (a process known as amortization).

Worksheet Instructions

line item                    formula                 example
1. term in years input 30 2. interest rate input 12.0 3. initial balance input 100,000 4. term in months 12*(line 1) 360 5. monthly decimal rate (line 2)/1200 0.01 6. exponential factor (1+line 5)^(line 4) 35.9496 7. monthly payment (line 3)*(line 5)*(line 6)/ ($) (line 6 - 1.0) 1028.61
8. balance after (line 3)+(line 3)*(line 5) 1 payment ($) - (line 7) 99,971.39 9. balance after (line 8)+(line 8)*(line 5) 2 payments (S) - (line 7) 99,942.49 10. balance after (line 9)+(line 9)*(line 5) 3 payments ($) - (line 7) 99,913.30 . . . 367.balance after (line 366) + (line 366)* 360 payments (line 5) - line 7 0.00

Example: rates vs. points

Now, let's compare some alternative mortgages. Suppose that another lender offers a mortgage of 11-7/8 but with an additional 1/2 point up front. How would this work? Assuming we are using a spreadsheet, we set up another column to try this alternative.

Let's assume that we cannot come up with additional money out of thin air, so we finance the 1/2 point (0.5 %, or $500 on a $100,000 loan balance). That makes our initial balance (line 3) $100,500. The mortgage rate is 11.875, to be entered on line 2.

Next, we will force (hardcode) our monthly payment (line 7) to be the same as on the 12.0 percent mortgage, that is $1028.61. The idea here is to have the same cash flow pattern as the 12 percent mortgage--no additional money down, no difference in monthly payment.

If you put these mortgages side by side on your own spreadsheet and look down to month 66, the balances on the two mortgages are nearly identical, with the 12 percent mortgage at $97,343 and the 11-7/8 mortgage at $97,348. Starting in month 67, the balance on the 11-7/8 mortgage is lower than that on the 12 percent mortgage. This is because the same monthly payment eats away more principal on the 11-7/8 mortgage, and by month 67 it has eaten away enough to make up for the higher $500 balance from the extra 1/2 point up front.

In this example, the analysis suggests that if you stay in the house over 5-1/2 years, the low-rate (more up-front points) mortgage is better. Otherwise, the high-rate (less up-front points) mortgage is better. The spreadsheet analysis will yield different breakeven residence times for different combinations of interest rates and points.

Example: two-step mortgage alternative

Ready for another one? Try a "two-step" or "balloon" mortgage, with a 30-year term but the rate fixed for just 7 years. Let's suppose that the rate is 10.5 percent, and we enter this on line 2. Once again, let us "hardcode" the monthly payment (line 7) at $1028.61. Now we really start to eat away at the principal. Suppose that after 7 years the rate adjusts up to 15 percent from 10.5 percent. What will happen to us?

Looking at the balance after 7 years (84 payments), you should see $81,060.76. Put this on line 3 of the worksheet, put in a remaining term (line 1) of 23 years, and put in an interest rate (line 2) of 15.0 percent. The new monthly payment is $1047.22, not much more than the $1028.61 on the fixed-rate mortgage. Even in a relatively adverse scenario for the balloon mortgage, with interest rates up sharply, the balloon works out all right. In other scenarios (say, we sell the house before 7 years; or interest rates do not rise as steeply), the balloon works out better.

Things to Remember

Here are some thoughts to keep in mind as you analyze alternative mortgage programs:

fmmortwork