Using Excel’s Financial Functions To See If You Can Afford That New Car

The average person shies away from the financial functions in Excel because they think they are only for the mathematical wizards. But, if you use the Insert Function button, some of them aren’t too hard to learn and can answer some questions we all have.

Scenario

You want to buy a new car and the car dealers all offer different interest rates and payment plans. They throw a lot of numbers and plans at you, but usually all you want to know is how much of a downpayment do I need to make so I can afford the monthly payments?

Well, Excel can figure this out for you pretty easily, using the PMT function.

Let’s say you know that the car you want costs $25,000 and you know you can only afford $450 a month for car payments. You have $6,000 in your savings account, but you don’t necessarily want to use it all for a downpayment. So, how much do you really have to put down on this car in order to get the payments you can afford?

First, talk to your lending agency and find out how many years you have to pay and what the interest rate is that they are offering. Then enter this information in a new blank worksheet.

This shows, in cell B4, you are borrowing $19,000 ($25,000 minus the downpayment of $6000 which you put in B6), over four years (B2), totaling 48 payments (B3, which multiplies the number of years by 12 months), at a yearly interest rate of 4.9%. Notice that I put formulas in both B3 and B4, so you can change the number of payback years and the downpayment amount and everything else will be adjusted automatically.

Now let’s figure out what the monthly payment would be with these figures.

1.      Click inside B7 and click on the Insert Function button on your toolbar (or go to the Insert menu and choose “Function”)

2.      Choose “Financial” from the box that says “Or select a category” and choose PMT from the list below. Notice it tells you at the bottom, that the PMT function “Calculates the payment for a loan based on constant payments and a constant interest rate.”

3.      Click OK and you will be presented with this box:

4.      Now, all you have to do is fill in the boxes and Excel will give you your answer:

a.    In the “Rate” box, type B5, which is the cell that contains your interest rate. However, the lending agency will give you the YEARLY rate and what you need is the MONTHLY rate, so, you need to put B5/12 in this box (that’s B5 divided by 12 months in a year)

b.    Hit your tab key to move to the “Nper” box.

c.     The “Nper” box is for the number of payment periods and we have that entered in B3, so put B3 in this box.
(TIP: You don’t have to type B3 in the box. If your cursor is positioned in the box, you can just click on cell B3 and Excel will put it in the box for you.)

d.    Hit your tab key to move to the “Pv” box.

e.    The “Pv” box is for the present value, or the amount you are going to pay. That value is in B4, so put B4 in that box.

f.      Hit your tab key to move to “Fv”.

g.    Notice that “Fv” and “Type” are not in bold like the others. This is because these arguments are optional, whereas the bold ones are mandatory. See at the bottom of the box that Excel tells you that Fv is the future value and if you leave it empty, Excel will assume 0 (zero). Since you want to pay this loan off entirely, you want a zero balance, so just hit your tab key to leave this empty and move to the “Type” box.

h.    Lending agencies often offer different payment amounts if the loan is paid at the beginning of the month than if it is paid at the end of the month. Your lending agency will tell you if they offer this. For now, put a zero (0) in that box, assuming you will pay this loan at the end of the month.

i.       Make sure your choices look the same as mine below, then click OK.

Your spreadsheet should now look like this and you see your monthly payments with these choices are $436.70, which is lower than the $450 a month we can afford.

Click in cell B6 and change the downpayment to $3000. And look how close we still are! $505.65. and only using half of our savings for the downpayment!

Can we get it even lower? Sure! Click in cell B7 where your PMT formula is and look in your formula bar at the top of your screen and see the formula is =PMT(B5/12,B3,B4,0,0). Change that last zero to a 1 (assuming your lending agency allows for paying at the beginning of the month). That saved you another two bucks and now your payment is $503.59.

Maybe your lending agency allows you to pay over 5 years instead of 4? If so, change the 4 in cell B2 to a 5 and notice your number of payments in cell B3 adjusts to 60 and your monthly payment drops to $412.48. WOW! Maybe we can make even less of a downpayment!

Click in cell B7 and change that downpayment amount to $1000 and see that your monthly payment is $449.97 and right in line with what you want to pay. And, you still have FIVE THOUSAND BUCKS left in your savings account!

So, you can see that Excel can be a handy tool when you are trying to figure out stuff like this. And, you can go back to that car dealer armed with all you need to know to bargain with him/her.

And, if you want to know if you can retire early, try Excel’s Financial Function named “FV” and see if you can figure out how to determine what that savings plan you have will be worth in 25 years.

Remember, Excel doesn’t require that you are a mathematician. Excel does the work for you!

Linda Johnson is a college instructor of all of the Microsoft Office programs and also offers private training to companies in the Philadelphia area. Companies outside that area can also buy corporate licenses for distributing her ebooks and CD or send their employees to her online classes. See more info about Linda on the TechTrax consultants page or go directly to her services page to learn more about her online classes, ebooks and CD: http://www.personal-computer-tutor.com/services.htm.