a common sense approach to money

How to Create A Simple Loan Calculator In Excel

How to Create A Simple Loan Calculator In Excel

This post may contain affiliate links; please see our disclosures for more information.

Being the huge nerd that I am, I use Excel for just about everything to organize my life.  From simple checklists to complicated calculations, it’s an incredibly useful tool for making your life easier.  When it comes to my personal finances, I track everything via Excel spreadsheets; budgets, bills, student loans, you name it.  While I could talk all day about the various merits of this program that’s so essential to my life, today I want to focus on one specific function: using it to figure out what payments you can afford.

When shopping for a car or a house, most people have a rough idea of what they can “afford”, but using a simple loan calculator can help you nail down what that actually means.  If you think you can handle payments of $200/month, how much car can you buy with that?  Alternatively, if you know the car you want costs $11,500 total, how long will it take you to pay that off?

Today I’m going to show you how to build your own loan calculator in Excel using two different variations.  In the first, you want to know how much your payments would be; in the second, you want to see how much you can afford to borrow.  Feel free to follow along as I walk you through the steps!

Loan Calculator Version 1:  What would my payments be?

1.  In a new Excel workbook, add the following fields (no need to include my descriptions in gray or the key at the top)

Pic 1

2.  Now that you have the fields you need, fill in the green boxes with the information you have (hint: use the same cells as above so that the formula pulls from the correct place).

Principal – This is the total amount of the loan. If you’re buying a car that costs $12,000 but you’re putting $2,000 down on it, then enter $10,000 into this cell.

Interest Rate – This is the fixed interest rate. Variable rates are a little trickier and aren’t addressed in this post but we’ll get to those another day.

Term – This is how long you’ll be paying the loan for in months – you can put years here, but your formula will change a bit, so for our purposes just use months.

If you don’t have any of the information above, put your best guess; you can alter these numbers as much as you like to see how it affects the payment.

3.  Once you’ve entered your inputs or the “KNOWN” information, now we want to find the “UNKNOWN”, or the payment. This requires entering a formula known as “PMT” for payment.

Click on the blue cell to highlight it, then enter the following EXACTLY:

=(PMT(F4/12,F5,-F3))

This box will appear as you start typing the formula, and it helps to breakdown what you’re asking it to calculate:

Pic 2

In the above box, it shows that it needs the rate, the number of payments (nper), and the principal value (pv).

Rate: You’ll notice for this value we’re using the rate divided by 12, since this rate will calculate your interest monthly

Nper: This input is the total number of monthly payments, or Term in months

Pv: This is asking for the principal loan amount, so we put a negative in front (if we don’t, our payments will come out negative instead)

The example below shows the formula with the inputs:

Pic 3

You can see that the color coding shows where the formula is pulling from the inputs (another reason I love Excel).

4.  The hard part is over! Simply press enter once your formula is complete, and the payment should appear!

Pic 4

Now that your formula is set up, you can change the green cells as much as you like, and the payment will automatically calculate.   This is incredibly helpful when car shopping, comparing loans, and even house hunting.

Pic 5

Play around with these inputs and watch how it changes the payment – lowering the rate lowers the payment, but so does increasing the term.  Mess around with the numbers and find what makes the most sense for you!

Loan Calculator Version 2: How much can I afford to borrow?

1.  In the same workbook, set up the following fields similar to the first version; this time, you know how much you can pay monthly, and are trying to figure out how much total you can afford to borrow.

Pic 6

2.  Now that your fields are set up, enter your “knowns” into the green cells.

Payment – This is what you’re comfortable paying each month (make sure that you’re realistic with this number!)

Interest Rate (percentage)

Term (in months)

3.  In the blue cell below, now we want to find the total principal value that we can afford. To calculate this, enter the following formula:

=PV(I4/12,I5,-I3)

This time, the following box will appear as you start to type your formula, asking for the interest rate (divided by 12 months in a year), the number of payments, and the monthly payment.

Pic 7

4.  Press enter and your formula should calculate! Here’s an example using a similar scenario to show how much you could afford to borrow if your payments were $200/month.

Pic 8

Notice how the amount changes as you adjust different variables.

Pic 9

Using this simple loan calculator has helped me countless times to figure out what I can afford to pay and how much I can spend.  While getting out of debt is always the goal, making smart financial decisions along the way can help make that goal more attainable.

Let me know if you have any questions; I’ve been called an “Excel wizard” and am happy to share my tips and tricks with you!  Happy budgeting!

 

Looking to become an Excel wizard yourself?  Check out this Excel cheat sheet for helpful tips to get started!



1 thought on “How to Create A Simple Loan Calculator In Excel”

Leave a Reply

Your email address will not be published. Required fields are marked *