is there somewhere that explains that concept a little clearer then wikipedia? also it looks like the numbers are a little off as i had made a type o on the interest rate of the 20 year.
Don't know if it will be clearer, but see below for a different way to look at it. Won't change the answer of which is better, but different people see things better in different ways.
right now a 20 year mortgage will cost us 980 a month(3.635% interest) for P&I 30y is 798 (4.0%int)
The financed amount is $167,050 - close enough?
Assume you have $980.45/month. You can
1) use that for 20 years to pay the 20 year mortgage (according to the Excel PMT function), then invest that amount each month for 10 years, or
2) use $797.52/mo for 30 years to pay the 30 year mortgage (again according to the Excel PMT function), while investing ($980.45 - $797.52 = $182.93) per month for those 30 years.
At the end of 30 years you would have
a) paid the mortgage, and
b) have some investment balance
Because the mortgage is paid either way, you just need to look at the investment balance at the end of the 30 years.
To get the ending value of a monthly investment after some number of months at some interest rate, you want the "Future value of an annuity" described in the wikipedia article. This is also what Excel's FV function will give you.
In Excel,
FV(4%/12,
10*12,-980.45) gives $144,371, and
FV(4%/12,
30*12,-182.93) gives $126,962, so at a 4% market return the 20 year mortgage is better.
Using Jack's 7% assumption
FV(7%/12,
10*12,-980.45) gives $169,701, and
FV(7%/12,
30*12,-182.93) gives $223,168, so at a 7% market return the 30 year mortgage is better, as Jack found.
At 5% market return, the 20 and 30 year mortgages are ~equal. Thus, as Jack said, YMMV.
You could copy the table below, paste into spreadsheet cell A1, then vary the inputs to your heart's content - good luck!
Loan amount | 167050 | =B1 |
Annual interest rate | 0.03635 | 0.04 |
Loan period in years | 20 | 30 |
Number of payments per year | 12 | =B4 |
Scheduled payment | =PMT(B2/B4,B3*B4,-B1) | =PMT(C2/C4,C3*C4,-C1) |
Payment difference | =B5-C5 | |
Number of years invested | =C3-B3 | =C3 |
Market return | 0.05 | =B8 |
Investment balance at end | =FV(B8/12,B7*12,-B5) | =FV(C8/12,C7*12,-B6) |
Advantage of the first choice | =B9-C9 | |