Excel Templates | Excel Spreadsheets | Amortization Chart



Amortization Chart

Posted on July 25, 2011
Filed Under Finance | 6 Comments


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.

Amortization Chart 300x114 Amortization Chart

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.

xlsx Amortization Chart  Loan Amortization Chart V1.0 - Excel 2007/2010 (54.2 KiB, 6,662 hits)



Comments

6 Responses to “Amortization Chart”

  1. Mark on September 10th, 2011 12:13 am

    Can you help me with a couple of compounding formulas I need to create in an Excel spreadsheet?

    I can share the file with you from my .ftp site if you are willing.

    Thank You,

    Mark

  2. Kirk on February 3rd, 2012 1:41 pm

    Looks great I have been looking at excel dashboards and this will be a great learning tool.

    Thank you for all you do for us excelanauts

    Kirk

  3. Tony Ennenga on February 6th, 2012 8:13 pm

    Excellent resource for excel templates! Just make sure to watch for compounding periods that are different from the payment dates. For instance, revolving credit loans often compound interest daily but collect principal and interest payments monthly. The compounding differences can add up for large credit lines.

  4. Gourav on March 16th, 2012 2:50 am

    Excellent, thanks to this sheet I learnt to use Scroll bar inside the sheet.
    Thank you

  5. Amortization Chart | Excel Templates – amortization-chart on March 29th, 2012 1:40 am

    [...] this link: Amortization Chart | Excel Templates This entry is filed under [...]

  6. zoran on August 22nd, 2012 6:06 pm

    The best loan amortization template so far.
    An extra payment option (monthly) would really help.
    Thanks!

Leave a Reply