Welcome to Excel and
Finance video number two. If you want to download this
workbook and follow along, click on my YouTube
channel, then click on my college apps link. And you can download the
workbook Finance and Excel Chapter 00 Yes, this is for
a finance class. And we’re talking
about Chapter 00, which just is an introduction
to Excel and math. Now this workbook– we
did our last video– we had an introduction to
ribbons and Excel and whatnot. This workbook will
have the yellow sheet tabs will be information. The blue ones will be
the empty templates. So you can see there’s a cell. And you’ll complete that in
class or watching the video. And then the red ones
will have the answer– the completed answers. Now I’m going to
use my arrow key. Notice the arrow key, as we’ve
talked about in the last video, scrolls through the sheet,
but keeps the current sheet still active. Another way to do this
is Control-Page Down. That actually moves the
sheet and the active sheet. So we want to start on this
one, Formula and Functions. In this video,
we’re going to talk about formulas and functions. Now a formula always
starts with an equal sign. That’s the symbol you
type into the cell– equal sign– that
you’re telling Excel, hey, I’m going to do a formula. Now in this example here,
we have a annual rate. And there are two
periods per year. Later we’re going to talk
about bonds in Chapter 6. We’ll have bonds that pay
interest twice a year. So you can’t use
the whole rate– 7.75%– or as a decimal, 0.0775. You cannot use that
to calculate interest. You’ve got to divide it by 2. Now get this. This is a formula. We talked about this a
little bit in the last video. But we’re going to
emphasize it here. When you have numbers
that can change, like interest rate, number
of periods, loan amount, and you’re building a little
financial template like this, you never type these
numbers into the cells. Instead, use cell references. So with that in mind, let’s go
ahead and click with our mouse, our white selection
cursor, and you can see it puts B2 in the cell. And we use our math operator
Forward Slash for division. And we divide by 2. Notice this is not
0.0775 divided by 2. It’s B2 divided
by B3, all right? So that’s how we’ll do it here. One further point–
formula inputs in cells– formulas with cell reference
pointing to the formula inputs, but always label
your formula inputs. That’s just important. You guys want to be good
at finance and Excel, so you’re going to do it. Those of you in my class, you
also have an added incentive. Because if you hand in a test
without labels, formula inputs, and proper formulas with
cell references, well, you get hardly
any points at all. All right, here’s
what you never do. And unfortunately,
in the working world, you see a lot of this. 0.0775 divided by 2– and that’s what they do. They get the same answer. Ah, but there’s two problems. The first problem is
as soon as the interest rate changes, right? So we’re going to
change this to 95. This formula
beautifully updates. And you can see the
formula up there. It’s B2 divided by B3. So these– anything we
put there, it changes. But this one did not. Number one, it’s faster. It’s faster to change inputs. Number two, you clearly
see explicitly what your rate and periods are. And they’re labeled properly. The third reason is if you
have a spreadsheet filled with these, and they’re
all over your spreadsheets, and they don’t have any
labels, then how are you going to edit your formulas? So never do that. Another formula. And over here, I’m
going to hit F2. That puts it in edit mode. We created our formula with cell
references using our cursor, that [? Weiss ?] cursor. Over here we’re going to
see a different method. We’re going to
type an equal sign. And instead of touching your
mouse, use the Up-arrow key. I want to take Up-arrow,
Up-arrow, revenues minus Up-arrow. Totally awesome. That is faster if your cell
references with formula inputs are close to the formula. So using the arrow keys– totally awesome. Now in this class,
I will almost always list the labels in dark blue,
the formula inputs in white, and then any cell that
has a formula in green. Out in the working
world, you probably wouldn’t want to do that
when you’re making reports. But for this class, just
to help us decipher, there’s going to
be a color code. Formulas, formula
inputs, and labels. Sometimes in assumption
tables, you’ll see I use red. But most of the time I use blue. Now those are formulas, right? We saw how we use an equal
sign and some cell references. Now let’s look at
another type of formula. I’m on this sheet right here. I’m going to hit
Control-Page Down, page down, and get to the sheet tab for
the sheet Name Functions. And I want to look at Functions. Now I always have notes. There’s some notes over here. You can see that
we’re down here. Sometimes I have them up at
the top, some at the bottom. So when you download this,
you can read the notes. They usually have the
highlights of what we’re doing in each video. All right, we talked
about in the first video the sum function. There’s a keyboard
shortcut in this class. We’re going to use it. We’re not going to go
Home, click on this to get the auto-sum. We’re going to use the
keyboard shortcut– Alt- Equals. You hold Alt and tap Equals. Now remember when you’re
putting functions in, whether you’re using
the keyboard shortcut, or sometimes we’ll
use the Insert Function and the Function dialog
box, it always tries to guess. You can see the dancing ants
are dancing around the cells. If it’s correct, then
you can hit Enter. If it’s not– for instance, if
this really should have been over here– notice as long as the
dancing arts are dancing, you can just click and drag. Oh, I made a mistake. Oh no, oh no! As long as they’re
dancing, you can edit. And when you finally get
it right, there you go. And you hit Enter. This is what you
never want to do. Notice up here you can see equal
sign– means it’s a formula. Sum, that’s the
function– and a range– B7, colon, all the way to B10. Never do this–
equals that plus– and unfortunately, again
in the working world, you see this all the time. My first day in accounting job,
almost 10 or 12 years ago– a long time now– came in and the accountant
gave me the template that the last person
working there had made. And it was filled
with junk like this. I said, can I change
the spreadsheet? Right, so you get
the same answer. But what’s the
problem with this? If I click on that
cell and hit F2– well, first off, it takes
longer to make. And second– Escape– since spreadsheets are
dynamic, these are cash flows. What if all of a
sudden you wanted to insert a couple
more cash flows here? And you went right-click
on the row header– right-click, Insert. And then you typed 400 here– I’m using my Tab key– and 400 here. Well, you can see that
this formula updated, but this one didn’t– I’m going to click
here and hit F2– ah, because there were bookends. B7 to B11 with a colon
in between– when I insert something in between
those bookends, it updates. Escape, click here, F2, that
puts it into edit mode– didn’t update. Deadly mistake. And on my first accounting job,
like it was fate or something, the accountant came
in and says, oh, I want you to build
a new template. And I had already
changed it all to sums. And all she wanted me to
do was to insert a couple extra months. And I did it in
front of her eyes. And of course, she thought
that was magic, when all it was was using a range like that. All right, a couple
more functions– remember functions
are also formulas. If I click on this– how
you can see equal sign. And this formulas just
happens to have a function. Later, we’ll have formulas
that have functions and cell references and math
operators and everything. Here’s another one we’ll
do later in the class. We’ll take average or
arithmetic mean, the average of all of these returns. I’m going to type, because a
lot of times in this class, we’re going to use the same
function over and over. We’re going to use
average over and over. We’re going to us PMT for
loan payment over and over. So we’ll type them,
because it’s much faster than using your mouse
and going searching for it. As soon as you see it
highlighted in blue, you can hit the Tab. If you don’t like that,
you can type like this. You can come down here and
select it, and then hit Tab. You could even do this– double-click. Since PMT and average and
some of the other functions we’ll use in this class,
usually after 20 times you know what letters
you have to type. You type A-V-E-R and then Tab. All right, and then we
can simply click and drag. When you have a simple sum or
average or something like that, you don’t have to type that
last little closing parentheses. You can just hit Enter. And there is our average, right? This is like 2007, 2008, and
then some measly averages. So over the last four
years, our average on this particular stock
was really terrible. All right, now let’s see how
to search for a function. We want to calculate
a loan payment. So I’m clicking in the cell. I have my labels and
formula inputs here. I just want my yearly
interest payment. Later we’ll do monthly and
semiannual and quarterly and all sorts of
amazing finance tricks. The yearly payment, we’re going
to have a loan for $50,000 at this annual rate
and this many years. And we need to know
the yearly payment. I’m going to click here. There’s other ways to get Insert
Function, but that’s fast. There is a keyboard shortcut– Shift-F3. I tend to never use that. Shift plus F3– that’s show
me my list of functions. So I’m going to click here. This is the way I usually do it. And there’s Insert Function. I clicked Escape. Shift-F3 also does that. All right, we can search. We can also go to All,
which is the category you want to select. Then you got All– in 2010, there’s
over 400 functions. In 2007, there’s like 350. You can type. Come up here and type. I’m just going to
type loan payment. I can’t spell or type payment. And then click Go. All right, and then
it gives you a list. And especially in some
of these finance– these are all finance functions. We’ll get to see
almost all of those. The only reason why we
won’t see a few of them is because we have
different ways to do it than the function. But look, there’s a list here. And here’s a description–
any one of these. You might have to click
on a number of them and read the
description to find out the function you really want. But let’s read this. “Calculates the payment for a
loan based on constant payments and a constant interest rate.” We’ll talk all about the
details of that later. But that’s exactly what we want. We’re like, whoo-hoo! So now I’m going to click OK. And the advantage of using the
Functions Argument dialog box is every time you click or
use Tab to move forward, the description here changes. I’m going to use Shift-Tab
to go backwards or click. Notice it explains what
that argument should be. Now this PMT, for
this example, we’re going to use these
three arguments. Later in this class, since
this is a finance class, we’ll definitely see the use
for these other arguments. These are in bold. So these are ones
that are mandatory. The ones that are not in bold
means it will work without it. We’ll learn those
arguments later. But notice there’s
five arguments. Now that word argument
comes from math. It just means a number
I need in my function to get it to calculate
the right answer. Now Rate– let’s read this. “Is the interest rate
per period for the loan.” For example, use 6% divided
by 4 for quarterly payments at 6% APR. So it even gives
you a hint here. Now you can grab the
title bar like this. And actually, if you shake it
up, it tends to obey better. So let it know who’s boss. All right, now Rate– as soon as you see a
text box with this, it means you’re allowed to have
your cursor there and click on a cell. So our rate is going to
be annual, all right? And then I’m going to hit Tab. And I got NPER. NPER says “is the total number
of payments for the loan.” Well, this is a simple one. Later, we’ll see much
more complicated ones. 10, we need 10. And Tab. Present Value–
we’ll get to talk about that for the
first time in Chapter 4. But for our purpose
here, just think of present value as how much
the loan is worth right now. So I’m going to click
on this cell right here. Now let’s notice some great
things about the Functions Argument dialog box. It gives you a preview
over here of the input. So here– very conveniently
we see our cell references, which is what we’re going
to use for any formula. When the number doesn’t
change, you put it into a cell and use the cell reference. Here’s our preview,
which is helpful. Here’s our unformatted answer. You can see lots of
decimals hanging out there. And there’s our
formatted answer. Now we’ll talk about what this
formatting stuff does in just a few videos ahead here. But there it is. Let’s click OK. And there is our answer. Now this is actually
currency format. And those parentheses
in debits and credits, assets equal liability
plus owner’s equity– accounting–
parentheses mean minus. Now if you don’t like
that, later we’ll talk about number formatting. But I’m going to show you
right now how to change that. Control plus 1. That is the keyboard
shortcut we are going to use for Format Cells. Format Cells can do anything. It can change the number
formatting, the color, the font color, the
borders, everything– Control-1. The very first tab– and we won’t look at many
of these in this class. Just because we’re doing
mostly calculating. We will look at a
few of them, though, in our last video
for Chapter 0.0. Number– this is the one
we’ll be using all the time. Currency– that’s what
happens to be applied. I’m going to come over here. It gives me a sample, tells
me how many decimal places. The symbol– because I can
have whatever other currency I want– and there’s a lot here. And here’s negative number. So I’m going to click here. Because I don’t want to
see that parentheses, just a little negative. All right. Click OK. More about number
formatting later. And also, we’ll spend chapters
talking about cash flow analysis and why
this is negative. But just realize that if
you borrow this much money, and this is your monthly
payment, it’s minus. Because it’s coming
out of your wallet. You have to write the check
and send it to the bank. All right, two other things
about formulas and functions. I’m going to show
you how to type this. Because this is probably one
of the most common functions we’ll use. We’ll type it out like this. And once we get
used to it, we don’t need to go up to the
Functions Argument dialog box to read about each one
of these arguments. We’ll be fluent after a
couple chapters with PMT. So we’ll just go
rate and that’ll remind us what comes first. So we’ll go rate like that. And watch the screen tip. It’s bold. When I type a comma, it
moves on to the next one. Now if you’re using
PMT for the first time, and you’re like,
NPER, what’s that? But again, this is for
when we get used to it. We’re like, oh, that’s
the number of payments. So we click there. It’s bold. So that means I’m
still in that argument. But when I type
a comma, boom, it says give me the present value. And I click right there. We will use screen tips a lot
in this class– totally awesome. When I close parentheses, the
screen tip goes away and Enter. One last thing– I want to come back over here
to this sheet right here. Or notice that sheet
is the selected one, so I can Control-Page Up. I want to go to Formula
and Formula Inputs. Now here we talked
about these numbers– putting formula inputs in cells. Now here’s the rule. And it’s written down
here totally clearly. If the number can
ever change, which means if you build a
template for loans, the interest rates do change. Sometimes you have a
different loan amount. Sometimes you have a
different number of periods. If these numbers can change,
you type them into a cell. So that’s what we did. Our calculation here
was just period rate. Aha– but if the
number never changes, which we’ll see some
examples coming up later, then you can type the
number into the cell. For example, if you were going
to get the effective rate, and we’ll do this
later in this class. This is just an
example of when there’s a number that doesn’t change. I’m going to go Equals,
Open Parentheses, 1, Plus. That 1– and we’ll talk
about 1’s coming up, also. That 1 is never
going to change ever. So you do not put
it into a cell. You say 1 plus the period
rate divided by the– actually why don’t
we do it this way. All right, just pretend
I didn’t do that. 1 plus the annual rate, divided
by our number of periods, and then you close parentheses. And then you have to
raise it to an exponent. And the exponent
that you raise it to is the number of
periods per year. And then you subtract 1. Now all of the cell
references here are numbers that can change. But that 1 and that
1 never change. So we can just
leave it like that. Now we’ll learn this in
Chapter 6, I think it is. But that’s an example. Another example, which we don’t
ever get to do in this class– if you do payroll,
a lot of times you have to multiply
the proportion of one day times the number 24. Another example in
this class– when you’re building a template
for consumer loans, a lot of times it’s never
anything but monthly. And so in that case,
this denominator, you could type it 12. All right, so get it? In this video, formula inputs
that change go into cells. Formula inputs that don’t
change, like the number 1 in some of our
interest rate formulas, like the number 12 when we get
to some of our consumer loans, those can be typed
into a formula. All right, that’s a little bit
about formulas and functions. Oh, one last list–
you can just look here. The yellow ones usually
mean just information. In this class, the things that
can go into formulas, here’s a list of them. Here’s basically the
ones we’re going to use. Equals sign, cell references,
math operators– like plus, minus, et cetera– built-in functions– like
average, sum, and PMT– and numbers if they
will not change. Those are the things
we’re going to see in our financial formulas. We may occasionally see a
comparative operator just to see if something is
greater than something else. These other ones I don’t
think we will get to see. All right, when we come
back in our next video, we’re going to have to
talk about math symbols in order of precedence,
exponents, and a few other math items. All right, see you next video.