Tuesday, January 29, 2008


To most people, the amortization chart of a loan is like reading Greek, in other words, it is completely foreign. Pick a given period and it tells you how the interest and principal of the loan are broken up and applied. It is always front-loaded with interest (more of the interest is paid up front instead of spread over time) to insure that banks make their money off the loan. What I bet most people do not know, though, is they can reproduce an entire amortization chart for a loan as long as they know the period of the loan, the interest rate, and the amount the total loan is for. By putting those three items into an Excel workbook with the financial formulas of "ipmt" and "ppmt", each of which also require the reference of which period (that is, which month is being calculated), an amortization chart can be generated. Listing out the entire 360 months of a loan might seem time consuming, but it is quite easy as long as a person understands how to make the cells copy and paste while still reference the necessary information (the ipmt and ppmt would need to follow the number of the period, for instance). I know all of that explanation sounds extremely complicated, especially to someone unfamiliar with Excel. So why would I share how to create an amortization chart in this space? By having a complete amortization chart, a person can know what an additional payment to principal will do to reduce the interest of a loan going forward. What do I mean? Here is an example:

If I have always paid only the required payment, but now want to pay an extra $100 a month, I can tell how much benefit I get from that extra $100 by looking down the amortization column for "principal payment" below my current period to where $100 more gets me. In other words, the extra money is somewhat like "skipping ahead" in my loan amortization - which is what people mean when they say they have knocked a certain number of months or years off their loan by paying extra.

I have built a very helpful Excel workbook that has a lot of these amortization charts in it that allow me to determine what an increased amount of interest will do to my payment, or what an increased amount of payment will do to move me forward in my loan. Using these charts has helped me plan my extra payments and understand what I am accomplishing with an extra payment or two a year.

-- Robert


Julie Pippert said...

This is interesting to me. Are you going to talk more about it? In more detail?

Also, Dave Ramsey's program is okay. It sounds great at first. But it's like a diet. That's why most people don't sustain it long-haul.

I think a more weight watchers like program works much better. It addresses the mitigating issues and is more a lifestyle change, so more sustainable.

Does that make any sense?

I think I nattered on at great length one time a while back about economics and weight loss and the similarity therein.

Robert said...

If a person fails to change their lifestyle in eliminating debt, then debt will not go away. I think Dave Ramsey is a proponent of lifestyle change, as you suggest. He certainly seems to argue for those sorts of changes when he talks on his radio show. There is definitely a similarity between weight loss and debt reduction. Both problems tend to come from problems with gluttony (or at least lack of restraint).

I will be happy to talk in more detail about my amortization chart. I'd post a downloadable copy of the file on here if I knew how. I might post one on my website for people to download if they want. I just finished building one tonight that works nicely to determine what a given payment amount will do to reduce total interest and time in a loan. Let me know if you're interested.

Julie Pippert said...

I like Dave Ramsey in theory. It got too complicated for me to apply all of his techniques. My church did a great finances for families and used Dave Ramsey's program. So I did a buffet thing. KWIM?

Robert said...

I can understand it being complicated to implement all of Dave Ramsey's theories. Starting with the debt snowball, though, can really help eliminate debt. Starting with the smallest amounts and paying them off, and then the next and so on builds the snowball by reducing the number of minimum payments and allowing more dollars to focus on each successive debt.

Anything that gets me out of debt faster is better in my mind. I, fortunately, have never owed that many debts to have to worry about multiple minimum payments and rapidly building debt. But I'm not against sharing good advice just because I haven't needed it.