Loan Amortization Calculator Template

What is a loan amortization calculator?

Amortization is paying off a loan over time through incremental payments of principal and interest on a regular basis. Although amortization may have the word "amor" in it, very few people love it. One reason for this is that depending on the size of your loan, a significant amount of your early payments may go towards interest instead of the principal. Another reason amortized loans are unloved is because it can be difficult to calculate your payments.

A loan amortization calculator is a tool for solving that second problem. By inputting the terms of your loan, including the amount, interest rate, and repayment schedule, you can calculate the payment amounts for a fixed-rate loan. Whether you have a mortgage, a car loan, or a personal loan, using a loan amortization calculator will take the guesswork out of calculating your payments.

Did you know?

77% of American households are carrying debt. But far fewer American households are carrying a reusable water bottle, which is a shame, because it's more useful than debt. Don't forget to hydrate.

Famed 90's rapper MC Hammer eventually had to file for bankruptcy, which raises the question of whether he could have benefitted from an amortizing loan calculator. He probably never even considered changing the lyrics in his signature song from "Stop, Hammer Time" to "Stop, Amortize." The world will sadly never hear "2 Legit 2 Quit Paying Interest."

Fixed-rate loans can be easily calculated using this loan amortization calculator, while an Adjustable Rate Mortgage is more complicated and involves a lot of extra legwork to figure out how much you owe. In other words, such mortgages may cost you an ARM and a legwork.

By
Vertex42
Vertex42
2
Worksheets
Loan Amortization Calculator
Overview

Use this spreadsheet to create an amortization schedule for a fixed-rate loan. Edit the cells within the blue cell borders (Loan Amount, Term, Interest Rate, etc.), and watch the rest fill themselves in. You can also enter optional extra payments within the table to estimate the interest savings. Remember, paying down the principal is your pal.

How does it work?

Fill in the blue-bordered cells at the top of the spreadsheet with the terms of your loan:

Loan Amount is the entire principal of the loan. If you're calculating a loan that is already partially paid off, enter the remaining balance of your loan.

Annual Interest Rate, aka Annual Percentage Rate (APR), is the interest rate designated by the lender.

Term is the number of years, starting from today, over which you plan to pay back the loan. If you are calculating a loan that is already partially paid off, enter the remaining time on the loan. Decimals may be used as long as they divide evenly into your payment frequency.

First Payment Date is the date on which you will make the first payment on the loan. If you are calculating an existing loan, enter the date of your next payment.

Payment Frequency is how often you plan to make payments. Select this from the drop-down menu (monthly is common).

Compound Frequency is how often the interest is compounded. By default, this is set to automatically update to match your payment frequency, so you only need to change it manually if the two are different.

Rounding is enabled by default to round all values to the nearest cent, which most lenders will also do. If for some reason you do not wish to round to the nearest cent, you may disable this feature.

Once you have filled in all of this information, the loan amortization calculator will calculate your payments over the full term of your loan. A summary can be found to the right, and below you will find a full schedule of the dates and amounts of each payment, broken down into the amounts going towards Interest and Principal. Note that towards the beginning of your loan, more of your money may be paying for interest rather than the principal itself.

Extra Payments may be made towards the principal, and then entered in the Extra Payment column on the appropriate date. Your remaining Loan Balance will be adjusted accordingly -- and you'll save yourself some money in interest.

Do I need a loan amortization calculator?

Only if you have a loan or are considering taking one out. Otherwise, using a loan calculator with amortization is more of a theoretical exercise out of curiosity.

But if you do have a loan, or are considering signing up for one, it behooves you to understand exactly how much you will be paying. Because the amount of your repayment is certainly going to exceed the amount you borrowed, and understanding how much you will owe -- and when -- is important.

A loan amortization calculator will help you see if you can afford the monthly payments that new car is going to create. It will show you whether taking a personal loan to pay down your credit cards will save you money in interest. It will let you compare a 20-year mortgage with a 30-year mortgage to see if you can handle the higher monthly payments in exchange for keeping your final repayment lower. It will cook you breakfast.

Okay, it won't cook you breakfast. But by keeping your finances on track, it will make sure you can afford breakfast without having to take out another loan.

Who benefits from a loan amortization calculator?

Potential Borrowers

If you're considering any type of loan, whether it's an auto loan, mortgage, personal loan, or otherwise, you want to know what it will cost. You want to know what it will cost you in the short term, to see if you can afford the regular payments. You want to know what it will cost you in the long term, to see if it's worth it. And you want to be able to compare it to other options and see the numbers side by side. A loan amortization calculator lets you do all of that.

Anyone who wants to make an extra payment on their loan

Due to the way amortized loans are structured, a percentage of each payment goes towards interest rather than principal. The good news is that any extra payments you make go 100% towards principal, and reduce the interest you'll have to pay. This free spreadsheet will automatically calculate your payment reduction, to show you how one extra payment now may save you three payments later.

Excel Loan Amortization Schedule

by Vertex42

About This Excel Template

Of course, once you know you need a loan amortization calculator, the next question is, where's the best place to get one? Some people might suggest an Excel amortization spreadsheet because they're used to doing everything in Excel. And you absolutely can search for an amortization schedule in Excel, if you're working in Excel spreadsheets 24/7 anyway and already have it running.

But if you're more likely to have a web browser open than Excel files, you might find it more convenient to use the free online amortization calculator here on Spreadsheet.com. Think of us as a convenient amortization schedule Excel alternative.

More templates like this one
Asset Management
Asset Management provides a central place to keep track of computers, office equipment, and anything else that is owned or maintained by the company. Link assets to rows in the Vendors worksheet to track where each asset was purchased from. Monitor total asset spend and amount written off. Link assets to employee rows in a different workbook to track who is using each item.
Asset Management
Asset Management provides a central place to keep track of computers, office equipment, and anything else that is owned or maintained by the company. Link assets to rows in the Vendors worksheet to track where each asset was purchased from. Monitor total asset spend and amount written off. Link assets to employee rows in a different workbook to track who is using each item.
Inventory Management
Track and manage product inventory, sales, fulfillment centers, and vendor relationships. Inventory management is a system for sourcing, storing, and selling products in inventory. Successful inventory management means the right products, at the right stock levels, in the right place, at the right time, and at the right cost as well as price.
Inventory Management
Track and manage product inventory, sales, fulfillment centers, and vendor relationships. Inventory management is a system for sourcing, storing, and selling products in inventory. Successful inventory management means the right products, at the right stock levels, in the right place, at the right time, and at the right cost as well as price.
Income and Expense Tracker
Track income and expenses using transaction register and summary worksheets. Save images of receipts within the register.
Income and Expense Tracker
Track income and expenses using transaction register and summary worksheets. Save images of receipts within the register.
Break Even Analysis
Calculate the break-even point for your business with this ready to go template. For any new business, calculating the break-even point is an important calculation in your business plan. The break-even point is the point at which total cost and total revenue are equal, meaning there is no loss or gain for your small business. In other words, you've reached the level of production at which the costs of production equals the revenues for a product.
Break Even Analysis
Calculate the break-even point for your business with this ready to go template. For any new business, calculating the break-even point is an important calculation in your business plan. The break-even point is the point at which total cost and total revenue are equal, meaning there is no loss or gain for your small business. In other words, you've reached the level of production at which the costs of production equals the revenues for a product.
Cap Table with Shareholder Database
This capitalization table (cap table) template includes an integrated database of shareholders, both institutional investors and employees. Easily assign share allocations to the right investor, and then drill down at any time to track and manage information about them. A cap table is designed to show the equity capitalization for a company. Cap tables are most commonly used by startups and early-stage businesses but all types of companies may use them. In general, the cap table is an intricate breakdown of a company’s shareholders’ equity including common equity shares, preferred equity shares, warrants, and convertible equity.
Cap Table with Shareholder Database
This capitalization table (cap table) template includes an integrated database of shareholders, both institutional investors and employees. Easily assign share allocations to the right investor, and then drill down at any time to track and manage information about them. A cap table is designed to show the equity capitalization for a company. Cap tables are most commonly used by startups and early-stage businesses but all types of companies may use them. In general, the cap table is an intricate breakdown of a company’s shareholders’ equity including common equity shares, preferred equity shares, warrants, and convertible equity.
Invoice
Use this invoice template to create and customize your own invoices to your clients. An invoice is a document used to itemize and record a transaction between a vendor and a buyer. Typically, a business sends an invoice to a client after they deliver the product or service. The invoice tells the buyer how much they owe the seller and sets up payment terms for the transaction.
Invoice
Use this invoice template to create and customize your own invoices to your clients. An invoice is a document used to itemize and record a transaction between a vendor and a buyer. Typically, a business sends an invoice to a client after they deliver the product or service. The invoice tells the buyer how much they owe the seller and sets up payment terms for the transaction.