Alan Cring Productions in association with Emergent Light Studio presents the Illinois State Collegiate Compendium, academic lectures in business and economics.
This is business finance, FIL 341 for Autumn Semester 2024.
Today, stock price valuation.
I have a spreadsheet for this one.
It, and some of the worksheets within it, a lot easier to use the Excel spreadsheets and to try to do these on your own.
Trust me on that. You'll see why here in a little bit.
But, so in your canvas, you're going to go to files, and once you're in the files, you go to spreadsheets.
I said you go to spreadsheets.
Find spreadsheets here.
There we go.
Go to your spreadsheets folder, and the one that you will want to download is stock valuation.
Now, go over here to the three dots on the far right side of that line, and you click those and you download
the stock valuation sheet.
Enable editing.
Go to the dividend model.
I will take this from the simplest scenario, which is pretty unrealistic,
on through to some scenarios that are more realistic.
I'll show you how you deal with each one of those.
There's a worksheet in that spreadsheet for each of the different scenarios that I'm going to start with.
Along the way, I'm going to introduce some new terminology to you as well, just to make this a really rollicking lecture.
Okay, we're good.
First things first.
I've said this part before.
What we do in finance and in accounting hinges on an assumption, what we call the going concern assumption,
that the company is going to stay in business for an indeterminably long period of time.
In accounting, that is why we can do accrual accounting, not assuming that we have to pay all of our bills
right away in cash.
We allow accruals, accumulation.
In finance, it plays its part through the assumption that a company's earning power,
we hope positive but sometimes negative, continues for the foreseeable future.
In other words, we don't have a terminal date for a company.
Now, we may terminate our ownership of that company through selling the stock,
but the company itself goes on forever.
The first scenario you see up there is painfully simple.
It's an easy scenario.
This is where we would assume that the company, you're going to buy the stock,
and you'll collect the dividends for a couple of years, and then you will sell the stock down the road.
So we can start with a, and I will just give you a quick scenario.
This first sheet is woefully easy.
One thing I do want to promise you is that I won't take the models any further than examples that I do in class
so that you don't have to do anything crazy to the spreadsheets.
You buy a share of ZLT, common stock.
You receive a dividend at the end of the first year,
and again, this is a very unrealistic scenario, but it starts us off, of $1.60.
In the second year, a dividend of $1.80,
and in the third year, a dividend of $1.55.
After that third dividend,
you sell the stock for $18.75.
If you discount the cash flows at 7.25%,
what is the current price of the stock?
I'll give you a minute to catch up there.
Okay.
Now, most of these, I'm going to, I think all of them, I will be drawing a timeline.
It's not absolutely necessary to do it, but it helps clarify what you're after, what you're doing,
and I do them to this day, not just for the teaching, the pedagogy of it,
but just so I can keep my own brain straight.
What we have here, you buy a stock, year zero.
You got year one, year two, you got year three.
So in this case, the first year was $1.60.
You're going to get a buck sixty, 1.60.
Above the two, second year, I'm going to put the $1.80.
And above the three year, I'm going to put the $1.55.
But I'm also going to put my sale price, $18.75.
So what you are really doing here, to find the price right here,
P zero is going to be $1.60 discounted back one period, what did I say,
one plus.075 to the negative first, plus the second year dividend, $1.80,
discounted back two years at 7.25, I'm sorry, I put it, 7.25, 7.25,
one plus, to the negative second power, plus the third year,
which will be the $1.80, but also plus the $18.75 discounted back one plus.0725
to the negative third.
That's how you're going to get the price.
Now, you're just going to take each of these back,
you're going to telescope each of them back to the present,
and add them up, and that should be the price of the stock now,
because that's the present value of its future expected cash flows to you.
Again, it's kind of unrealistic, well how do you know the price in three years?
It sets the stage for more difficult ones.
But I promise I'll have one of these easy ones on a quiz or an exam,
so that you can actually sort of have one that's an anchor.
So in this case, on the worksheet one, the dividend model worksheet,
I'll put in the dividends that we need to do, 1.60,
the next dividend, year two, was 1.80,
and the dividend three is $1.55,
and then over here in the column C, where the sale price,
you put that in, and I'll put in $18.75.
There it is.
Now I have to put my discount rate in.
What did I say the discount rate was?
7.25%, 7.25, and there's your stock price, $19.66.
That easily.
Now notice how the formula works, because if I gave you four years,
you'd have to adjust this.
The way the formula works is I'm going to say equals NPV,
that's for the present value, open the parentheses,
and then the first entry will be your discount rate, B10,
and then the second one will be your first dividend, comma,
let me do it, I want to do it a little bit simpler for you to make it clear.
Your first dividend, B2, comma, your second dividend, B3,
comma, your third dividend, plus your sale price.
Now obviously if I gave you four years, it'd be B2, B3, B4, B5, plus C5.
I could do this with a macro so that it could tell how many years you were doing and all that,
but like I said, macros are not very popular.
And so close the parentheses, $19.51.
Now I will allow for, obviously as I always do, a little wiggle room in your answer.
Take the answer if it's plus or minus 25 cents or something like that,
because you might glitch a number in there and cause a value to change,
but there's all that you do, that's all you do.
Now the second one, okay, you okay on that one?
The second one, for this one I have to step back just a little bit here
and go through a kind of stock that I've barely mentioned,
and I think they mentioned it earlier in the book,
but I have a specific purpose for doing it here.
When we talk about stock, we almost invariably talk about common stock.
But as I had mentioned earlier, there's another kind of stock called preferred stock.
Preferred stock is odd, because to a certain extent it's like a bond.
Let me just do an example.
ABV cumulative preferred, I'm sorry, ABV, let's put in 2.65% cumulative preferred.
Par value $80 per share.
Here's how preferred works.
Preferred, when you buy a share of preferred, as long as you own that share of preferred,
you will get the dividend from the preferred every year forever, or they may divide it up.
It is perpetual, it is what we call a perpetuity.
The cash flow never ends, and it is the same.
What is that dividend? D, the dividend, you take 2.65% times the par value.
If you notice, that's just what you do with a bond.
You take the coupon times the face value, the par value, $1,000, it's always $1,000 for a bond.
Preferred, it's whatever is on the stock certificate itself.
So I would take.02, what did I say there, 6.5, times $80, $2.12.
So in other words, you're going to get a dividend every year for each share of $2.12,
and that will go on forever. It will never stop.
Now I won't beat this one over the head, but in your math class,
you were shown a kind of addition of numbers called a series.
It's an infinite, or maybe not an infinite, but in our case, an infinite sum of numbers.
Usually something like 1 half plus 1 fourth plus 1 eighth plus 1 sixteenth, something like that, forever.
And you saw, if you remember, that sometimes these diverts, they become infinite.
But some of them converge, and the one we will be using here actually converges to a specific value.
And there's a formula. You don't have to just keep adding up $2.12 times 1 plus the discount rate to the negative 1,
plus $2.12 times 1 plus the discount rate to the negative 2 forever. You don't have to.
Because the price of the share, adding up the discounted present value of $2.12,
forever at higher and higher discount rates as the years go on,
it comes out that the price of preferred stock is the dividend divided by the discount rate on the preferred stock.
That's all it is.
And in this case, that would be $2.12.
Now, let me give you a discount rate for the preferred here.
Let's say that its discount rate is 2.50%.
So we would have $2.12 divided by.025.
Survey says, divided by 2.5.
And I ask, let's try that again. $2.12 divided by.025.
$84.80.
And you notice that the yield, what the market wants, is 2.5, which is lower than what the dividend is.
So this price sell, this preferred stock sells at a premium to par.
Just like a bond.
Watch.
Let me do discount rate and the price of the preferred stock.
Now, we've done 2.50%.
And we've got a price of $84.80.
Let's try something like 2.80% for that formula right there.
So I take the coupon, or rather the dividend, $2.12 divided by.028.
And I get $75.71.
It sells at a discount to par.
Now let's see what would happen if I took a discount rate that was the same as the dividend rate, 2.65%.
In that case, I take $2.12 divided by.0265.
It sells at par.
It's just like a bond.
If the yield is below the dividend, then it sells at a premium to par.
The market doesn't need 2.65, it only needed 2.50.
So it's paying more than it should, so the market drives it up.
If the market wants more than the coupon, 2.80% against the coupon, or the dividend, I keep saying coupon, 2.65,
it will sell at a discount.
But if the yield and the rate and the dividend rate are the same, it will sell right at par value.
Just like a bond does.
As the price goes, as the yield goes up, the price goes down.
As yield goes down, the price goes up.
The same inverse relationship.
This formula right here.
That formula for the price of a preferred share, D over R sub P, is actually a special case of a more general formula.
I'll get to that in just a minute.
But that more general formula for dividends that don't stay the same but grow at a constant rate, like some stocks do,
the price of the stock will be the dividend in the next period, divided by the discount rate on the stock,
minus the growth rate.
Now the dividend in period one is nothing but the dividend they just paid, grown one period.
One plus G.
The formula for the preferred is just where G is zero.
So watch this.
If I want to price a share of preferred stock, if I wanted to price a share of preferred stock,
I go to the constant growth rate formula.
Well the first thing that I would want to do is I'd say this is a preferred stock so it has a zero dividend.
And then I look at my discount rate.
Let's try that 2.5% discount rate.
And the current dividend, if I want to find the current dividend, I just say equals, what is the dividend?
Well it's.0265 times $80.
And there you go.
So for preferred, all you do is set the growth rate to zero.
That's basically all you do.
Because dividends on preferred stocks don't grow, they just stay the same forever.
Good times.
And I could do the other one too where the current dividend, what do I say, discount rate.
What did I do for that other discount rate?
2.8075.71, just like I calculated.
So this one, as you see, as I had said, you do a little more legwork in these worksheets than you did before.
You have to just make sure that you have the right growth rate in, the right discount rate, and also what the current dividend is.
And with preferred, all you do is take the dividend rate times the par value of the stock.
And I'm going to put that here.
And then you just get your answer.
That one shouldn't be too bad for you to do on the quiz.
The quiz will just be a series of these problems.
Starting with the very simplest dividend model problem, and then going to the preferred stock.
Now, let me step back a little ways here.
Companies almost always start out not paying any dividends.
That's one of the reasons they're risky.
Their betas are high, and their prices are so volatile.
We really don't have a whole lot of a model to figure out what the present value, the intrinsic value of the stock is.
So we kind of feel around in the dark.
However, most companies, the first thing that they want to do is to start paying a dividend.
It's a signal that the company can afford to give the shareholders back some of what they have put in.
So a company first starts by giving out a dividend, but it usually is one of those things where the dividend is all over the place.
They give out a dividend of $1 the first year, $2 the second year, then they can't give a dividend the third year, then it's 85 cents the fourth year, something like that.
So it's all kinds of, all over the place.
However, companies want to try to stabilize their dividend.
More precisely, they want to stabilize the dividend to a constant growth rate where there is a certain present value that we can find.
And so the larger investors who want some way to see intrinsic value, the fund managers who want some anchor to know where intrinsic value is,
so that they will be more willing to invest in this company.
That's why companies strive eventually to get to a constant growth rate of their dividends.
Now, older companies have already gotten there.
They are already in the phase of their life where their dividends grow at a constant rate per year.
I wasn't sure if I bought that years ago, and then I started looking at dividends by year of some big old companies.
Sure enough, from year to year, the dividend was growing at a relatively constant rate.
Now, sometimes it was a little above that rate, sometimes a little below that.
But the trend line was actually very tight around the data point.
This company grows its dividend at 2.25% per year, or 1.2% per year.
You can see it in the data.
Companies really do have constant growth rates.
So we can talk about a company, like for example, G.C.B. just paid a $2.50 dividend.
That is expected to grow at a constant rate of 3.25%.
For the foreseeable future.
If we discount the cash flows at, let's say, 8.75%, what is the current price?
Price per share of G.C.B. common.
Now remember that this formula uses the dividend one year out.
I'll let you get caught up with me here.
So in order for me to get this price, I would take D0, which is the dividend they've just paid,
and I would take the discount rate of $2.50 times one plus the growth rate of.02325 divided by the discount rate.0875 minus the growth rate.0325.
Now if you do this by hand, for heaven's sakes, don't be like me.
Put the bottom in parentheses.
Don't forget to do that.
One thing I should mention.
You might see a problem where they're telling you what the next dividend would be.
They'd be telling you the D1.
So if they do tell you that, then you don't grow it.
Like if I said, the dividend next year is expected to be $2.55, then you just put $2.55.
You wouldn't grow it because it's already D1, the one they're giving you.
But I give you this.
I will use the current dividend they've just paid, so you will use it in the form that I've shown you there.
Now here we go.
We're using the same worksheet, constant growth rate model.
The discount rate is first, 8.75%.
The growth rate in this one is 3.25%.
And the current D0 dividend is $2.50.
And there's your stock price, $46.93.
That is easy.
Just make sure you put it in the numbers correctly.
And I give you a little room in case you glitch on a little bit on the number, but $46.93.
Now you can do it by hand, but you're fraught with little mistakes in parentheses.
This way you just get the answer.
No pain.
And remember, if it's preferred stock, the growth rate is zero.
That's all you have to remember is if it's preferred stock, the growth rate is zero.
And you have to calculate the current dividend, the dividend rate times par value.
But this takes you through the first more or less realistic stock pricing technique.
Something I need to mention here is that growth rate.
This formula would not work if the growth rate were larger than the discount rate.
In other words, if I said the growth rate is 10%, all bets are off.
Now that is not as much of a weakness as you might think.
If you decided to take a course after this one because you just can't live without me teaching you finance,
we have a term that at first it sounds like it was rejected and it's still rejected by some business people.
We call it the maximum sustainable growth rate.
And it's a mathematical formula.
And we have found that this actually jives with the empirical studies.
In other words, looking at companies.
The maximum sustainable growth rate is a growth rate that a company,
if it goes above that growth rate, it actually will die unless it pulls it back down below that growth rate within a year or two.
The reasons might be all kinds of varied, but we know that you can't have growth rates that are just 50% over and over and over again.
Because if they are, the company begins to get into terrible trouble and eventually it either goes bankrupt
or it gets absorbed by a much larger company whose overall growth rate is sustainable.
So it's not so much to say, well, the growth rate can't be as large as the discount rate.
In fact, that comes into play from the results of maximum sustainable growth rate of a company.
I mean, you should see the growth rates of companies, older companies when they were young,
those growth rates were insane, really high.
But also their discount rates were very high too because they were risky and young.
So rarely do we see a G that is larger than R anyway.
But when we do, if that does not come under control relatively quickly, the companies just don't survive.
It would be sort of like in a situation where you, good man, when you were a toddler, you were a little chunky,
but you were going to grow out of it, but you didn't.
You grew at a foot every month and 20 pounds every month.
My God, by the time you were two and a half years old, you were eating my cats.
But you can't live that way. I shot you. You ate my cat.
But you understand that this is actually a part of a much larger principle.
Growth cannot exceed a specific place because intervening factors go into it.
But anyway, that's just a side note.
You don't have to worry too much about it.
Certainly not for my quizzes and tests.
Okay, enough of that.
Now, the most realistic scenario.
I can't remember whether I mentioned this or not before now,
but companies don't start out paying dividends.
And when they do, the dividend is going to be all kinds of different things.
I had a few, some of my consulting jobs that lasted for more than a couple of years.
There would come a time when they were profitable and there was pressure from the shareholders.
Will you please start paying a dividend?
We've put money into this company, and all you do is take the profits and plow them back in.
You don't give us a little bit to keep us going.
And sometimes it can get kind of ugly.
Companies, I was at shareholders' meetings, and they'd have me, the consultant,
answering the shareholders' questions at the annual shareholders' meeting,
while they sat behind me in chairs looking down at their papers.
And you get, there was one, the woman was just furious.
She had her time and she used it to just, where are the dividends?
We have not had a dividend.
We put all this money in, and you keep giving us promises,
and now you're bragging about a profit and you don't have a dividend for us?
What is going on here?
And she's just yammering, she was.
And I had to be diplomatic with her.
Bitch, I don't, there's nothing you can do.
We're moving money back in.
But sooner or later, the question comes up at the C-suite meeting,
do you think it's time we started paying a dividend?
And my answer is two-part.
One is that your shareholders want it.
And two is they don't really matter as much as the big dogs on Wall Street
and the fund managers and the heavy investors.
They want you to show them that you can afford to pay a dividend.
So yeah, but the second part of that is,
once you start paying a dividend, you damn well better just keep paying it
because you pay it once and then you say, we can't pay it this year.
That's even worse than if you didn't pay it.
So the second thing is, once you start paying a dividend,
you should keep going with the dividends.
Now, they don't have to be the same every year, not for a while.
But eventually, you need to have a horizon
where the shareholders can count on a constant growth rate.
They get a dividend and they know it will be that plus a little more scratch the next year.
And then after that, a little more scratch the next year.
That is when the fund managers get really interested in you.
You have shown one, that you can afford a dividend,
and two, that you can sustain it, and three, that you can grow it.
And that is not going to happen this year or next year.
Your horizon could be out there five, seven, ten years.
But there is a point right now where you say,
we have this goal of getting the dividend under control and growing at a constant rate.
That's when we are a big boy company or a big girl company and we can do this.
So that takes us to the last of the models,
where we say the company may pay dividends that are kind of all over the place for the next few years,
but then we have a place where we have hit the horizon.
So that you have dividends, but they are out of control for a while.
So I'll draw a timeline of a company.
Here is now.
One year from now, the company pays its first dividend of let's say 75 cents.
And then two years from now, the company pays a nice dividend of $1.50.
And then in the third year, it pays, let's say it can't pay a dividend.
And then in the fourth year, it picks dividends back up again at $1.60.
And then from there, it is committed to a constant growth rate of the dividends,
let's say of 2.4%.
So ever after that, the dividends will be growing.
So this dividend out here, year five, will be the $1.60 times 1 plus.024.
Year four is called the horizon.
And we can find the price of the stock at year four.
Because that would be the dividend one period further out divided by R minus G.
Because we can use the constant growth rate model now.
We've hit a horizon where the constant growth rate formula kicks in.
And I can tell you right now, that can be a pain in the butt.
Especially if you have like a seven, 10-year horizon.
You've got to take the present value of 75 cents discounted one year back,
plus the present value of $1.50 discounted back two years,
plus the present value of zero discounted back three years,
plus the present value of $1.60 discounted back four years,
plus the horizon value discounted back those four years.
So that is where we can use the horizon value model.
But this is the one where you have to customize it.
You clear out your dividends and the horizon value.
Now what we're going to do is first get in our discount rate.
I didn't give you a discount rate here.
Oh, 2.4%, that's right.
No, I didn't give you the discount rate.
Let's say that the discount rate is 9.1%.
Now once you have the data, for heaven's sakes,
I give you a word problem.
I'd say the same thing that I've said 43 years ago in math classes.
Get the numbers out of the word problem and throw away the words.
The discount rate, I see that that is 9.1%.
The growth rate of the dividends is 2.4%.
We get those in there, thank God.
Now you go back in, I've made this one for six years.
That's the worst that I would do to you is six years.
It's not that bad, but still, I could write a macro
that you could just key in the number of years and the table would adjust.
But it would be a macro and that would be a problem.
So year one, 75 cents.
Year two, $1.50.
Year three, zero.
Year four, $1.60.
Now we're going to do the horizon value,
rather the dividend one year further out,
which would be equal to the dividend one period before times,
or in parentheses, one plus the growth rate.
Now for the horizon value.
Remember that the horizon value is the first dividend
where constant growth is going to happen.
Be sure that you see that.
The horizon value here is in year four.
We need year five because we need the dividend in year five.
So we say equals that year five dividend,
which I calculated here, divided by R minus G.
So in other words, at year four,
the dividend is paid and the stock price at that time is $24.45.
Now I do appreciate this is a little more robust.
You're having to do a little modification.
But now we're going to say equals NPV to find the present value.
Close the parentheses.
Open the parentheses.
First thing with the NPV formula,
you use the discount rate, comma.
And then each year of dividends, one after the other,
year one dividend, that's B2, comma,
year two dividend, that's B3, comma,
year three dividend, that's B4.
And then in year four, I'm sorry, that's year three,
it is the dividend that year plus the horizon value that year.
We put them together, close the parentheses,
and there's your Uncle Bob, $20.34.
Now if it was six years, five years, and then it went constant,
I'd do the year six, and then I'd put the horizon value there
with the year five.
This is the formula.
I can't remember what the formula function is.
But you can see it right there.
All you do is first put in the discount rate, then comma,
one after the other.
Don't leave out the zero because it's keeping count of years.
And then you just keep putting in the dividends until you get
to the year when the dividends stop being random.
That was year four because they're going to grow
constantly after that.
So I stop in year four, get that dividend, then I grow at one
period so that I can have the formula for the horizon value,
and you go back here and get your horizon value.
There you go, that's close enough.
Got it?
That's the horizon value.
Just be sure that you take the dividend one year past the last
one you're given, and then you use that in the formula in column
C of the cell right before that calculation.
Let me do something here.
There you go.
That helps.
That's a little better.
So in other words, step one, get all your numbers in.
Step two, get the dividend one period after your last number
you're given.
Step three, get your horizon value.
Back up to the one where it all started to be constant and do
the formula.
Let me put this in here.
Then I'll re-upload it.
That should help you a little bit there.
Okay?
Now if you can remember how to do this, and I've got one more
sheet here.
That's just another horizon value problem for you to see.
Now this one I've actually made it so it calculates it, but
again, that wouldn't work if you had a different number of years.
So there is that little bit of customization you have to do.
But this one is big enough, this last little example here, is
big enough that you could just delete a couple of the rows
from this one for a problem that was fewer.
I give you my guarantee that I would do no more than six years
of dividends.
Okay?
That way, I mean, otherwise you're not proving anything to
me.
You're just typing extra numbers.
So it'll be relatively limited.
And all you need to do is be able to show me that you can use
the formula the way I want.
You all good on it?
That is stock valuation in a nutshell.
And that is all I have for you today.
I thank you.