Variable Loan Amortization Spreadsheet
|File Type(s):||Excel 2007 Spreadsheet (.xlsx); Open Office Calc Spreadsheet (.ods)|
|File Size:||69kb (ZIP file)|
|Last Update:||February, 2012|
Now that I've had a host of people ask me for some sort of spreadsheet to track loans with variable terms (like a changing interest rate, or perhaps non-equal payments), I've come up with something that I think should work nicely for most cases. I call this my "Variable Loan Amortization" spreadsheet ... or "VLA" for short.
The VLA spreadsheet is pretty simple, really, and shows up like this:
There's more about the purpose of the VLA spreadsheet later on this page. For now, you can grab a copy below:
There's only one worksheet in the spreadsheet itself, and what few instructions there are, are right there.
When you extract the ZIP file (tutorial), you'll actually find two separate spreadsheets. One is for Excel 2007 and later (.xlsx). The other version is for OpenOffice Calc (.ods).
Please use the spreadsheet that's appropriate for the program you've got.
Working with Variable Loan Amortizations
I'd like to think that not much about this spreadsheet needs explaining — it ought to be pretty straightforward, after all, if you have a basic knowledge of loan components.
The difference between this spreadsheet and my basic loan amortization worksheet is that the VLA allows for the interest rate, payment, and amount borrowed to change in any month, rather than remaining fixed for the life of the loan.
In my experience, loans between family members and friends often work out this way: Interest rates change, payments change, and sometimes even the amount borrowed changes. Most loan-amortization spreadsheets don't handle this very well, if at all. I'd like to think that the VLA spreadsheet bridges a gap in this area!
Will It Be Exact?
If you're hoping that this spreadsheet will give you the same figures you'd get from your bank's amortization software, well, think again. Whether you make payments at the beginning or the end of the month makes a difference, for instance, in how much interest gets tacked on. But, in most cases, this should track your loan pretty closely.
If you're using it to track a family or personal loan, well, it ought to serve that purpose nicely.
This 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 work perfectly on everyone's computer.
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!