Loan Amortization Spreadsheet

I'm not sure the internet needs another loan-amortization spreadsheet ... but I'm gonna put one out there anyway!

For the financial newbies out there, a "loan amortization" is simply a breakdown of any loan's payments and balances over the life of the loan. In other words, an amortization shows you how much principle and interest you're paying, month by month, until the loan is retired.

My amortization schedule spreadsheet looks like this:

The Loan Amortization spreadsheet is a pretty simple concoction. (We all like simple, yes?). You can download a copy below:

The first worksheet in the spreadsheet file contains the instructions, but a few items ought to be covered here, too.

Spreadsheet Versions

When you extract the ZIP file (tutorial), you'll actually find two spreadsheets. One is for Excel (.xlt), and one is for OpenOffice (.ods).

Please use the spreadsheet that's appropriate for the program you have.

The "Quick Payment Calculator"

As noted above, the first worksheet in the file is the INTRO page, with instructions and such. The second worksheet is called the QUICK PAYMENT CALCULATOR (QPC), and looks thusly:

Not much to it, is there? Well, that's okay. It doesn't have to do much!

The QPC's one and only goal in life is to calculate the monthly payment of any fixed-rate, fixed-term loan. (Auto and fixed-rate home loans would qualify here, for example.) Just enter the loan's interest rate, term, and borrowed amount, and the worksheet returns the necessary monthly payment.

Note, however, that IYM stresses debt-free-ness. Just because it's this easy to calculate a loan's payment ... well, that doesn't mean you need to go out and get one!

Working with Loan Amortizations

Let me stress this: The amortization schedule built by this worksheet is intended for any fixed-rate, fixed-term loan or mortgage of up to 480 months (40 years) duration. It'd work for a credit-card balance, too, I suppose ... but only until (1) the card company changes the loan terms, or (2) you borrow more against the card or run up fees on it. In that case, you would likely be better off using my variable-loan amortization spreadsheet ... but it still won't give you exact figures which match your bank.

Let's take a quick look at the screenshot again. Namingly, let's go through the sample data:

The top left-hand corner is where you'll find the five required inputs. (Pictured below!) Enter your loan amount, its interest rate (APR), term (in months), and starting month (or date of first payment).

If you are making (or plan on making) additional contributions toward the loan's principle every month, enter the amount of this "Regular Add'l Contribution" in Cell F9.

This particular spreadsheet is set up to allow users to track the loan's amortization over time. If you decide to make any "One-Time Add'l Payments," you can enter these in Column H, in the amortization itself. Simply enter any extra payments on the row that corresponds to the month you're in.

Will It Be Exact?

Not bloody likely. Whether you make payments at the beginning or the end of the month makes a difference, for instance. But it should track your loan pretty closely!

Printable? Yes!

The amortization schedule should print nicely in landscape format. But because printers tend to be a bit testy with these sorts of things, I can't promise that this will be the case on everyone's machine.

Looking for a Turbo-Charged Version?

If you're looking for a spreadsheet to build a comprehensive debt-payoff plan, then you'll want to check out ExcelGeek's Rapid Payoff / Debt Snowball Calculator elsewhere on this site!

Questions? Comments? Contact Me!

As always, if you've got a question or issue, don't hesitate to drop me a line. I'll get back to you as soon as possible!

Want to know when I add to and update my spreadsheets? Subscribe to IYM's feed!