Jelle Druyts

If anybody would be interested, I've recently put together an Excel sheet that calculates the payment table ("aflossingstabel") for a loan, i.e. for each month it shows you how much you need to pay, what the interest is, what the remaining capital is, ...

I've only modeled two options (that were relevant to me): fixed monthly payments ("vaste mensualiteit") - where you pay the same consant amount every month - and fixed capital ("vaste kapitaalaflossing") payments - where you pay off a fixed amount of capital but a variable amount of interest (making it a decreasing loan).

When I showed this to my bank, they were actually pretty impressed so I figured somebody else might benefit from this And yes, this means we just bought a house, yay! But the examples in the Excel sheet and below are not ours, if you were wondering

Features:

• Calculates payment tables for loans up to 40 years
• Shows payment graphs up to 25 years (by default, you can enlarge this of course)
• Calculates how much of your total payments are actually interest payments (try not to weep when looking at this)
• Allows you to compare different loan options (amount, duration, interest rate), e.g. to compare different bank proposals

Download here: Loans.zip (108 KB).

Note that you can only open this in Excel 2007 since it uses some financial functions only available there. And although the calculations were very accurate (just a few cents deviation on the total amounts compared to the bank's proposals), it goes without saying that you use this at your own financial risk

Example payment table:

Example yearly graph for a fixed payment (constant) loan:

Example monthly graph for a fixed capital (decreasing) loan: