My almighty spreadsheet

[cerberusss]

Early this year, I discovered the site and the forums, and started wondering what my mortgage actually looked like. I created a spreadsheet that shows:
- given a yearly payment towards the principal, how long I'll be in debt
- if I miss a payment, how much that would cost me in extra interest
- a nice graph showing the downward slope of my debt
- when the house will entirely be mine

When I was having trouble keeping up morale last week, a reader asked me: what would it cost to buy this expensive laptop you're eyeing?

Entered the costs into the spreadsheet and voilą: a 2000 euro laptop would mean an additional 1800 or so interest. Really happy to have these numbers ready to punch me in the face.

[MissStache]

On call, self-inflicted face punching!  I love it!

[AugsburgMustache]

Would you be so kind and make that spreadsheet available? Of course you should clear out any personal information but I think it would help a lot of folks to get started on calculation such complex matters.

Thanks in advance!

[cerberusss]

Too much of a hassle to create a new Google account. However, I got most by peeking how this guy did it:

http://www.mutilatethemortgage.com/2012/07/23/the-mortgage-planning-spreadsheet/

[AugsburgMustache]

Thanks for the link!

Did you create the spread sheet in Google Docs?

[marty998]

Just put it as an attachment to your post...reminds I started a thread in general a few months ago where everyone could share their spreadsheets

[cerberusss]

Good idea. Added the attachments to the topic opening!

[AugsburgMustache]

Thanks for uploading.

I am kind of stuck here and I can't find my error in thinking. In the spread sheet, the payments you make each year are substracted from the principal. But it seems that the intrest that accumulates is never considered in the payments. The principal is reduced by the yearly payments until it eventually reaches zero and the interest is listed but never considered in the calculations.

At least that's how I see the spread sheet. Do I miss something?

[marty998]

Yeah I'm having problem with interest too.

Try the loan repayments calc I uploaded into this thread in the first post:

https://forum.mrmoneymustache.com/welcome-to-the-forum/spreadsheet-collection/msg67067/#msg67067

I don't have any fancy graphs in mine though :) Just walls of numbers.

[cerberusss]

I am kind of stuck here and I can't find my error in thinking. In the spread sheet, the payments you make each year are substracted from the principal. But it seems that the intrest that accumulates is never considered in the payments. The principal is reduced by the yearly payments until it eventually reaches zero and the interest is listed but never considered in the calculations.

At least that's how I see the spread sheet. Do I miss something?

Thanks for catching that. I did a bit too much cleaning up. The saved interest should be snowballed back into the principal, I've corrected it.

[AugsburgMustache]

I'm still renting and therefore I am no mortgage expert, but here is how I see it:

The principal is 200K, 3% interest rate, I pay 6000 a year (for simplicity).

At the end of the first year I owe 206K (200K principal plus 3% interest 6K). So if I pay 6K to the mortgage, I still owe 200K. This would go on and on and I will only pay towards interest forever.

Yet in your sheet the principal decreases. Could somebody enlighten me please?

[cerberusss]

Thanks for looking so thoroughly at this. This is going wrong in a couple of places.

Yet in your sheet the principal decreases. Could somebody enlighten me please?

I assume in the spreadsheet that the interest is automatically deducted from your checkings account, and end-of-year, you move saved money towards the principal. Thus the principal is decreased. This ignores a lot of stuff like the interest you gain in your savings account and what have you.

Secondly, my mortgage consists of two parts, an interest-only part, which is obviously quite simple. The second part is a complicated (perhaps typically Dutch) construction where you save into a linked savings-account and only at the end of the 30-year (or whatever) period, fully pay off the loan. Thus you maximize the fiscal benefits.

I attempted to clean up my spreadsheet but in doing so, forgot that mine actually models the two parts.

I'm doubting whether to add this construction into the spreadsheet, because it's really very specific. I could adapt the spreadsheet to show a typical annuity-based mortgage, but for me personally, it's worthless so I won't. I could also adapt it to an interest-only spreadsheet, which I've done.

[AugsburgMustache]

OK I see, so the sheet is for a specific point of view on the mortgage and not intended to be an allround mortgage calculator. That clears up a lot. Thanks for the help!

[cerberusss]

Glad someone's getting some good use out of it! :-D

Sir, I take off my hat to you!

OK I see, so the sheet is for a specific point of view on the mortgage and not intended to be an allround mortgage calculator. That clears up a lot. Thanks for the help!

Yes. I provided it since you asked, but in all honesty I think people are much better off creating their own. You don't need to learn the formula for present/future value, if you set it up the way I did. Just simple calculations.

[AugsburgMustache]

I provided it since you asked, but in all honesty I think people are much better off creating their own.

That's exactly what I thought when I looked at various sheets. And it helps your understanding of the complex topic when you have to think up your own formulas. Thanks anyways!