Welcome and Excel video number 67.
Hey, if you want to download this workbook for chapter 7
or the PDFs for chapter 7, click on the link directly
below the video and scroll all the way down
to the Excel finance class section.
In this video, we're going to use the--
look at a non-constant growth problem.
And we're going to combine the model we've been using,
the constant dividend growth model, and some discounting
future cash flow techniques into one, because ultimately if you
can figure out the future cash flows,
you can discount them back given some assumed discount rate.
So here's our example.
Suppose a firm expected to increase dividends
at 20% in one year, 15% in two years.
After that, dividends will increase at a rate
of 5% per year indefinitely.
If the last dividend was $1, which
means given a time zero on the required return is 20%,
what is the price of the stock?
Remember, as with any asset, if you can figure out
the future cash flows, you can discount them back
and value the asset.
All right.
Let's look at some calculations and then a picture,
and then we'll go over to Excel and see
if we can calculate this.
Well, first off, we can calculate the dividends.
We know the dividend at time 0, so we can just
multiply that by a principal plus the interest.
We got 120.
So at time 1 year out, we have that cash flow.
Year 2, we multiply this one times
the 15% increase, then we take this one
and finally we get dividend at time 3
at the 5% growth, which is assumed
to continue into the future.
Now guess what?
With this dividend here-- this is at d 3, time 3,
we can figure out the price at time 2.
So price at time 2 is dividend 3.
So we simply plug that in, subtract this, and get that.
Now we have all of our cash flows.
We have cash flows here, and at time 2, we have a dividend
and we have a price.
So now we discount these back.
Here's the picture.
We have this one, so we're going to have to discount it back
using our normal techniques.
This one.
And the only trick was, you had to figure out
once you have a dividend here, you
need to-- at the constant rate-- go forward one
ahead, calculate the price at time 2, right?
So we have this cash flow and this cash flow,
and we discount them back.
Add them all up, and that should be our price.
All right.
Let's go over to Excel.
So dividend at time 1, our first rate, second rate, third rate,
and our discount rate.
So dividend at time 2.
I'm going to say $1 times open parentheses 1 plus our 20%.
Now I'm going to notice something--
I have a 15 right there.
That green box should move down, but I want that one locked,
so I'm gonna hit F4.
Actually, I don't know what I'm thinking.
Never mind.
Forget I said that.
Just hit Enter.
All right.
There's our 120.
Now we can take that dividend, and since the next period
we're assuming a 15%, we can say times this one plus 15%.
So we have dividend 1, 2, and finally
we need to go out to time 3 with our dividend
to get price at time 2.
So we take that.
And I'm going to go 1 plus the constant assumed dividend
increase rate.
Now we can calculate our price at time 2,
because any particular price--
time t, as long as we know the next dividend, which we do,
we can get that.
Next dividend, divided by open parentheses
required return, minus our assumed growth rate.
Now we have cash flow here and then
we need to add this cash flow and this one.
So cash flow at time 1, enter.
Cash flow at time 2, that dividend,
plus the assumed price at time 2.
Now just a note about this--
remember our model assumes that all future dividends can
be used to get a current price or price at time t,
so when we calculate this price at time 2 right here,
It really does incorporate all of the future dividends, which
are assumed to go out indefinitely.
So even though we have a price here,
it's time 2 number that assumes that it
has information or the price about all future dividends.
So really even though we're using
a price, dividend 2, dividend 1, it's still all dividends
that we're using to value this.
So now we can take two present values--
and we can do this a few different ways.
I'm going to do it this way.
I'm going to say the present value, the rate, NPER.
I don't have a 1 here anywhere.
I'm actually going to cheat.
Watch this.
I'm going to put--
No, I'm not.
I'll put a 1 and a 2, because we're
going to need those numbers, equals present value.
The rate at 20% discount rate.
Our NPER.
Wow.
For this one, it's going to be 1.
Comma, no payment, but there is a future value
and that's the dividend.
So then I close-- and it's a close parentheses-- and then
plus.
And we have to do PV again.
We're taking the present value of two separate things.
So I'm going to say rate comma time, NPER, comma, comma,
future value.
That's it.
This one.
You know, I'm going to change this one right here.
I want to keep this consistent.
I have the cash flows here, so--
instead of that one right there, I'm going to click right here.
And then we have our visiting future cash flow extra.
What do you think about using future dividends
to value stocks?
Can I tell you something?
Yeah, you have the secret stock pick you
want to tell everyone about?
Yeah.
What is it?
What's the secret stock?
I made a word called stick, stick pen.
Stick pen?
Wow.
So you guys hear that?
That's straight from my four-year-old son.
He says go out and buy stick pen.
I don't believe any analyst when they try to sell me something.
All right.
I'm going to hit Enter, and that's a minus.
And it should be-- because this is a cash flow going out
for us--
buying a stock.
So $8.67.
All right.
Let's do another example.
I'm gonna go over to sheet eight.
Here's our situation.
We have rad company here.
Shares outstanding are 500,000.
We are going to assume that for the next three years,
it's going to be high growth, and that high growth
rate for dividends will be 20%.
After that, it'll tail off and we
are going to assume a constant 5% growth rate.
Dividends just paid were one million bucks.
Our required return is 15%.
Can we calculate the price per share?
All right.
Let's go ahead and we've got dividend 1, 2, 3, 4.
3-- that's at this assume 20-- and then
we'll go out one period.
Dividend at time 4 using our 5%.
We'll discount that back using our model to get price 3.
Price 3 will, in essence, hold all future price information
about dividends, so we'll have all our dividends
and we'll do a present value calculation.
All right.
So dividend 1, we're gonna say a million bucks times--
oh, I'm actually going to lock this.
F4.
And then in parentheses, I'm going
to say 1 plus 0, that one.
And I'm going to lock that one also.
Now what we really need is this-- we need carat 1,
and then when we copy it down, it needs to go to exponent 1.
And then here it needs to be 2, and here it needs to be 3.
So we could actually do that, right?
Copy this down and then edit it.
But I want to try and figure out something a little bit sneakier
than that.
There's something called-- and you
can do it that way for the test or whatever, if you want--
but here, we're going to look at the rows function.
I'm going to exponent to the rows.
Well, rows tells you how many rows there are.
Well, look, I'm sitting in B9, so I'm going to type B9,
and then I'm going to hit the F4 key.
Actually, I shouldn't have hit the F4,
but then I'm going to hit a colon-- oh, yes, that
does work.
Then I'm going to hit a colon and type B9.
Now look at this.
This is weird.
One of the cell reference, this is
like a range in a sum function or average
or whatever-- one of the cell reference is locked,
one of them is not.
Well, let's go back to the purpose of the rows.
It says, how many rows are there from 9 to 9?
Well, the answer is 1.
But because this is locked and this is not,
when we copy this down, this will remain 9.
This will move to 10 and 11.
So this is a clever way of getting the number 1,
and then when we copy it down, it'll show 2.
When we copy it down again, it will show 3.
So let's try it.
Control enter, and I'm gonna drag it down.
Just out of curiosity, let's see if it does work.
Oh, 9.
That one's locked, that one's not.
So 9 to 11, there are three rows.
And you can highlight it and hit the F9
key to evaluate it and prove to yourself that it actually
did work.
Now I'm going to control z, because I don't
want to hard code that in.
All right.
Now that we have that, these will be cash flows
that will discount back.
But now we've got to go out one period to dividend 4
to get price at that time 3, so we
take that next period's dividend divided by our 15
minus our assumed constant rate after the year 3.
Oh, I'm sorry.
That's the next formula.
Just pretend I didn't say that.
Oh, yeah, now we have our dividend.
We need to multiply it times 1 plus the 5%.
So dividend at time 4--
we're just taking this one, adding 5% growth, Enter.
Now we can take or calculate our price at time 3
equals next dividend, divided by r minus g.
Now we're going to gather together our cash flow,
so there's that one.
Enter.
That one.
Enter.
This one, plus this one right here.
And notice, the stock doesn't cost $18 million.
When we do our discounting back, we're
going to divide them by the number of shares
and that'll give us our current price.
This actually is the price at time three for 500,000 shares.
All right.
So there's our cash flows.
Now we can simply say three present values in a row.
And actually next chapter, we will learn a new technique
when we do capital budgeting and we'll see a new function that
will make this whole process of multiple irregular cash flows
a little bit easier.
But for right now, this is what we have.
I'm gonna say present value, the rate-- that's the discount
rate-- comma, the NPER.
I'm clicking right there.
No payment.
Future value is this.
I'm gonna see if I can copy this.
Copy, control c.
Plus Control V, and then I have to change two things.
I have to double click that NPER and click there, double click
this and click there.
Plus Control V, and then I'm going to double click there.
That's the NPER, so it's that.
Double click there-- that's the future value--
and that.
All right.
So there's our present value of future cash flows.
We need to figure out the price per share,
so I'm going to say negative this
to get a positive, divided by 500,000.
$30.40.
All right.
That's the last video for chapter 7.
Next, chapter 8.
We'll get to start some capital budgeting-- that's
inside the firm doing discounted cash flow
analysis for various projects to see if they're worth taking on.
All right.
See you next chapter.