I would like to thank our first guest blogger Ryan Cosby he is a finance major at Missouri State and will be graduating this semester. Check out his blog FYInance I LOVE the name. Enjoy!
Thinking about finances can be overwhelming and at the same time confusing. One, because money, interest rates, APR, and savings can be intimidating, and two, because it requires us to think introspectively about our finances.
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!
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”.
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.
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:
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
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.
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
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?
(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.)
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.)
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.