the real power in Excel comes in its
ability to perform multiple mathematical
calculations for you one of the tools in
Excel that you can use to perform these
calculations is a data tool called
what-if analysis what-if analysis allows
you to see the effect that different
values have in formulas it sounds
complicated I know but let's think about
it in real-life scenarios maybe you've
wondered what interest rate do I need to
qualify for to have a car payment of
$400 on the car I want let's say you
need a loan to buy a new car you know
how much money you want to borrow how
long the period you want to take to pay
off the loan and how much money you can
afford to pay each month but what you
need to know is what interest rate I
need to make my payment $400 a month
right now I can see that if I didn't
have interest and just broke this
$20,000 into 60 monthly payments I'd be
paying about three hundred and thirty
three dollars and 33 cents a month but I
need to find the interest rate that will
give me a $400 a month payment rather
than performing all these calculations
on your own you can use the what-if
analysis tool before we get into that
you may be wondering about this function
you see in b5 I've already inserted a
function to this cell that will
calculate this for me this isn't part of
the what-if analysis but let me just
really quickly show you what I did I
needed a formula that would calculate
the monthly payment instead of writing a
big formula myself I inserted a function
to do this just select the formula tab
and click the insert function command
from the dialog box that appears select
PMT short for payment and then click OK
you're then asked to select cells that
will give the function the needed
information this dialog box looks really
confusing but it really is very easy as
you select a field a description is
displayed below that basically tells you
what's information and cell you need to
choose once you've entered all the info
you'll just click OK right now I'll just
close this if you're still how
comfortable with functions you should
watch our video lesson about functions
which will give you some more
information ok let's go on to the
what-if analysis tool there are actually
three types of what-if analysis that you
can use to see these select the data tab
and locate the data tools group click
the what-if analysis command and the
list with the three options appear all
three of these are different types of
what-if analysis one of the tools goal
seek is the perfect tool to help you
determine the interest rate you must
qualify for to meet your needs
once you select goal seek from the menu
a small dialog box appears here we have
to set a specific cell to a specific
value by changing another cell in this
situation we want to set to be 5 our
payment cell it already appears in the
dialog box because the cell is selected
we want to see it at a value of $400
since we're making a payment that will
be subtracted from our loan amounts we
have to enter that as a negative number
we'll move down to the next field then
we need to set the cell that we want to
change that will give us that payment
amount
so we'll select cell b4 which is the
interest rate
let's click a cell to select it and it
will appear in the dialogue box now
click OK and then ok now we can see that
the interest rate appears in the cell
and we know that a 7% interest rate will
give us a $400 a month payment on a
twenty thousand dollar loan that is paid
off over five years or 60 months as you
can see in goal seek
we gave Excel the result that we wanted
a payment of $400 a month over 60 months
and it gave us an input value that would
give us that result spend some time
exploring goal seek and practice using
it so you can see what a powerful
feature it is