Welcome to Finance in Excel video number 61.
Hey, if you want to download this workbook for chapter 7,
click on the link directly below the video
and scroll all the way down to the Finance Excel class.
And this video here, we want to talk about a valuation
method for stocks.
Now, there are lots of different valuation methods for stock.
In this textbook, they talk about just one.
And pretty much the other ones, we're going to talk--
well, the one we're going to talk about
is based on future cash flows.
Oh yeah, that's what we've been talking
about throughout this whole book.
And the future cash flows for stocks are dividends.
Now, there are other valuation methods.
And most of them require that you are
pretty good with accounting.
All right, so let's just look at a hypothetical situation here.
Let's just say there was a stock.
And we knew exactly what the dividend
was going to be at time 1, so 1 year from now, 2 year
from now, 3, 4, 5, 6, 7, 8.
And at time 8, we also knew exactly what
we were going to sell it for.
Well, this wouldn't be so hard, because we've already
seen valuation techniques for exactly this.
So I'm going to type Time.
And this is 1.
I'm going to type a 2, Enter.
And I'm going to highlight this and then
drag it down, down to 8.
Now I'm going to type a--
this will be our time.
We'll do our present value of future cash flows.
This would be the cash flow.
And we pretty much have exactly what we want here--
equals, and I'm going to click right there.
That's the cash flow one year out, a dividend.
All the way down to 7.
It's just 8, we're going to have this cash flow,
and we're going to sell the stock.
So here I'm going to do Alt-Equals and then
highlight these two right here.
All right, so there's our cash flow.
And now we could-- these are future cash flows.
So now we could take the present value of all these equals PV.
And the rate-- our required return is 15%.
That's our discounting rate.
That can be tricky to calculate sometimes for stocks.
We'll see later in this class some methods
of trying to figure out what the required return is.
NPER, we have that.
It's a relative cell reference.
PMT, no-- comma to skip over that and our future value.
The type is going to be end.
By default, it knows end, so we leave it out.
Control-Enter.
Double click and send it down.
Now I can come down here and Alt-Equals for AutoSum.
And then down here I could say PO.
And I'm going to show you this great trick.
See how we have these subscripts,
and sometimes you want superscripts?
You highlight this, and you got to go
to the Font group, which is Control-Shift-F. And then click
that right there.
I do Alt-B. I do this all the time, so I know
Control-Shift-F, Alt-B, Enter.
And so stock at time zero, because all these future cash
flows were discounted back, I'm going to say equals minus this.
And let's see.
I'm highlighting all those.
I'm going to add some formatting.
By the way, I highlighted all these non-contiguous areas
using the Control key.
Right there I'm going to pick some color.
All right, so that's the hypothetical situation.
There are some problems with using future cash
flows from a stock.
And I'm sure you could think of what some of the problems
are, problems with using dividends
for future cash flows.
The board may or may not declare dividends.
Remember-- we talked about this last video or the video
before--
the dividends are dependent on the board
of directors declaring them and then paying them out.
It's not like debt where interest payment for debt
is a contractual obligation.
You have to pay it.
The board made declare them.
They may not.
Now, with publicly traded companies,
companies when they say they're going to pay them,
they like to pay them, because it's a bad sign when
they don't pay them.
And usually they're pretty predictable.
So you definitely can for some companies
that look predictable.
You can use future dividends.
All right, but that's problem number one.
Problem number two, the board can declare whatever amount
they would like.
There is no contractual amount.
It's not like debt that says you will pay 5%.
Now again, companies like to be pretty predictable.
And the ones that are, you can use
the discounting future dividend cash payment method.
But these are two problems.
And then finally, the third one is, what future price would you
use?
We just guess, basically.
Oh, it says, OK, eight years from now,
it's going to be selling for $25.
Yeah, right, like we know that for sure.
But this concept will be helpful for us.
Let's go over to our PDFs, which also can be downloaded.
And I want to look at this.
If you do, if there is a way-- and again,
we will see a few situations where dividends
can be used to value the stock.
But here's the hypothetical-- price at time zero.
We have dividend 1, 2, 3, dot, dot, dot, all the way out
to some future point.
Here's the actual price we're going to sell the stock for way
in the future.
All right, if I blow this up, so this
is our price way in the future.
And this is our--
actually, I can already see that's our discount rate.
It actually should be an exponent here.
But the point here is, at some point
when this gets way in the future-- because oh yeah,
stocks have an unlimited life.
It's not like a bond contract that says 30 years.
Stocks hypothetically can go on forever.
So in developing a model, a math model,
to calculate present value of future cash flows for a stock,
they assume that at some point this
is going to be way, way in the future.
And when you discount it back, it gets close to zero.
So what they say is then the model gets reduced down
to just this, the price of the stock at time zero
is just all of the future dividends discounted back.
So that's the concept here.
And some people say, well, what about the stocks
that don't pay dividends?
Well, guess what?
That's just like a financial black hole.
There's no way.
Eventually companies have to pay dividends.
Would you make an investment in something
that would never pay cash?
Now, then the retort is, well, it's
just I'm going to hold it for a while and then sell it.
And I'm never going to--
but still, at some point in the future,
they have to pay dividends, or it just
is like a financial black hole.
So ultimately, the problem is, can we
predict future dividends?
Well, we're going to see three situations where we can.
In the next three videos, we'll cover these.
A constant dividend, it just pays $2.00, $2.00, $2,00.
Oh yeah, we already talked about that last video
or two videos ago.
Preferred stock does have a stated amount that it pays.
Another situation-- constant growth.
Companies, some predictable companies,
tend to have a predictable growth rate for their dividend.
So we can use this model.
And finally, super normal growth-- that just means you
increase in some abnormal pattern.
But then at some point in the future, you steady off.
And then you grow at a constant rate.
All right, so in our next few videos,
we'll see the math models for these.
And again, the caveat is, it's all estimation,
because there is with stocks-- we just don't have a contract.
The cash flows are not guaranteed.
But for some companies, these models do work.
All right, we'll see you next video.