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.
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.
Number of payback years
Total Number of payments
Interest Rate for Loan
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.
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
OK and you will be presented with this box:
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.
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.
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
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!
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: