How to Use Excel’s Loan Calculator Template
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
A loan payment calculator is a must-have tool if you’re planning on taking out a loan.
It’s a good way to determine how the loan amount, its interest, and the loan term affect the total amount you’ll be paying.
If you want to make your own, the ‘PMT’ function in Excel, coupled with other functions, can be used to create a loan payment calculator.
But if you like the easier and quicker way, you can download and use existing loan calculator templates online.
Today, we’ll be showing you how to use Microsoft’s free simple loan calculator to create an amortization table and payment schedule for any type of loan.
Kasper Langmann, Co-founder of Spreadsheeto*This tutorial was written in Microsoft Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.
Microsoft’s free loan calculator template
Not only is this a free loan calculator, but it also lists out your payment schedule on it. You can see details like balance, principal, interest, and ending balance.
To download this template, simply head over to this link and click the ‘Download’ button.
Once you got the file, open it up and you’ll see the calculator.
How to use the loan calculator
The first thing to do after you downloaded the file is to enable editing.
You can’t input your own figures without doing this.
Click ‘Enable Editing’ at the top just below the Ribbon.
Now that you can edit the file, you have to know first the appropriate cells you should edit.
In this calculator template, there are lots of information you can see:
- Loan amount – the amount you’ll be borrowing
- Annual interest rate – the interest rate of the loan annually
- Loan period in years – the term of the loan in years
- Start date of loan – the day when the payments for the loan will begin
- Monthly payment – the total amount you’ll be paying monthly for the duration of the loan
- Number of payments – total number of payments/installments
- Total interest – the total interest you’ll be paying
- Total cost of loan – the total amount you’ll be paying
In addition to that, there’s an amortization table and chart showing you the principal paid, interest paid, and the loan balance.
Kasper Langmann, Co-founder of SpreadsheetoUsing the calculator is really easy. Once you change a value, the result gets automatically updated.
However, you should only change the loan details:
- Loan amount
- Annual interest rate
- Loan period in years
- Start date of loan
Here’s what would happen if you change a single detail:
The ‘Number of payments’ is automatically calculated (multiplied by 12) given that you would have to pay monthly for the duration of the loan.
But if your loan’s payment period isn’t monthly (weekly, quarterly, semi-quarterly), editing this calculator would prove to be a bit of a hassle.
As most loan calculator templates, this one is currently set up to calculate and show the payments you’ll be doing monthly.
If you really need an Excel loan calculator where you can adjust the payment periods, you can actually create your own advanced PMT calculator in under a minute. Just download the file on that guide and copy-paste the formulas from the article to complete the calculator.
Wrapping things up…
Using a loan calculator would save you time from doing computations. Plus, you’re less likely prone to errors.
With a ready-template, there’s no need to re-invent the wheel. All you have to do is download the file and you’re ready to go. 😊
Kasper Langmann, Co-founder of Spreadsheeto