Taking out a loan can be a complicated procedure, but figuring out if it’s a good idea in the first place can be even more difficult.
How much will you be paying each month? How much will you pay in interest over the life of the loan? How long will it take to pay off?
Here’s how to get it all figured out.
The Amortization Schedule
In figuring out the details of your loan, you’re going to use an amortization schedule, which is just a fancy term for a financial spreadsheet that details your payments, remaining balance, and interest paid. In short, it gives you all the information you need to figure out if you can afford to take out an auto loan, a mortgage, or any other kind of loan. It’s also useful in looking into the future of a loan you already have.
Here’s an example of the input information you might need for a home mortgage calculator and the sort of details you’ll get back:
As you can see, this particular spreadsheet can take a lot of things into account, including extra payments, taxes, and home owners’ insurance. It can also give you a lot of useful figures, like your remaining balance at a specific time and the highest monthly payment you might be required to pay on a variable-interest loan.
At the bottom of the spreadsheet is the actual payment schedule, which shows the amount of the principal that you’ve paid, the total balance, and the interest due. It also allows you to add additional payments to pay off the loan faster, which will update the estimates at the top of the page.
If you’re not looking at a spreadsheet that’s specifically home-mortgage-focused, you won’t need to enter as much information. Here, using a simpler tool, I’ve entered a $50,000 loan at 6.2% interest on a 10-year repayment plan:
This simple calculator gives you the total amount that you’ll pay on the loan, as well as the amount of interest that you’ll pay.
Simple calculators like this don’t take capitalization into account, so you’ll need a more complex spreadsheet to deal with that (especially if you’re interested in paying off your student loans in the least amount of time possible).
Loan Calculators for Auto, Home, and More
There are tons of different loan calculators and amortization schedules out there, and running a quick search will get you thousands of results. To save you the trouble of sorting through them all, I’ve gone out and found some of the best free ones available.
After downloading this Vertex42 spreadsheet and entering your mortgage information, you’ll get the amortization schedule, information on total costs, and useful tax information so you can estimate how much tax you’ll have returned by claiming your mortgage interest on your tax return. There’s even a graph showing your balance over time.
You can include title transfer and registration fees, trade-in information, cash rebates, state sales tax, service contract, and other miscellaneous charges. There’s also an amortization schedule and a sheet for comparing the amount you’ll pay with different loans.
Vertex42’s general amortization calculator doesn’t have as many options as the mortgage or auto loan sheets, but if you just want a simple loan calculator for Excel, this sheet is perfect. You can adjust the payment frequency, compounding frequency, and other factors to see how they affect the loan over its lifetime, too.
This Google Sheets amortization schedule lets you enter basic information about a fixed-rate loan and will give you the payoff schedule and a running total of the interest you’ll have paid.
One of the best parts of this loan calculator is that whenever you make an extra payment, the table in the top-right of the sheet will tell you how much you’ll save in interest and how much faster you’ll be able to pay off the loan. This could be really useful if you’re not sure how much to budget for loan payments.
To use this in your own Google Sheets, hit the link above and then click the Use this template button at the top of the screen.
If you don’t need a full-fledged calculator, and you just want an idea of how much interest you’re going to pay on a simple(ish) loan, www.amortization-calc.com is a good place to go.
Enter four numbers (loan amount and term, interest rate, and your ZIP code), and you’ll get a simple schedule as well as the total amount you’ll pay.
In case you’re wondering, your ZIP code is used to serve you some ads about potential places you could get a loan. Just hit Amortization Schedule on the results page to skip all that.
Know What You’re Getting Into
Knowing if you’re able to afford an auto loan, a home mortgage, or any other kind of loan can be a huge help in deciding whether or not you should take it out. If peer-to-peer lending isn’t an option, and banks trying to sell you a loan aren’t giving you the answers you need, these Excel and Google Sheets templates should do the trick.
Once you have your loan details figured out, you can add them into your Excel budget or Google Drive financial management tools, and you can run all the calculations you need to figure out if you can afford a loan.
What do you use to get the calculations for your loans? Have you used these tools, or do you prefer others? Share your best tips below!