Loan Amortization Chart

• Version
• 17'478 Downloads
• 56 KB File Size
• January 4, 2022 Updated
• 5 Number of comments
• ★★★★★

  ★★★★★
  Rating

Loan amortization is a method of debt repayment in equal installments over a specific period of time where the payment amount consists of paying principal and interest with different amount every month. The payment amount of interest is gradually decreased and the payment amount of principal is gradually increased, and will be become zero amount by the end of payment period.

It is not easy to explain loan amortization concept to general people who don't understand finance. Usually they just think that the principal and interest are paid in the same amount every month, so when they plan to pay off their home loan before their loan period ended, they got shocked by the remaining principal they have to pay because of poor knowledge of loan concept.

You can find several free loan amortization tools in internet, either written in Excel, online, or written in other programmes, to be used for your personal purposes. I was writing three tools for calculating loan amortization that you can find in my previous posts. Perhaps you have downloaded them. And I think those tools are simple enough to help you calculating your mortgage payment.

This is another loan amortization tool that I tried to display it in dashboard style. I have finished it at the same time I finished F1 dashboard, but I haven't had spare time to upload it in my site. And this dashboard works well if you are using Microsoft Excel 2007 or 2010.

There are four tabs in this dashboard with description as follow :

Data Tab
This is the tab where you have to type your loan amortization parameters. Type your loan amount, bank interest rate (percent/year), payment period (in month) and payment start date in corresponding cells. And the other three tabs will automatically reveal your loan amortization information based on your data.

Table Tab
This is the area where you can see the break down of your monthly payment. You will see how your principal payment gradually increased and interest payment decreased month by month. And you can see also how your total principal is decreased every month. And there is a scroll bar that you can use to scroll payment information from the first month until the end of payment period.

Chart Tab
This is a visualization of your payment per year (not per month). You can see that your interest payment (bar with pink color) is higher than principal payment (bar with dark red color) in the first half of your loan period. So, you will see that it is not economically wise to payoff your loan in the first half of your loan period. Those bar values are referred to the left side of the chart. And there is a balance amount that displayed with the blue line where the values referred to the right side of the chart.

Summary Tab
This is an area where you can see your monthly payment, the total amount of your monthly payment and the payoff date.

That's all guys. If you want to learn how to make this dashboard, just unhide the "dummy" sheet. But, you have to check the grid and the heading option in view menu, and change the font color to make it visible.