Amortization Schedule


This article may contain affiliate links. I will make a small commission if you make a purchase through one of these links, at no extra cost to you. This is how I hope to keep the website up and running for years to come! Please read my disclosure for more info.

The world of personal finance is flooded with beautiful and sometimes (often) complex worksheets – which assume a certain level of expertise in using Microsoft Excel or its equals. This creates a lot of room for error for those less experienced with these kinds of programs and with that a serious bias in who has appropriate access to these ((un)necessary) tools. This is why, instead of creating just another version of the same complicated worksheets – I provide easy-to-use worksheets with limited access to prevent unnecessary (mistaken) modifications.

With this being said, hoping that every sheet can be used without much explanation, I do feel the need to provide access to some sort of manual for each of my worksheets for those still uncomfortable with using them. If after all this, you still have questions or missing something – reach out!

Last side note: I avoid the use of currency, to make any sheet applicable to your local situation – this does mean that you should try to avoid mixing different currencies into one worksheet though! I would love to think Rp 15,000 is the same as € 15,000 – but sadly this is not the case!

“Real estate cannot be lost or stolen, nor can it be carried away. Purchased with common sense, paid for in full, and managed with reasonable care, it is about the safest investment in the world.”

– Franklin D. Roosevelt

The worksheet – Net Worth Calculator

I spent a lot of my early time on this website explaining the basics of mortgages, as this might be one of the biggest financial decision one can make. A decision that can set you up for a net worth increase while you’re asleep – or put you in a life of worry over paying your monthly bills for years to come. While it might be a discussion on whether or not it is better to buy than rent, it is good to know the numbers associated with your situation to make an informed decision.

For this reason, I build a calculator to create your own personal (adaptive) amortization schedule. Have a look below. As with all my worksheets, golden cells are those that need to be filled by the user.

The introduction and reset button

As this is a worksheet with a multitude of options, it already included a small instruction inside the document on the right. Important here is that there is also a button included that will reset the worksheet back to the baseline setting, deleting all your input in the process.

The input – Mortgage details

In this area, one can put in the information that was valid at the starting time of the mortgage. Choosing the mortgage type (a dropdown menu with level payment or straight-line), this sheet does not take into consideration interest-only mortgages as you’re not really paying off your mortgage (equity builds only with appreciation of the property). If there is great demand, this can be added in a later build (let me know). Add the interest rate that the mortgage started with. Both the mortgage type and the interest rate can be altered on a month to month basis below, so don’t worry if you’ve refinanced down the road.

Continuing, you can add the total loan amount, and the down payment (I’d put 0 if not applicable). The mortgage term, default assumes 30 years. The date the mortgage started and the day of the month payments are due

The amortization schedule

At the bottom of the sheet, following the input given in the previous section, an amortization schedule begins to form. In this schedule, you’ll have a few options and I’ll guide you through the table from left to right. On the far-right, the mortgage type is added, for the first month it looks at your input given in the previous section. After this, it will assume this stays the same – however, you have the option to pick another option every month (in the example given, I’ve altered a level payment mortgage to a straight-line mortgage after 10 years). The same is true for the next column, the interest rate. If at any point you’ve changed it incorrectly, or you’re not sure, just enter the option you want to have.

Next two columns tell you in which year and month you are in your amortization schedule (max. 30 years or 360 months).

The following section is about the monthly payments, the minimum payments, which cannot be altered as they are based on the input given previously. Any extra payments you wish to do, in this example 200 was added, this is repeated until you put it back to 0 (or another amount). The total monthly payment, which is the addition of the minimum – and the extra payment.

After that, two columns about the interest payments, the interest paid for that month (with a red bar showing how this is changing over time), and the cumulative interest paid (with a golden, increasing, bar showing the progress). The next column accompanies the interest paid per month and this is the principal paid per month (the amount that is reducing your loan).

The next column tells you about the date this payment occurs, with greyed out dates being in the past, the green date being the current month and the white months still in the future. The last column tells you the status of the total debt remaining in that particular month, assuming that the previous columns have been filled out properly. This column can be altered, as the extra payments, when done on another date then the date of payment, can change some details (pennies) in the principal/interest payment balance. By altering the current debt, you can bring the amortization schedule back in line (but the impact is minimal).

Side note: inside the worksheet is the option to collapse this amortization schedule on a yearly basis. This has however been blocked in order to prevent mistakes to happen with the less experienced user – this might alter in the future, and therefore has not been removed. Reach out to me if you’re interested.

The outcome – Current situation

Back on top, after changing extra mortgage payments, or other changes in the amortization schedule (if applicable), you’ll find your current mortgage situation based on the current date (assuming no missed payments, which might be added by adding negative ‘extra payments’ in the previous section).

On top, you’ll find how much principal has been paid to date, and how much remains and how this translated in a percentage of your mortgage being paid off.

At the bottom, you’ll find your current equity situation, where you can add the home value at the time of purchase and the home value currently – which will tell you how much equity has been build, combining the principal paid and the appreciation of the home value. This leads to the percentage of the home that is current owned, which lead into the next section.

The outcome – Graphical summary

Visualizing what part of the home you live in is actually yours, that’s what the purpose of the equity thermometer is. As you get higher and higher the colors will go from red to green – changing every 20%. Does this mean any kind of equity is worse than the other? No! The first 20% still reddish as this is the limit for a reasonable down payment in most parts of the world, including the U.S. Although definitely not a requirement before purchasing a home everywhere.

Have any suggestions for improvement or things that you feel are missing? Let me know in the comments below!

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.