Over the years, a lot of people have asked for calculator features like occasional extra principal reduction payments, tracking fees or late charges, summary of interest over a fiscal year. My pat response is “this kind of thing needs a spreadsheet or special-purpose software.”
Well, I’ve taken a little time to work up a basic amortization spreadsheet. It doesn’t have all the calculation options that the web calculator provides (it only calculates the payment, for example), but the amortization schedule it produces will allow one to track extra payments and fees, to include payments for insurance and taxes, and it provides a nice fiscal year summary of interest paid.
It took a few days of playing around with Excel to get it working mostly right, and it’s free for you to download and try, if you’re into that kind of thing. Maybe it will give you some ideas about how to build your own custom spreadsheet. But of course, use at your own risk! Please don’t base any high-finance decisions on the spreadsheet’s results until you’ve verified that it’s doing everything correctly. Like the web calculator, I consider this a planning tool only!
If you want to give it a shot, here’s a link to the spreadsheet. Enjoy!
Hi, Jack. The details of making such a thing work would have to be spelled out ahead of time, as there are any number of ways one might legitimately resolve the unpaid balance. I don’t really know how lending entities might choose to work out these sorts of problems since I’m not in the industry. It is not a true amortization if there is not a fixed termination to the payback schedule, and under these conditions, the spreadsheet can only be a starting point. As it’s a very different problem you are trying to solve, I don’t think I can be of much assistance beyond the info that’s already provided.
Best wishes!
Hi Bret,
I, too, would like to join the others in saying “thankyou” for creating such a useful tool and allowing everyone to use it for free!
I notice on your current simple interest spread sheet, a balloon payment is calculated if the full payment was not made somewhere along the line.
I have been searching the web for a compound interest loan calculator that will allow the payment amount to be variable. If a payment is skipped or additional principal is paid, the calculator will recalculate the future payments so there will not be a balloon payment on the last scheduled payment date.
Is this something you could help me with?
Thank you!
Hi, Cathi. Yes, there is an amortization calculator which can generate an amortization schedule for you. If you click “Calculator” in the blog menu headings, the first link is “Amortization Calculator”, which takes you right there.
Is there really an amortization schedule on your blog? I’ve searched several times, looked at most/all of the categories, and do not find the amortization schedule. If there is one, would you please tell me how to access it? Thanks for any help you can give! /Cathi
Hi Bret,
Just getting back to this project. A quick follow-up, it worked great on a Windows computer, but it stops at payment 81 on a Mac computer. I’m guessing it’s just a compatibility issue.
Lisa
Hi back to ya Bret! Unfortunately I hadn’t slept in about 30 hours when I was trying to work on this. I finished entering the information out to payment number 104 and printed the spreadsheet. I’m going to go back over it with re-energized brain cells at a later time. I’ll let you know if it was operator error on my part. 🙂
Thanks again, your quick response is as fantastic as your website! It’s nice to have people in the world like you.
Take care! Lisa
I have a question/comment, but first wanted to say “WOW!” Your site is amazing! I can barely spell Excel, let alone create anything in it. So, I would like to echo the comments above … thank you!
I saved the amortization calculator and I’m trying to figure “money owed” retrospectively, 104 months back. After about the 50th entry, it seems whatever formula is being used stops. This is very complicated (to me anyway) because the person that owed the money paid very sporadically, sometimes missing 9 out of 12 months of payments or an entire year.
Is there a way for me to continue the formula? Any suggestions on how to calculate this accurately would be
appreciated. Thanks, Lisa
Hi, Lisa. I’m afraid I can’t offer much help with the information you’ve provided. The spreadsheet has a lot of formulae buried in the cells, so my guess is that somewhere along the way, one of those cells got replaced with a value that broke the calculations. Or perhaps there was a flaw in the spreadsheet that I haven’t caught yet. Unfortunately, I can’t tell you how to fix it since I don’t really know what’s broken. But since you know the row where things seem to stop working, poke around the surrounding cells to see if you can identify any disruption in the patterns. You may need to find yourself a local Excel guru to help you with this.
Good luck!
Hi, Gary. Thanks for the compliments! Flexibility was indeed what I wanted for my own use, so I’m happy that others have also found it helpful. Best wishes!
Thank you so much for developing this Amortization Calculator and accompanying schedule. It’s more user-friendly than others I’ve come across and extremely flexible in allowing you to calculate various amounts by leaving whatever amounts blank that you want it to calculate. I’ve used it both in my personal finances and at work to assist real estate clients.
Gary
Hi Bret,
I registered just so I can say thank you for all the time you spent creating your Amortization Calculator and spreadsheet and then giving them to everyone to use. I have used both of them and they have, and continue to, help me immensely. A very big THANK YOU again for all your hard work and generous spirit, Bret. Please take good care.
–Jerry
Thanks for taking the time to write, Jerry. I’m glad to know the calculator and spreadsheet have been useful.