Loan Amortization Spreadsheet

Loan Amortization Spreadsheet

Price: Forever Free!
File Type(s): Excel Spreadsheet Template (.xlt);
OpenOffice Spreadsheet (.ods)
File Size: 114kb (ZIP file)
Last Update: February, 2010
Current Version: 4.1
Macros: None.
  • Pinterest

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:

Loan Amortization Worksheet - Click to Enlarge

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

Download Spreadsheet!

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:

Quick Payment Calculator

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:

Loan Amortization Worksheet - Click to Enlarge

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).

Required User Inputs

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.

Enter 'One-Time' Additional Payments in Column H

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!

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

Add to My Yahoo!

Make a Donation
If you're a user of my Excel spreadsheets and feel that they're worthy of a small donation ... well, that's extremely cool. And I'm truly honored that you'd think so.

The following button will allow you to make a donation (any amount) via Paypal. I would ask, though, that if you're someone who's in substantial debt, and working like crazy to get out, that you please refrain from making a donation to IYM.

Instead, use your intended donation amount as an extra contribution toward your highest-interest debt ... and then drop me an email to tell about how my site or my spreadsheets have helped in some way.

That, I assure you, is payment enough for me.

As always, I am truly grateful for your kindness and continued support!