How to Use Excel’s Loan Amortization Calculator (2024)

Planning to take out a car loan? Great idea.

But make sure to monthly update the total loan, interest rate, loan term, and your loan payment frequency.

Feel dizzy already? Yeah – keeping track of all these factors manually can be a real pain.

Luckily, we have the loan calculator that does all these things for us automatically 🧐

We have curated a detailed guide below to help you understand how it works. So without further ado, let’s get right into it.

What is a loan amortization schedule?

Amortization refers to paying off a loan in periodic payments with an ever-increasing principal and interest rate.

Amortization offers a hassle-free loan repayment process. But many people are not inclined towards this concept.

That’s because a huge part of your loan payment amount is going into interest. And you don’t even realize how much interest you’re actually paying.

Moreover, it gets difficult to keep track of your loan repayments 😕

Fortunately, we have the Excel loan amortization calculator. It lists out the entire repayment schedule in seconds.

You can either get the free online template or create your own loan calculator using the Excel PMT function.

Usually, people prefer using the online template as it’s free and ready to use. All you need to do is download the template, add your values, and all the results will be updated for you.

With the Excel PMT function, you need to combine it with other functions, which can be time taking. But it’s a good option if you want to create a custom loan amortization schedule.

Download the template

You can easily download the loan amortization schedule template by clicking this link. Press the Download button, and voila! It’s done.

Once it’s downloaded, open it in Excel, and it’s ready to be used 🚀

Calculate loan amortization using the template

Let’s now see how to use this template to track a monthly loan payment.

Once you open the template, click enable editing at the top of the Excel sheet.

You can now edit the spreadsheet.

The template might seem daunting at first, but it’s really simple actually.

Let’s break down all the information given in this template

Loan Details

Loan Amount: Refers to the amount you have borrowed

Annual Interest Rate: The current annual interest rate

Loan Period in Years: The expected period of loan repayment in years

Start Date of Loan: The day the first loan payment will be made

Loan Summary

Monthly Payment: The loan amount you will be paying off every month

Number of Payments: Payment frequency to pay off the loan

Total Interest: Total interest to be paid on the loan

Total Cost of Loan: Total amount of loan to be paid after adding interest

You also have a table at the bottom. It offers insights into the payment amount, number, remaining balance, interest, and principal.

Now that you know all the values listed in the template let’s see how to use them.

Say we have borrowed a loan of $20,000, and the current annual interest rate is 7.60%. We anticipate paying off this loan in around six years, and the payment date of the first payment is 1/12/2022.

Let’s add all this information to the amortization table now.

As visible, the template has automatically updated the loan term and final payment. It shows a payment frequency of 12 months. This means you will be making payments on a monthly basis.

The values in the table have also been changed as follows:

You can use this to check when you need to make the next payment and its present value with interest.

How convenient is that? 😃

The only downside of this template is that editing the payment period is messy. By default, it shows monthly payments and not weekly or yearly.

Most of the time, this is not a big deal. But if you do need a weekly loan amortization schedule, you can create your own in minutes.

You can use the advanced PMT calculator for this purpose. And make sure your payments are set up just as you want. Learn more about it here.

That’s it – Now what?

In this article, we learned what a loan amortization calculator is. We also saw how to use a loan amortization template to organize our payments ahead of time 😉

All in all, using this calculator can save you a lot of unnecessary hassle. And you will be able to produce more concise, error-less, coherent loan reports.

All you need to do is download the template, customize it according to your loan details, and voila! You’re good to go 🤗

Other resources

Did you enjoy reading this article? If yes, then you’d love to know more.

Read here: IRR function in Excel, PMT function, and more.