PDA

View Full Version : mortgage loan - calculating loan payoff schedule (extra principal payments)


Arnold Winkelried
04-09-2008, 11:10 AM
I've been playing around with a free morgage loan calculator I've found ( http://beaveramb.org/MumboWare/FinancesOSX/FinancesOSX_Help/index.html ) and I'm not sure I am getting the right numbers. Perhaps someone can explain to me how this is supposed to work.

Sample scenario (not the real numbers):

My mortgage loan was originally for 30 years (360 months), loan amount 150,000, interest rate 5.5%

Right now I still owe 130,000, 23 years left on the loan

Suppose I start paying an extra $200 towards the principal, starting this month. I want to find out how many months it will take me to pay off the loan.

In a loan mortgage calculator, if I type in (scenario 1)
loan amount 130,000
Months: 23 * 12
Interest: 5.5%
Payment: current + $200
will I get an accurate answer?

Or do I have to enter this (scenario 2)
loan amount 150,000
Months: 30 * 12
Interest: 5.5%
Payment: no extra for first 7 * 12 months, $200 extra towards principal starting at month 85

I was hoping the first scenario would give me the correct answer but the numbers don't look right. On the other hand, the free mortgage calculator I downloaded doesn't seem to have an option to handle the second scenario.

P.S. If anyone can point me towards a website that would explain this, that would be great. Thank you.

beowulff
04-09-2008, 11:15 AM
I just went through this myself, and I found this very nice spreadsheet: http://www.mtgprofessor.com/spreadsheets.htm
(you probably want this one: Extra Payments on Monthly Payment Fixed-Rate Mortgages)

Gus Gusterson
04-09-2008, 11:18 AM
Karl's Mortgage Calculator (http://www.jeacle.ie/mortgage/) can handle the situation you're trying to calculate. You enter the original term, balance, and rate of the loan. Then you can enter the prepayment amount along with the point in the term when you start prepaying. It calculates everything for you. It's written in Java so it requires the Java runtime, in case that is an issue for you.

ivylass
04-09-2008, 01:57 PM
That link says I can pay off my mortgage in six years by paying an extra $50 a month! Can that be right?

gazpacho
04-09-2008, 02:15 PM
ivylass I think that you have made a mistake somewhere. I tried basically your numbers and started extra $50 after 84 months and it pulled in the time to pay it off by about 5 years.

Cabbage
04-09-2008, 03:46 PM
Actually, I see absolutely no difference between your two scenarios.

I get that your original payments are $851.69 each (rounded up to the nearest cent).

After 7 years, your current balance is $133,223.17 (again rounded up to the nearest cent).

From now on your payments will be $1,051.69. This will pay off the loan after about 190 more payments.

Duckster
04-09-2008, 11:25 PM
I would run some test scenarios using round numbers with a calculator, a pencil and paper using the standard amortization formulas and compare the results against the online mortgage calculators. I just compared the downloadable Excel spreadsheets against the online Java program and got completely different results.

Caveat emptor. YMMV.

Arnold Winkelried
04-10-2008, 10:24 AM
Thanks guys, I will try your suggestions. Also I found out at my bank's website another java applet for mortgage calculations. I didn't have time to try any of this yesterday but today will be the day.

Duckster - I've found a standard formula here
http://mathforum.org/library/drmath/view/54641.html
and I can try it out too just to refresh my math skills.

Cabbage - did you mean you see no difference between the two scenarios I mentioned in the OP? I thought that the numbers came out different, but I will have to try again. Maybe I made a mistake.

Cabbage
04-10-2008, 01:07 PM
Cabbage - did you mean you see no difference between the two scenarios I mentioned in the OP? I thought that the numbers came out different, but I will have to try again. Maybe I made a mistake.
That's right. Actually, I do see one difference: The first scenario has the current balance (after 7 years) as $130,000, while the actual balance (rounded up to the nearest cent) after 7 years is $133,223.17. (I figured you rounded it off to $130,000 for convenience, so I ignored that difference).

But aside from that rounding difference, I don't see any difference. Scenario 2 covers more, but after 7 years the current balance matches scenario 1.

accidentalyuppie
04-10-2008, 03:29 PM
A 5.5% mortgage is a pretty good deal, my inclination would be to take the extra $200.00 a month and put it into an alternative investment.....you shouldn't have trouble finding something safe.....that pays off at more than 5.5%. Unless you have a specific need to pay off the house this would be a higher-yielding strategy.

ultrafilter
04-10-2008, 03:40 PM
A 5.5% mortgage is a pretty good deal, my inclination would be to take the extra $200.00 a month and put it into an alternative investment.....you shouldn't have trouble finding something safe.....that pays off at more than 5.5%. Unless you have a specific need to pay off the house this would be a higher-yielding strategy.

Paying off a mortgage is a risk-free investment. You can find something safe that pays more, but nothing that guarantees a higher rate with no risk of loss.

Sunspace
04-10-2008, 04:07 PM
A 5.5% mortgage is a pretty good deal, my inclination would be to take the extra $200.00 a month and put it into an alternative investment.....you shouldn't have trouble finding something safe.....that pays off at more than 5.5%. Unless you have a specific need to pay off the house this would be a higher-yielding strategy.I dunno... since so much of the beginning payments of a mortgage go to interest, anything extra that cuts down the principal early will have huge follow-on effects in reducing interest payments later. I suspect you'd have to find a very productive investment to match the avoided payments.

Arnold Winkelried
04-10-2008, 04:31 PM
That's right. Actually, I do see one difference: The first scenario has the current balance (after 7 years) as $130,000, while the actual balance (rounded up to the nearest cent) after 7 years is $133,223.17. (I figured you rounded it off to $130,000 for convenience, so I ignored that difference).

But aside from that rounding difference, I don't see any difference. Scenario 2 covers more, but after 7 years the current balance matches scenario 1.I'll check that and see. What complicates the matter is that I made extra principal payments in the past but they were not regularly made every month. Also that our interest rate was renegotiated after the first three years to a lower rate. So, from what you say, I could just pretend that I am starting a brand-new 23-year-loan with a $130,000 principal and 5.5% interest rate, and the numbers that are coming out should be the same as the "real" numbers?

beowulff
04-10-2008, 04:37 PM
I'll check that and see. What complicates the matter is that I made extra principal payments in the past but they were not regularly made every month. Also that our interest rate was renegotiated after the first three years to a lower rate. So, from what you say, I could just pretend that I am starting a brand-new 23-year-loan with a $130,000 principal and 5.5% interest rate, and the numbers that are coming out should be the same as the "real" numbers?
Use the spreadsheet I suggested. It allows arbitrary additional payments to be calculated.

Santo Rugger
04-10-2008, 04:38 PM
<snip> I could just pretend that I am starting a brand-new 23-year-loan with a $130,000 principal and 5.5% interest rate, and the numbers that are coming out should be the same as the "real" numbers?

Yep. Instead of thinking of them as not being "real", think of them as being "updated".

Arnold Winkelried
04-10-2008, 06:24 PM
Use the spreadsheet I suggested. It allows arbitrary additional payments to be calculated.Yes, beowulff, thanks for that link. But the thing is, I'm not really interested in the past, but more what's going to happen in the future starting now. So if I can start with the numbers as they are now (see my post #13 in this thread) then I wouldn't need to go back from the beginning and dig through my records to find when (and for how much) I made additional principal payments in the past, while also accounting for new interest rate that started in year 3 of my loan.

beowulff
04-10-2008, 06:29 PM
Yes, beowulff, thanks for that link. But the thing is, I'm not really interested in the past, but more what's going to happen in the future starting now. So if I can start with the numbers as they are now (see my post #13 in this thread) then I wouldn't need to go back from the beginning and dig through my records to find when (and for how much) I made additional principal payments in the past, while also accounting for new interest rate that started in year 3 of my loan.
Oh.
I would just use the formula that the spreadsheet uses, and plug your current principle, interest rate, and remaining periods in, and let it figure out the rest.

ultrafilter
04-10-2008, 07:10 PM
You can just pretend that the last seven years never happened, and that you have a 23-year loan with the principal and rate that you mentioned. If you go through and work out the accumulated value of the first seven years of payments, you'll find that all of your answers end up being the same, but you do a lot more work.

Arnold Winkelried
04-11-2008, 01:01 PM
Thanks everyone. After getting the advice in this thread I was able to prove to myself that the new loan that this guy was trying to get me to sign up for would not be beneficial - I would actually end up paying more with his loan than with the loan I currently have.