Alan Cring Productions in association with the Emergent Light Studio presents
the Illinois State Collegiate Compendium, Academic Lectures in Business and Economics.
This is Business Finance, FIL 240 for spring semester 2024.
Today, risk and return.
This is a subject that Excel is extraordinarily useful.
Building a template though, it's sort of like, it's just a matter of knowing a few formulas
and I'm going to show you a rather valuable thing that Excel can do
that will be useful to you possibly just well beyond this course.
But I'll get to that in a little bit here.
The first thing to do is of course, as always, let's look at the numbers.
And we have an interesting situation set up here.
If I can get to the actual numbers I want to use to start our look.
Come on, there we go.
You see that we have another one of those days that's kind of hard to say
what's going on here.
The Dow and the S&P 500 are down just a little bit.
And the NASDAQ was down too, but it has just popped in the last few minutes.
And the SPARK chart isn't even showing that.
Overall, there is not much of a direction that the markets are seeing where to go from here.
And in a case like that, you'll just see the stocks bounce around.
As you can see, the Dow was down just a tiny bit and so is the S&P 500, a small amount.
Nothing really major.
There's no big, big news.
Now, the Fed Chairman has just finished mumbling or prognosticated or saying what he has to say.
And that seems to have really gotten the NASDAQ all kinds of happy.
But it doesn't seem to have shown anything yet on the other two exchanges,
although the quotes may be delayed, and that's why those haven't popped.
The Fed's speech was overall taken positively by the markets.
It was favorable, the reaction was.
So that should get all of them up a little bit here in the next hour or so.
Crude oil, it's been plunging.
It was up there.
It's in a range that old 72 to 79 is gone.
Right now, its range seems to be wanting somewhere around 81 to 88 or 89.
And it's kind of bouncing around in there.
It's at the low end of the range.
As you can see, now, one point to make here is that commodity markets,
I think I've said this before, commodity markets do not work on the same clock as stock markets do.
So the oil market has been active since last night.
And through that time, it has been dropping somewhat.
It was up there, I think, around 83 a barrel when their trading started last night.
And it has been falling ever since then.
Well, that's still not going to help the price of gasoline.
It's still going to probably be a little on the high side.
But that's about all there is to it.
We just say, okay, oil prices are a little higher than they were.
It's not terrible.
We're seeing right now about 379 a gallon in the local area,
up from where it had gotten earlier months ago at 339 and 349.
But it's still not awful.
Gold, it's just staying flat.
There's not like any panic going on.
To what I am seeing, gold has now built a trading range around 2100 to 2200.
And it's staying right now tightly within that range.
So you don't see anything like panic, gold buying causing the price of gold to go up right now.
It's just sort of floating along, waiting like everyone else for what happens next.
The bond market, no, it's down two basis points.
Right now the yield is down two basis points, but down not quite so much.
But at the same time, clearly the yield is down, which means the price is up,
which means that there is some buying, nothing spectacular,
but there's some buying of safe harbor in the treasuries.
And that's probably some of that money is coming from the price of bonds going up
because the price of stocks has settled.
So some of the money that was there in the stocks, the stocks sold off a little bit,
and that money was put into bonds.
And that's why the price went up and the yield went down.
Coming over here to the other side of the world, the Nikkei started out in kind of a bad mood,
but through their trading day, which was our last night,
it crawled its way up to a decent end at about two-thirds of a percent up.
So, I mean, there's that.
But London, very much like the United States, is just sort of bouncing around.
It's finished the day off 0.01%, which basically means it was flat for the day.
It's just sitting there sort of like where we have been through the day today, too.
No clear direction in the Western world on where the economies are going.
We already know that the economies are getting better.
But that's already been put into the prices.
From here, the expectations would be about what goes from that assumption.
We've got an improving economy.
Is it going to improve more, less, or is it going to be about what we expected?
Right now, it's just sitting where it is.
So, yeah, we're just sort of going to work on the idea that it's where we expected it to go,
the economy is.
Not better than we expected, not worse than we expected.
And these markets are just not going to move.
They could just stay like this kind of flat for a day or week or even a month or more.
The markets could just sit waiting for something big to happen.
We don't see it yet, so markets are just going to stay flat.
Let me take you on a journey here for a little bit.
This is about risk and return.
To begin this, I have to talk about risk and get a little more formal.
I'm not really super formal yet.
But this is sort of the outline on a little bit more structured basis of the concept of risk.
Risk is, for lack of a better way of explaining it, risk is the variation in possible outcomes.
If there is only one outcome, then there is no risk.
We know what's going to happen.
But once we go to more than one possible outcome, there is risk.
Two outcomes, you are going to live or die.
Now that's risk.
Today you could live or you could die.
Well, you see, there we go.
But there could be more outcomes.
So risk, the first thing is about risk, how many possible outcomes.
The second part.
Okay, how different are those possible outcomes?
Well, if you have two possible outcomes, like a stock could go up 8% or 8.1%, well, I mean, that's not really risk.
It is a risk, but it's trivial.
But if the stock could go down 18% or up 23%, well, that is a lot of difference in those outcomes.
So how different are the possible outcomes is part of risk.
Another part of it is how, for lack of a better term, clustered are the possible outcomes.
How clustered are the possible outcomes?
In other words, if almost all of the possible outcomes are very close together and you have a few outliers,
that's a different situation from one where the outcomes are all over the place and you don't see any clustering around a specific amount.
That would be a very different kind of risk.
Now typically I'll draw this as a normal distribution.
Now in the first one versus the second one, most of the outcomes are near the center.
In the second one, there's more distribution. There's a lot of chance that outcomes could be very far away from the center.
And so this one, this lower one, would be a riskier portfolio than this one.
And one more, how biased are the possible outcomes? Let me explain that.
Let me draw you three.
That one in the middle, the one at the top rather, is symmetric. There's no appearance that some below are more likely than some above.
In the second one, there is more of a chance of seeing outcomes below than above.
Similarly in the third one, there is more of a chance of outcomes above than below.
That's bias. The technical term for it is skew, skew, S-K-E-W, skewness.
This is why, I'll get to the next one here, we have to turn this into meaningful numbers so we can compare one risk asset to another risk asset.
So we have to have numbers. The first number that we would want is a number that measures central tendency.
Where is the middle, as it were? Now there are two that do that.
The first one is the most popular, and it's not necessarily the best, but it's the most popular. It's called the average.
And I'm going to represent that with a little bar over it, X bar.
Now this one mathematically, and I'm just writing the formula because it looks cool in your notes, don't freak out.
It would be the sum of the data points from the first one to the last one of each data point times its probability of occurrence.
So you take each data point times its probability and add them all up.
That could be a real pain in the butt to do by hand. Fortunately Excel has a very cool trick in it that can make it.
Now if all of the probabilities are the same, then this simplifies to what you are probably more familiar with,
which is 1 over N times the sum from I equals 1 to N of the data points, X sub I.
In other words, add them up and divide by the number. That's the one that most people are familiar with, the average.
A little technical thing here of terminology. When I say average, it usually means you just add them up and divide by the number of data points.
The more precise term is mean, M-E-A-N. That would be when you actually take each data point times its probability of occurrence.
That's quite useful. The spreadsheets that I'm going to pull up here, I will load those into Canvas for you to use.
So don't panic if you don't keep up with me here on this. But I'll start with...
Here's a little bit of a data set. You got the return to a stock and there are five possible returns. Negative 12%, negative 3%, 5%, and 10%, and 14%.
Oh, sorry about that. Thank you. There, there's your sheet. And like I said, I'll have this up for you on the web.
If you want to key it in. So our first one, the expected return, the mean as it were. You will have to multiply negative 12% times 0.1% plus negative 3% times 0.15%
plus 5% times 0.3% plus 10% times 0.25% plus 14% times 0.2%. However, there's a stupid pet trick in Excel.
I don't think I've shown it to you yet, but you will be really happy to use this in a lot of situations. It's called sum product.
I'm going to write the expected return for cell B8, I'll say equals sum product.
Open the parentheses. Now with sum product, you tell it a first array, which would be the array negative 12%, negative 3%, 5%, 10%, 14%.
Then comma, you give it the second array, 0.1, 0.15, 0.3, 0.25, 0.2. And what that will do is that will take each data point on the first array and multiply it by the associated data point on the second array
and then add all those up. And there's your Uncle Bob, 5.15%. That's a lot easier than taking each one times its probability plus the next one.
This, a little later in the course, when we get to weighted average cost of capital, that is a pain to do by hand.
But sum product makes it a lot less painful, believe me.
Now, however, comes the second measure of central tendency. This one was the mean.
The next one is the median. And I could write a formula for it, but the formula is daunting, even though the idea is simple.
The median is the halfway point in the data. Half of the data is below the median, half of the data is above the median.
Now, that's an important thing. So I just write this as the halfway value of the data.
This, actually, the median in many situations is more important than the average.
Let me explain. Suppose that I give an exam.
Where's my eraser? Suppose that I give an exam.
And the distribution looks like this.
This, the average, is going to be abnormally affected by those few high scores there.
The median doesn't care what the numbers themselves are. All it cares about is how many are above the halfway point and how many are below.
So the median would tell me that half of the scores are about here.
The average would tell me because these high scores were pulling it, I would see a higher number.
Just because a few people aced the exam, but most people didn't do all that well.
The same would be true the other way. I could have a distribution where I have a few low scores, but most people did very well on the exam.
Those low scores would draw down the average, but they would have no effect on the median.
Because the median just counts how many are below and how many are above the middle value.
That's why medians, especially in education statistics, they should be used and they hardly ever are.
As a matter of fact, this abomination called Canvas, its statistics will tell me the average on an exam, but they won't tell me the median.
I have to take it into Excel and get the median. I have to put all that data into Excel for a test and then get the median.
Because that's the one that really matters, but Canvas doesn't give me that.
That gives you an idea of how poorly people understand statistics, even basic ideas.
You don't need that. Well, yeah, I do, because it means more to me.
If I've got a test where I have a couple of you geniuses, you four geniuses right there, aced the exam, but everyone else flunks it,
I'm going to say, well, the average was pretty good. Everyone else is going to feel like, boy, I'm a dumbass.
No, you actually were pretty much normal for the exam. It was just these troublemakers that ruined the average.
They busted the curve, as it were. Of course, that's something that we always love.
Well, it could have been that bad because one person got 100, even though the rest of you got 20%.
Of course, then they go out and slash the tires on my car. But you see what I'm saying? The median is actually useful.
Now, fortunately, Excel, if you look at this data, now, if you're going to just visually look at the data,
you would have to have them in order. But in Excel, it wouldn't matter whether they're in order or not.
It'll get it for you. But if you look here, these are in order. The middle value is 5%.
So I could just say equals median of that array, A2 through A6, and there it is.
And that's, by the way, that's great. If you have 100 data points, finding the middle one is not that easy.
Now, if it's an odd number of data points, then it's that middle value.
If it's an even number of data points, you take the one right below the middle and the one right above the middle
and take their average to get the median. But it is a really useful little thing.
There. It's one that's worth it for us to know about.
Now, the one that has to do with the risk, the classic one is the standard deviation.
It measures how dispersed the data is. You see, this one is this lower one right here.
This is a more spread out. There's less certainty in it because there are a lot of values above and a lot of values below.
In this one, there is not nearly as much spread. So there's more certainty, less risk.
So we use this one. Now, the formula for this one, just overall, the general formula is the sum from I equals 1 to N
of each data point minus the average squared times the probability of that data point.
There are a couple of homework problems where you'll do that one.
More realistically, if you've got a ton of data, you'll just assume each probability is the same.
And so this would reduce for a large data set to 1 over N minus 1 times the sum from I equals 1 to N of each X sub I minus the mean squared.
A caution here. See that one right there? That one's the one you'll usually use, the one, just 1 over N.
If it's a sample from some population, you use N minus 1 as that divisor.
If it's the whole population, you've got every data point that could possibly exist, it would be N. That's very unusual in real life.
You should know that because Excel will give you the chance to tell it whether you are using a population of data or a sample of data.
And because sample is what we usually use, that's the default.
But for this simple data set right here, the standard deviation, we'll have to do it the painful way.
Now the first thing I'm going to do is this.
I'm going to take each data point, for example, for the first one in cell C2, I'm going to take open parenthesis cell A2
minus the average, the expected return, 5.15. That's cell B8.
Now I'm going to hit F4 to make that an absolute reference on, so it's dollar B dollar 8.
Because when I sweep it down, that will keep the 5.15 in place.
Then close it, and then I'm going to raise, oops, close it.
What? Close it? Is there some reason this sucks?
Close it, and then raise it to the second power.
Okay?
Oh, I didn't do something here. Put in here square root.
Forgot to take the square root of the formula.
Okay, so now I'm going to latch on at C2, and I'm going to drag that formula down so that it does all of them.
Expected return.
No, I'm going to do it in the sum product this time.
I did it the other way in the last class. I'll do it a little easier now that I've realized I could have done it easier.
Watch this. The standard deviation will equal the SQRT, square root. You've got to do that part.
Now watch. Sum product of this array, those XIs minus X bars.
Huh, I added an extra one in there. I'll delete that in a second.
Comma the probabilities.
Close parenthesis, close parenthesis, divided by N minus 1.
I could have done that, I can do that by count, but I'll just put 5 minus 1, 4.
I did it again, didn't I?
What am I doing wrong here?
Hmm. I'll have to look at that. I may have to change that later.
I don't like that. Let me get rid of this first of all.
Each of these, A2 minus the data point minus the average squared.
Data point minus the average squared. Data point minus the average squared. Data point minus the average, data point minus the average.
And then I add those up. This, oh, I didn't, I'm using the wrong one.
Let me delete that, try that again. Equals SQRT, open parenthesis, sum product of
that array, probabilities, by that array.
Close off the square root, and then divide by N minus 1, which is 4.
Hmm. That number doesn't look right, but we'll take it for what it's worth.
I may have to fix that later, but it looks right. Don't know why it's not what I got the last time, but anyway.
There's one last measure of risk. It's called the coefficient of variation, the CV.
You take the risk divided by the average. This is better than the standard deviation on its own.
Here's why. The standard deviation is completely dependent upon the size of the numbers you put in.
So if I'm looking at returns on a penny stock versus returns on a ginormous S&P 500 company,
the numbers are on completely different scales, which means that their standard deviations couldn't be compared.
The CV takes that out of the equation, takes that out of the analysis. So in this case, I would take the standard deviation divided by the average.
And now I could compare any two companies regardless of the scale of the numbers that were involved in them.
I don't know in your statistics class, you took that management class, if they showed you CV, but it's sort of like the median.
Everyone looks at, expect the average and the standard deviation, when really you should look at the median and the coefficient of variation.
Those would be much, much more explanatory.
Now, let me do something here. I'm going to show you how to get data.
I'm going to take you to a site called onestockone.com. I'll try to get it straight. I may have to Google to get it.
There it is. Here's why you should know about this. Onestockone.com
will give you a data set of returns for, since 1975, yearly returns, since 1975.
You can get the return to the Dow, Standard Port is 500, the NASDAQ, well look at, here, let me show you one, the Dow.
Will you quit it? This is like gold, because data sets you have to pay for data anymore.
This site's been around for years and years and it just offers this, but it's not just like the Dow or the S&P 500 and NASDAQ.
You get the yearly returns for any market you could imagine in the whole world. The London Exchange, the Singapore Exchange,
places that you couldn't even think about what they're talking about. And these are all available to you.
Nikkei, some of these places like the Swiss Bourse, just about anything you would want to imagine. New Zealand even, for heaven's sake.
But even more than that, you could get stocks. Their yearly returns for, you go by the first letter, like I might want to find IBM.
Just click on the i-stocks and go find IBM. You'll get its yearly returns. It's a gold mine. It's not vast, day to day or anything like that.
But still, you can't beat it if you just want a good, decent, reliable data set. So what I do is I'm going to collect the yearly returns
for the Dow Jones Industrials and also for the S&P 500 and for the NASDAQ. Here's how you do it in Excel.
And I'll do this a few times. Nope, that's not the one I wanted. Okay. I'm going to take a data sheet, sheet one. Let's take sheet two.
The first one I'm going to grab will be the Dow. I go over to stock, one stock one, and I click on the Dow Jones Industrial Average.
This is its data set. First thing I'm going to do is grab the web address, the URL. Control C on it so that I've copied it.
Now we're going to go back into Excel. Data. On the ribbon, top, choose data. And then over on the far left, get data.
Go down to from other sources, and I will get from the web. And now you'll come up, it'll ask you for that web address.
Control V, there it is. And I'll say okay. Now it's going to connect to that web page.
Now oftentimes web pages have several tables. Many websites create their top bar, choose drop down menu. They do that as a table.
So it's going to see a couple of tables. Table one, see that's the table we want. We say load it. And there's all that data right there for you.
So I'll give this one the name Dow 30. Now I'm going to go to sheet two. And I'm going to back up one in one stock one dot com and click on the S&P 500.
And there it is. And I'll click on the top bar, the address bar, Control C to copy it. I'm going to do the same thing that I just did before.
I'm going to say data, go into Excel, in this new sheet, data, get data from other sources from the web. And I'll give it that web address and say okay.
And it will go in there, look around, see what if it's table two. Well yes it is. Table one, I'm sorry. Load. There's all that data in your Excel sheet.
And I'll call that worksheet S&P 500. And now I'll open up a new worksheet and we'll do the same thing for the NASDAQ.
Go back to one stock one dot com, back up and click on NASDAQ. Go up to the address bar and Control C to copy.
Then come back over here to Excel in this new worksheet, data, get data from other sources from the web. Control V, there's the web address of that data set.
Say okay. Table one, there it is. Load it. And look at that. And I'll call that worksheet NASDAQ.
And now I've got all these. The one I care about is the last column in these, the return.
So I can go down here. Let me make these three sheets a little bigger so you can see it a little better.
So now I'm going to go down here to the Dow. I'm going to say mean, median, standard deviation.
Now this is the one, STDE standard deviation. I'll have some of this here. And the coefficient of variation.
So now the mean equals average. And I just highlight that whole data set there from E2 down to E, what is it, E50?
There's the average. I'll turn that, these first three are percentages. The median equals median.
And I take that whole data set, drag it down.
Standard deviation equals STDEV. Leave it like that. That will give you the sample standard deviation.
Open parenthesis. Drag it down.
And then the coefficient of variation is just equal to the standard deviation divided by the mean.
There you go.
Now these first three are percentages. And I'll set them to two decimal places.
Now the coefficient of variation is a pure number. So you just highlight it and then hit the comma up in the ribbon.
And it will do it as a pure number.
And I can do that for all three of the sheets. And I'll leave you to be able to do this.
You've got the model and you've got the data. I encourage you to give it a try to grab data.
Once you get the hang of it, it's really easy. And you can even make it so that it's dynamic.
So every time you open the sheet, the most recent data will show up.
So that's kind of a nice little feature of it.
Anyway, as far as anything else goes, we'll pick the subject back on Monday.
You have a quiz to take. And when you're finished with that quiz, that's all I have for you today.
I thank you.