let's say that I'm shopping around for a
car loan to try to find one that I can
afford and I'm using Excel to help me
find the answer because I know that it's
great at calculating things
automatically
all I know right now is that the total
loan amount should be twenty thousand
dollars and the term should be sixty
months and the highest monthly payment I
can afford is four hundred dollars the
one thing I don't know is what interest
rate I would need in order to get a four
hundred dollar payment to find the
interest rate I'm going to use a feature
called what-if analysis which allows you
to fill in the blanks based on the
information that you have now this is a
somewhat more advanced feature and
you'll need to be pretty comfortable
using formulas and functions before you
can really use it
the first thing I need to do is set up a
function in this cell so I'll go to the
formulas tab
and I'm going to use the PMT function
which is in the financial category
you
the PMT function calculates the payment
for a loan based on constant payments
and a constant interest rate just like
any function if you need more
information about how it works you can
click help on this function but for now
you can just follow along and enter in
the same arguments that I do
for the interest rate will enter cell b4
divided by 12 and that's because the
yearly interest rate gets divided up
into 12 months
in PR is the number of payments and in
this case that is in cell b3
PV is the present value which is the
total amount of the loan and that's cell
b2
and we don't need to worry about the
last two arguments
since our interest rate cell is blank
Excel is assuming that the interest rate
is zero so this payment that it gives us
is not correct and this is where what-if
analysis comes in it will allow us to
say if the payment is $400 then what
will the value in this cell be
so go to the data tab
and under what if analysis you'll see
three options we're going to use goal
seek
you
set cell will be the cell that contains
the monthly payment which is b5
and we want a $400 monthly payment but
the PMT function actually needs to see a
negative value here so type negative 400
and it needs to be negative because the
amount is being subtracted from the loan
when you make a payment
where it says by changing cell we'll put
the blank cell that we're trying to find
the value for which in this case is b4
and when you click okay it should tell
you that goal seek found a solution
so just click okay again
and our answer is seven point four two
percent so that's the interest rate I'm
going to look for when I go out shopping
for a car loan
you may want to practice this a few
times because it is a little bit
different from the other features that
we've looked at but if you're in a
situation where you just need to fill in
an unknown value it is a really great
feature to use like what you just saw
rate it at a comment subscribe to our
Channel the freedom to learn what you
want when you want absolutely free
GCF learn free.org