Welcome to Finance and Excel video number 64.
And if you want to download this workbook for chapter 7,
click on the link directly below the video,
and you scroll all the way down to the Finance Excel class
section and you can download this workbook.
Hey, in this video here--
actually the last video, we talked
about the dividend growth model for pricing or valuing a stock.
And in this video, we want to see
how to chart because we have our model
and we want to see what happens to the price
as the constant growth rate increases and then we'll
do a second chart and see what happens
when we change the required--
let's say just required-- what happens when there were
required return rate changes.
So in essence, we're going to see
how one of the inputs for our model changes
and what that does to the stock price.
So first we have to build a little table.
We have 0% to 19%.
Remember the growth rate for this model
is assumed to be less than the required return,
so we only go to 19.
And we build our little model here.
It's always going to be, hey, the dividend at time 1--
and I'm going to lock that with the F4 key--
divided by open parentheses R--
assumed R, 20%, and I'm going to hit
the F4 key minus R growth rate.
And that'll be a relative [INAUDIBLE],, and so
as we copy down that'll be the input that
changes Control Enter and then double click and send it down.
Sorry about that.
So what's happening here, you can clearly
see as the growth rate increases, the price increases.
Now let's go ahead and plot this.
I'm going to click right here and use control asterisk.
That just highlights the whole current table,
asterisk on the number pad.
Then I'm going to go to insert.
And we have number-- so two numbers,
so I'm going to do a scatterplot chart.
And I'm going to select this one right here.
And just as you predicted, we could
see the pattern is price goes up, and as it gets closer--
as we get closer and closer to--
the growth rate gets closer to the required return,
it skyrockets in price.
Now let's make this a little bit better.
I'm going to click here and delete key to get rid of that.
I want to actually make this a little bit smaller.
I'm going to point to the edge.
When I see that diagonal black cursor,
I'm going to hold Shift and drag in.
Whoops.
Scroll over here.
And now I want to do three things to this chart.
I want to click on the title, and I actually
want these words to appear in the title.
So I'm going to click up in the for-- click on the label, click
in the formula bar, type equals, and then
click on the cell you want.
Now with this edge selected, I'm going
to go up here and change the font to 12.
Now I want to label here and here,
so to go up to the context sensitive chart tools layout.
I'm going to go to access titles horizontal.
I'm going to say below.
And this is going to be our-- same trick.
We're going to highlight that, click up here, equals,
and click on that cell right there, enter.
Now you can see that's showing up there.
Axis titles vertical, I'm to say this one.
Click in formula bar equals and stock price.
So we can see, and then you could print this out
or do whatever you want.
But there it is.
We can clearly-- our label--
our chart labels are properly labeled.
Actually we could click here control one
to open up the format axis.
I'm going to go to number, and I'm
going to say 0 decimal places.
I can maybe even scroll this down here.
So that looks a little bit better there.
Now that's how the price changes as the constant growth
rate changes.
Let's go and see what happens to R.
We'll do the same thing here.
Equals-- we'll do our model, dividend at time 1
and F4 to lock it divided by open parentheses.
Oh, this is going to be the relative cell reference.
That's the rate minus RG, and there I'm
going to hit the F4 key to lock it, close parentheses, Control
Enter, double click, and send it down.
I'm going to highlight the whole table control asterisk.
I'm going to go off to insert, scatter.
I'm going to select this one, point to the edge,
click and drag, click there and delete, click there,
and we'll do our same thing equals that enter.
Change the font, I'm going to change it to 10.
I need to add some labels here, layout axis.
I'll do the horizontal equal--
click up in the front of the bar equal required return.
Axis vertical, we'll do this one.
I going to say click there equals current stock price.
Maybe I can point to the edge and make a little bit taller.
Click on their control 1, number 0.
I'm going to hit Tab to move to the next one.
Notice the axis already is changed but watch this.
Now I'm going to click down here,
and now I can change the number for that, too.
I maybe should have done that on the other one, too.
I should have had 0 there.
Tab, now we can see that is working.
I'm going to click Close.
I'm going to click here and then change it to 8.
So we see the idea that--
oh, here's another thing.
We don't-- remember we had to start our required return above
the G, so we're never going to get anything below 5 here
on this one.
So we can change this axis.
I'm going to control 1, and I'm going
to go to the minimum fixed.
I'm going to say--
How about 0.05 I'm going to hit Tab and see what that does.
That works just fine.
Close.
So it starts at 5, goes over there.
So as we-- this is the required return.
And just like we saw in earlier chapters,
as you increase the discount rate,
the amount of interest that you take out
when you discount future cash flows is greater,
so the current price is smaller.
So as this increases the required return,
stock price reduces.
And that makes perfect sense.
This is the discounting rate, so as the discounting rate
increases, the price decreases.
Oh, we saw this relationship most prominently
in the bond chapter, but it's true for any discounting.
As the discount rate goes up, the price or the value
or the current value goes down.
Next video, we'll look at some--
still using the dividend growth model,
looking at some more complicated examples.
See you next video.