Thinking about finances can be overwhelming and at the same time confusing. One, because money, interest rates, APR, and savings ling can be intimidating, and two, because it requires us to think introspectively about our finances.
In this post I want to give readers one leg up on their finances. In this post I want to teach readers about a complex system of future value, but in simpler terms that we’re used to seeing. To a consumer there are two significant types of future value. They are: Loans and Savings Accounts. Today you will learn how to calculate loan payments on excel.
If you ever decide that you do need a loan, it is a good idea to go into a bank armed…
Well not armed with actual weapons, but the weapons of knowledge. You should know how things affect what you will be paying each month, and how much you pay over all! In this post I’ll tell how first how to calculate how much you’ll pay each month, and then how to tell how much you will pay over all!
Loans
First off let’s set up a scenario. You want to buy a car. The car is $5,000 and you really don’t have the money. You decide you need the car enough that you want a loan, and after careful consideration, you decide a payment of $180 a month is the most you can afford. Before you approach a bank, let’s see if we can’t drum up a figure on how much you would expect to pay a month for this car.
First start with excel open. Next you’ll want to get to the formula’s tab. After this, there is a button in the upper left hand corner called “Insert Function”.
You’ll want to press this button.
Next it will ask you to describe a function you want. What we’re looking for is the
PMT function. So type in PMT, or scroll down the list until you find it, and when you do double click the function (in this case it’s PMT). When you do it should bring up a dialogue box looking something like this:
So as you can see we have a few options and fields we need to fill in here. We’ll only be using the fields that are in bold. Don’t worry we’ll go over each in detail so you can see what we’re doing.
Rate
The first box you see is where we’ll put the interest rate the bank is giving us, or we expect to receive. Excel is decently intuitive when it comes to this. Any percentage you put in, Excel will assume APR (Annual Percentage Yield). This is just the interest rate that the bank gives you on the paper you sign. Plain and simple, it’s what percentage of the money you’ve borrowed you pay in interest each year.
Back to our loan; we decide that 8% is what we can expect. (This is just a number I’ve derived from experience. It could be low or high depending upon numerous factors.)
So simply insert 8% into Rate field as shown:
As I said earlier Excel uses an annual rate, plain and simple. Well that’s all and good, but the bank probably is not going to let us pay just one payment a year. They’re going to want us to make monthly payments. Now, since the rate is yearly we’ll need to divide it by 12 to make it a monthly payment. We don’t need to do this manually, just do the following in the field:
8%/12
And that’s it! If you’re extremely curious, the number to the right of the field is the 8% divided by 12 months. Essentially, it’s the monthly percentage rate you pay on the loan. Here’s the field again for clarity:
Nper
When we see Nper, we can think of “Number of Payments”. This field is similar to the top if we think about it logically. We decide that the car is probably a little older and the bank would probably be willing to give us 3 years to pay it off.
(Pro Tip: We also want to pay it off quicker as well. It’s a used car and we don’t want to be stuck with a loan for a long time, because the car may not be worth what we’re paying on it that long into the future!)
So we simply put a “3” in the field.
However, again it’s not quite that simple. What we’re saying in this field is that they’re giving us 3 years to pay it off. However, we haven’t yet told excel that we are doing it in monthly payments! So let’s do that.
Simply take the 3 and multiply it by 12 months in the year, it will look something like this:
(* stands for multiply)
Again, to the right side we can see what the number is. In this case it is 36. This represents the total number of payments we’ll be paying to the bank.
PV
We’re almost done. This is where it can get tricky and very deep. But we’ll keep it simple, I promise. PV stands for “Present Value”. Simply stated, we’ll think of this as the present value of our loan that we’re going to get, or the value that it is at the time we start it. This is possibly the EASIEST number to derive since we know we need $5,000.
So, simply put 5000 in the field. Below we show the formula:
In this box, we can also see the result! Look at the lower left hand corner; we see “Formula Result” and we can see our payment: -156.68. We can think of it being negative because we’ll be paying the bank this money. As we can see this number is below our target of $180. That’s good!
Let’s see if we can’t get our loan closer to that target….
“But wait, why would I want to pay more?” Is probably what you’re asking yourself. That’s a very good question.
To answer that, I’m going to tell how exactly what each of the three fields we just filled in does to your payment
Rate
It’s almost common sense to say that an increase in your interest rate is an increase in how much you pay. This is true. Go ahead, try it. Increase and decrease the 8% to other numbers…(i.e. 8.5%, 9%, 7%...) all the while, remembering to keep the “/12” behind your percentage. Watch the “Formula Result” change as you do this. What happens?
Simply said as the rate goes up, so does the payment and visa-versa. In short, the less we pay in interest the better.
(Pro Tip: Remember, when playing with the rate that banks usually are very firm on their rates. This will be the last place you’d have the best bargaining power with a bank.)
Nper
Increasing the number of years and months also can affect your payment. Think of it this way:
Increasing my time, gives me longer to pay and so I pay less in the short run and more in the long run.
Decreasing my time, gives me less time to pay and so I pay more in the short run and less in the long run.
This is a good example of why we may want to increase or decrease our payments on the loan. Try to play around with this number. The easiest way to do this is do the math yourself rather than multiplying the years by twelve. For instance, maybe you think you’ll try two and a half years, typing in “30” (30 months in 2 and half years) instead of “2.5*12” into the field is actually a lot faster, and adding or taking away single months is easier.
(Pro Tip: Banks are usually A LOT more willing to work with you on time length of payment rather than your percentage rate. When playing with a payment, try to adjust this first to see if you can’t get a more favorable result.)
PV
This is simple: the less money you borrow the less you pay! Maybe we could borrow less, pay a bigger down payment, or talk the seller down on the price! Try playing with this number as well!
(Pro Tip: Increasing any of the above inputs will increase the sensitivity of the rest. In other words: The more money you borrow, the higher your rate, and the longer the life of the loan, the more the other things affect how much you pay!)
Lets put it together!
Okay, let’s put it all together. Let’s take our first payment of $156.68 and see how much we’ll pay over the entire lifetime of the loan. This is rather simple. Just take $156.68 and multiply it by the number of months (or payments) listed in the formula. We’ll see that it comes out to be: $5,640.48.
In order to find out how much interest we’ve paid just take that number minus the total we spent on were loaned on the car which was $5,000. We’ll see that we paid a total of $640.48 in interest over the 3 years we had the loan.
Now, I want to prove my point of paying off a loan sooner, and that increasing your payments can be of benefit to you!
Let’s decrease our number of payments to 30 like I stated earlier (or two and half years). As we can see it gets us A LOT closer to our goal of $180. It comes out to be: $184.44.
So take those figures and see how much we’d pay over the life of the loan! (184.44 multiplied by 30). We see that we’d pay a total of $5,533.20. Do the same thing as we listed above with finding the total interest, and we see that we’ve paid a total of $533.20. That’s a savings of $107.28! That may not seem like much, but try doing the same exercise with $50,000 or $100,000 and seeing how much you save!
I hope you’ve learned a little about loans, and the next time you need one (if you ever do) you can do a little math beforehand and be prepared for your banker!
