Why use a worksheet? |
| |
| In general, you will find 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. Choosing the wrong mortgage can be one of the biggest mistakes made by first-time homebuyers. |
| |
| Is there a software package 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 analysis 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?. |
| |
| 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 you can use to start to do some economic analysis to compare mortgages. The first seven lines set the basis for the analysis and 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. Note: Payments 4 through 359 are not shown. |
| |
Worksheet Instructions |
| |
 |
| |
| Line |
Item |
Formula |
Example |
| 1 |
Term In Years |
Enter a Value |
30 |
| 2 |
Intrest Rate |
Enter a Value |
12% |
| 3 |
Initial Balance |
Enter a Value |
$100,000.00 |
| 4 |
Term In Months |
12*(Line 1) |
360 |
| 5 |
Monthly Decimal Rate |
(Line 2)/12 |
0.01 |
| 6 |
Exponential Factor |
(1+Line 5)^(Line 4) |
35.95 |
| 7 |
Monthly Payment |
(Line3)*(Line5)*(Line6)/(Line6-1.0) |
$1,028.61 |
| | | | |
| |
Balance After |
|
|
| 8 |
1 Payment |
(Line3)+(Line3)*(Line5)-(Line7) |
$99,971.39 |
| 9 |
2 Payments |
(Line8)+(Line8)*(Line5)-(Line7) |
$99,942.49 |
| 10 |
3 Payments |
(Line9)+(Line9)*(Line5)-(Line7) |
&99,913.30 |
| . |
. |
. |
. |
| . |
. |
. |
. |
| . |
. |
. |
. |
| 367 |
367 Payments |
(Line366)+(Line366)*(Line5)-(Line7) |
$0.00 |
|
| |
|
| |
Example: Rates vs. Points |
| |
| Now, let's compare some alternative mortgages. Suppose another lender offers a mortgage of 11-7/8% but with an additional 1/2 point up front. How would this work? Assuming you are using a spreadsheet, you set up another column to try this alternative. |
| |
| Let's assume you cannot come up with additional money out of thin air, so you finance the 1/2 point (0.5 %, or $500 on a $100,000 loan balance). This makes your initial balance (line 3) $100,500. The mortgage rate is 11.875%, to be entered on line 2. |
| |
| Next, force (hardcode) your monthly payment (line 7) to be the same as on the 12% mortgage, which is $1028.61. The idea here is to have the same cash flow pattern as the 12% 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% 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% 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 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 things to keep in mind as you analyze alternative mortgage programs: |
| |
- Mortgage rates and points may not tell the full story. Check into fees and closing costs as well.
- For instance, suppose one lender is going to charge $500 more in fees than another lender on a $100,000 loan. This is equivalent to half a point. Add the extra charges onto the loan balance in analyzing the lender's program.
- Consider various scenarios for interest rate movements and for how long you stay in the same house. It may be best to look at three interest rate scenarios: rates stay constant; rates up 3% after two years; and rates down 2% after two years. Also factor in two mobility scenarios: move after 4 years; move after 12 years. Combining these gives a total of six scenarios.
- If you have an adjustable rate mortgage (ARM), your rate typically will go up even in the scenario where market rates stay constant. This is because lenders typically offer "teaser" rates on ARMs below the fully indexed rates. This means in a scenario where interest rates rise by 3% after two years, the rate on your ARM is likely to be more than 3% higher than the initial rate.
- In a scenario where rates fall by 2% and you stay in the house, you probably would refinance a fixed-rate loan at a lower rate.
- Your income probably will increase over time, so a mortgage with a lower monthly payment for three years and a higher monthly payment thereafter may be a good fit.
- Taking interest cost savings and plowing them into equity (as in the balloon example above) can be very effective at reducing the burden of a mortgage.
|
