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.

good one. thnx for video !!!

You are welcome!

@ExcelIsFun

Hello , I am just wondering if I need some help in specific section in excel, would you please help me? Because , in this semester I am doing a course and its all about spreadsheet. have a great day.

Thank you.

I work about 80 hours a week and I do not do consulting, but if you post a clear question and I have an answer in my mind within 10 seconds (if the question is clear I can usually do that), then I can usually point you to a helpful video. If you wnat to learn excel from beginning to end, watch my Excel Basics playlist and then the Highline Excel Class Playlist.

This is great!

I am glad that you like it!

perfect, it helps me a lot. Keep on teaching like this, it is great. Greetings from Germany

You are welcome!

have you considered naming the cells that have the variables?

At what minute mark in the video are you referring to?

Thank you for these posts!

I'm looking on how to multiply my entire column by one factor is that possible? Is there a video for that? Essentially (A1:A21000)*1.14 is all I wish to do.

Basically where each is multiplied individually by 1.14.

very useful!! thank you so much! you makes me want to study excel! ;D

Around the 18:18 to 18:20 mark, after you're done typing it auto formats to match the other label cells (blue fill, white font)

thank you so much!! your video is lifesaver!

having a interview regarding tons excel

I am glad the videos help!

thank you so much for sharing your knowledge, and dedicated your precious time to help all of us. I am planning to go over all your videos. I would like to improve my financial modeling skills as well, do you have any good books to recommend and anywhere i could have some free couses like yours?

I do not know of any free financial modeling classes.

this video helped me in my assignment. subscribed!

I am glad that it helped!!

thanks

You are welcome!

Great Work….Keep Posting !!

Thank you man for the good work. Solid excel background before hitting VBA programming. Thanks once again

you are awesomely awesome ðŸ˜€ god bless

i really love you!! excel fun …….i am sure you are god`s messenger !!!

Thank you very much.

So helpful! Thank you so much!

Thank you! very helpfulÂ

Well explained! Thanks

You're a genius.

This might be the dumbest question, but I cannot find the correct Excel Workbook in the link provided. Under the Excel Finance Class Series, the number two file is Busn233Ch03.xls not the one shown in the video?

Thankyou

A Zillion Thanks to you!!! I'm going to master Excel and make your every effort count! you're an inspiration! Thanks bro! ðŸ™‚

This guy is an amazing teacher

sir please help me i could not find this exercise file in your given link

thank you for clear explantion. Pls help me find the excel files for the video in your link

Im 13 years old and my dad is an engineer and im going to teach him CATIA v5 and excel and this is very useful for me thanks!

thank you so much for a wonderful class

Hi.. i am unable to find "Finance & Excel ch00 workbook. pls send me the link. thank.

Please don't talk too much in your videos, it is really annoying. Good classes however, thanks.

sir i just dont know how to thank you.You guys are really doing a great job,it helper me a lot

im having trouble memorizing all the formulas for my finance classes.. would excel help me being more efficient with my classes?

inspirational very helping in increasing excel knowledge

Can you put direct link to download the file rather than the web page cause its kinda hard to distinguish….

Your videos have been a great help to me in teaching other Excel and Finance

*others

Sir your range goes from basic to highly advanced version

And I require basics before we move on to advance

Your video helps me to understand every portion of excel

Highly interested in all of your videos

Loads of love sirðŸ˜€ðŸ˜€