How to Calculate a Loan or Mortgage with Excel
After last week’s post about the budget template, it made me think of another template I love that comes as part of Microsoft Excel: a loan or mortgage calculator, with an amortization table. I created a little demo video showing you how to find it and explaining the basics of how it works.
I found this template after complaining to my friend, Ben, about needing a tool that would help me figure out how long it would take to pay off my mortgage. Near the beginning of the mortgage, I paid an extra $30/month towards principal, then later started paying something more like $70. There were also a few random cases where I had a freelance job or got a tax return and would put that money towards the mortgage principal. There weren’t any loan calculators out there that provided a simple way to accommodate those variations in additional principal payments. My friend suggested I make one in Excel. When I went to do that, I discovered there was a template that did exactly what I wanted. Thanks, Ben!
If you have a loan or mortgage, you really need to try this out. I find it highly motivating for paying off debt, because it makes it easy to see exactly what will happen if you apply different amounts. The video explains it all.
Feel free to share your two bits if you are aware of any enhancements or even of other templates you have found useful.
Subscribe to RSS
I'm Michael Crowther, and I'm passionate about sharing the peace of mind that comes from budgeting, saving (including debt elimination), and investing.
February 12th, 2009 at 1:56 pm
Nice, especially since we’re considering a home. I wonder if these templates will work in OpenOffice, which I’ve switched to. Also, where can I look up the average interest rate at a given point in time?
February 12th, 2009 at 11:13 pm
That’s a good question about OpenOffice. I’ll have to check. Regarding interest rates, I usually start at Bankrate.com
February 12th, 2009 at 11:24 pm
It works in OpenOffice. It was simpler than I expected. I just “unprotected” the Excel template, copied it all, and pasted it into OpoenOffice’s Calc. It appears to work exactly the same.
December 30th, 2009 at 9:08 am
Thank you so much! Thanks for posting on ehow and leading me to your site. This is exactly the tool I have been looking for!
December 30th, 2009 at 6:58 pm
Connie, thanks for letting me know! I had that same experience and know others who did. I’m glad you found what you needed.