Welcome to Excel Basics video number 10.
In this video, we got to talk about the Insert Function
dialog box to search for a particular function
and get help with function arguments.
Now here's a picture of the formula bar.
We're going to click that Insert Function button.
It will open the Insert Function dialog box where
we can search for functions.
And there, once we select a function,
we can use the function's Argument dialog box
to help us complete each argument
with the correct entry.
Now let's go over to the sheet search.
Now here's the task we've been given.
We have a price of a car, the annual interest
rate, the down payment, and our ultimate goal
is to calculate the monthly payment for the loan.
Now if I click in cell B14 and hit the F2 key,
man, I do not want to have to remember
that complicated finance formula to calculate monthly payment.
Well luckily, Excel has lots of built-in functions
for calculations just like this one.
We actually could go up to the formula's ribbon tab,
and then there's actually a function library.
If you are in finance, you might click the dropdown
and look through here, lots of common terms in finance
like FV for Future Value, IRR for Internal Rate of Return.
And you notice if I hover, it says for this one,
returns the internal rate of return
for a series of cash flows.
If we keep going down and searching,
especially, if we're in the field of finance,
we, of course, would immediately choose PMT
because that's the term we use for a payment on a loan.
And if you hover your cursor, you
can recalculate the payment for a loan
based on constant payments and a constant interest rate.
Now you can go through each one of these logical text.
These are the categories.
Date and time, remember, in our last video,
we talked about the end of the month.
But there's lots of other functions in the date category,
like net working days.
That one's amazing.
It reads returns the number of whole work
days between two dates.
That sounds amazing.
Look up in reference, math and trig,
if you needed the arccosine in your trig class
or you needed to calculate the combination or other functions
like degrees or radians for your trigonometry class,
you could search through that.
There's also more functions.
Oh, there's one of my favorite-- statistics,
all sorts of powerful, statistic functions.
Engineering cube, information-- compatibility is a strange one.
These are older functions that have been replaced
by more modern functions.
So that's one way to search for functions.
But here's a great way where you don't even necessarily
need to know the category.
Now monthly payment, I need a function right there.
I can come up and click the Insert Function button.
That's a little f of x from algebra.
If I click it, it opens up Insert Function dialog box .
Now I'm going to click Escape down here.
There is a keyboard--
Shift-F3 opens up Insert Function.
Now I'm going to just try and type something here--
monthly payment on loan and Enter.
And just like that--
this doesn't always happen--
right at the top, it's highlighted in blue
and down here is a description.
Calculates the payment for a loan based on constant payments
and a constant interest rate.
That's exactly what we have.
Now, it gets better than that.
Once you select the function, you can click OK.
And look at this.
It will walk you through each argument.
So rate is the first argument.
You can come down here.
And if you don't know what rate is,
there's a big hint down here.
It's the interest rate per period for the loan.
For example, use 6% divided by 4 for quarterly payments
at 6% annual percentage rate.
Man, that's really helpful.
I can click in the next argument with my cursor or hit Tab.
Sure enough, NPER is the total number
of payments for the loan.
Now I'm going to click Escape here
because I want to do some precalculations before we
fill this out.
For example, where it says rate, it
suggests that we divide 6% annual rate divided by 4
for the number of quarters.
Instead of doing that right here,
I'd rather do that over in the spreadsheet.
So I'm going to click Escape.
Now here's the price of the car, the Annual Interest
Rate, or APR, down payment.
Well, we have a couple of calculations first.
Remember in that dialog box that said take the annual rate
and divide by 4, well, we don't have quarterly, we
have monthly.
So I'm going to do that calculation in the cell
and label it over here following Excel's golden rule.
So monthly interest rate, that formula equal sign,
and I'm going to up arrow, up arrow
to get the annual percentage rate.
And I'm going to divide by 12.
Now wait a second.
I thought this violated Excel's golden rule.
Remember, if a number can change, put it
in a cell with a label and refer to it in your formula
with a cell reference.
But if this really is monthly interest rate
and then eventually we'll have a monthly payment,
and that's never going to change,
that doesn't violate Excel's golden rule.
Because guess what?
The number of months in a year is not
going to change anytime soon.
I'm going to hit Enter.
So 0.4%, notice that's already formatted.
If I go to Home, I can see that it's got
percentage number formatting.
Now years, that's given.
And now we're going to also need the total number of payments
to get our formula to work.
That's going to be total months.
Well, if we have five years, we simply come down here--
equals, up arrow to get five years, or whatever year I
put there, times 12 months.
I'm allowed to put that 12 because 12 months in a year
won't change anytime soon.
Enter.
Now loan amount, well, if the car cost $24,000 total
and we're doing a down payment of $2,000,
then we simply need to get $22,000 as the total amount
for our loan.
That's going to be equal sign, and I'm an up arrow
to get the price of the car minus,
and then up arrow to get the down payment, and Enter.
Now we have everything that we need.
With cell B3 selected, Shift-F3.
And I'm even going to try something different.
I'm going to type loan payment and Enter.
Sure enough, it came right up again, payment.
Now I can double click that.
Click OK or hit Enter and that will open my Function's
Argument Dialog box.
Now I want you to notice something.
The cursor's in rate.
I can put whatever I need there and then Tab forward.
Also notice that the function is already in the cell.
Now that's the way we do it if we were searching
for the very first time.
I'm going to click Escape.
What if you've done this function many times?
So you can just type it out--
equals PMT, and then I'm going to accept it by using Tab.
And then all of a sudden, I'm like,
oh, I do not remember how to complete
each one of these arguments.
Well, here we can use the keyboard--
shift F3.
That is so cool.
And it will help us figure out what to put in each argument.
Is the interest rate per period?
Well, guess what?
We already calculated that.
So I can simply with my cursor in rate,
come over and click on the correct cell--
monthly interest rate.
So I see B9.
What's also great about this is it shows the cell reference,
and off to the side it shows what is actually in the cell.
Notice, that's not formatted.
Over here, as we've talked about the last few videos,
that's number formatting.
But underneath, functions and formulas
never see the formatted number.
They see the underlying number.
Now I'm going to hit Tab.
NPER-- I'm reading down here-- is the total number
of payments for the loan.
That's the total number of months for us.
So with my cursor flashing, I click on total months.
That's 60 right there.
Now I can see the 60 right there, I hit Tab.
Present value-- is the present value
the total amount that a series of future payments
is worth now?
Now that one's not very helpful unless you're in finance,
then it makes perfect sense.
But we want to use common sense here.
What's the present value of the loan?
Well, it's $22,000.
Now with my cursor in PV argument,
I'm going to come over and put the amount of the loan.
And there is the preview right there.
Now I can hit Tab, and I want you
to notice, bold, bold, bold.
These are not bold.
When you see arguments that are not bold,
that means that they are not necessary to get an answer.
Future value-- is the future value or cash
balance you want to attain after the last payment is made?
Zero if omitted, that means a balloon payment at the end,
and we don't have any.
Type-- we read this down here is a logical value.
Payment at the beginning of the period equals 1.
Payment at the end of the period equals 0 or omitted.
Now almost all consumer loans, which this is,
are end of the period or end of the month that you
make your payment.
And notice, it says 0 or omitted.
We'll assume that it's the end of the period,
so that's why these two arguments are not needed.
We don't have any balloon payment at the end,
and our loan payment is being paid
at the end of each of month.
So we don't even have to put anything
in for those arguments.
Now here's something also awesome about the Function's
Argument Dialog box.
There's a preview of the unformatted answer.
And down here, that's the preview
of the formatted result--
$413.15.
Now we're never going to use this in any other calculation,
so we don't need to worry about rounding.
Now I can click OK.
And when I click OK, that is amazing.
F2 to put that in Edit mode, PMT those three cell references,
Enter, or F2.
Two You can use that huge, long formula there.
I'm going to click Escape.
Now the reason that it is negative
is because a function like this in finance knows cash flow.
It knows that this money is coming out of your wallet
or purse each month.
Now, of course, we followed Excel's golden rule here.
Each one of these is a formula input.
And our formula down here, I'm going to hit F2.
Notice, those are cell references pointing up
to these other formulas.
So that loan amount of $22,000, total months 60,
and monthly interest rate 0.4%, those
are all formulas that the PMT function is looking at.
Those formula inputs are actually formulas.
And those three formulas are attached to our actual formula
inputs.
I'm going to click Escape.
You wanted a car for $24,000, but you decided
to go with a car for $34,000.
So I'm going to edit that number,
and watch what happens when I hit Enter.
Immediately, loan amount will change, and, of course,
that will be reflected in our PMT.
So when I hit Enter, just like that,
now you're paying $600.95.
Now I'm going to Control-Z.
You decided instead on a car that was $27,000.
And when I hit Enter, the interest rate
said 3.95% And notice, that's pre-formatted with number
formatting, so we see that percentage symbol.
But when I hit Enter, monthly interest rate will change
and also the loan amount already changed.
So when I hit Enter, all of those
changes from our formula inputs, our formulas
for monthly interest rate, and loan amount update
and so does our PMT.
Now, of course, you decided to go back to the original one.
So I'm going to Control-Z-Z to undo both of those changes.
And there it is--
$413.15.
So the Insert Function button up here or Shift-F3.
Let's look at another example.
We have a bunch of house prices, and we
want to calculate the average.
Well, we've typed this one out-- we did this in video number
1-- equals aver, returns the average arithmetic
mean of its arguments.
That's what we want.
Tab.
We didn't have to search for that one.
Now I'm going to click in the top cell,
and we're to use our keyboard, Control-Shift-Down Arrow
and Control-Backspace to jump back to the active cell.
Now close parentheses and Enter.
Now that's fine.
We actually got our average in statistics.
It's called the mean.
It added up every single number and divide it by the count.
But oftentimes, when you're averaging
if there's a few really small values or a few really
large values like these two over a million dollar houses,
then that will tend to push the average up.
So you're making this calculation.
And your coworker said, hey, look,
you've got to use the median calculation when you're getting
an average for house prices.
Now median literally means to sort,
so you'd have to sort every single number in perfect order
and then find exactly the one in the middle.
You don't want to do that because you know you can simply
search for a function.
You know the calculation is called median,
so either click this button up here or Shift-F3.
I'm going to type up in the search, Median and Enter.
And then sure enough, right at the top, median.
Returns the median or the numbers
in the middle of a set of a given numbers.
That's exactly what we want, so I'm
going to double click that or click OK or Enter.
Now this one's easy.
We can just enter the whole column,
so I click on the top cell.
We can see one number.
But I'm totally allowed to use my keyboard, Control-Shift-Down
Arrow.
Control-Backspace.
That's pretty cool.
Control-Backspace jumped us up.
And the range in number one argument is correct.
And I can see over here, it shows me all of the values.
Well, of course, it can't show all the values here,
but there is a tiny preview of that whole column.
There's the unformatted answer, and there's
the formatted median calculation.
Now I'm going to click OK.
And there you go.
You can see there's a huge difference.
That's why real estate people use the median.
It's a better estimate of a typical value
in a neighborhood when there's a few really large values
or a few really small values.
You can see that the average calculation was much bigger.
So in this video, we saw how to use Insert Function dialog box,
and we saw how to do the median calculation.
And then up here, we saw how to use the PMT.
And, of course, we also saw Shift-F3, the function's
argument dialog box to help us figure out
what goes into each argument for a given function.
That was Excel's Basics 10.
In next video Excel's Basics 11, we'll
talk about all the different things
you can put into formulas.
And be sure if you like this video, click that thumbs up,
leave a comment, and sub because there's always lots more videos
to come from Excel is Fun.
We'll see you next Excel's Basics video.