Excel Amortization Table

I recently purchased a new car - a 2006 manual Saturn Ion. As with any good car purchase I had to take out a hefty auto loan for most of the car's worth. Thanks to a large amount of student loans and a short work history, the interest on the car loan was quite bad. I began to wonder if and how I could pay less interest on the life of the loan.

There are two main options I decided to explore. First, I wondered how much increased payments would affect the life, and interest, of the loan. Second, I wanted to look at the amount I would save by refinancing the car after a year under a lower interest rate. I was also interested in the gap of my loan (the difference between the trade-in value and loan principle), which would also be the amount I would owe the bank if I totaled my new car.

The only numbers I had to do a bit of research on was the rate of depreciation and the value of my car. I did some guess and check for the depreciation, but later found out that twenty percent is pretty standard for vehicles. Also, I based all of the rows under the data tab on the life of the loan, not when the principle drops below zero (in case of refinancing or increased payments).

When I had finished the sheet, not only could I view the remaining loan amount due and experiment with different payback options, but I could also easily see the value of my car against the loan principle. I also learned that if I add about a hundred dollars to my loan payment, the life of my loan would be decreased by over two years, saving me a ton of money in interest.

The only parts of the spreadsheet that need to be tweaked for different loans are located on the variables page. If you care about the graph, you can also play with the range on the summary page as well. Otherwise, feel free to download and play around with my fairly rough but somewhat-dynamic amortization sheet!