Walter and Gordon Dividend Model in MS Excel (Asst. Prof) #BCH #BCP — Transcript
Full transcript
- 0:00A very good morning to one and all. I am
- 0:02Dr. Rishab Gupta from Shahid Bhagat
- 0:04Singh College, University of Delhi. And
- 0:07in this video, we shall be practicing
- 0:09the numericals of Walter and Gordon's
- 0:12division model in MSXL. So let's begin.
- 0:16Following are the details regarding the
- 0:19ABC limited. So return is 8%, 10%, and
- 0:2415%.
- 0:26Cost of equity is again 10%. earning per
- 0:29share is rupees chen per share.
- 0:32Calculate the value of the equity share
- 0:34of ABC limited using Walters and
- 0:38Gordon's model at different rate of
- 0:40return when the dividend payout ratio is
- 0:440% 40%
- 0:4780% and 100%. So based upon this
- 0:51information I need to calculate the
- 0:53price of equity share through Walter's
- 0:55model as well as through Gordon's model
- 0:57in MSXL.
- 0:59So let's begin. In this case the D
- 1:02represents dividend payout ratio which
- 1:05is 0%
- 1:070.4
- 1:090.8
- 1:10and 100%. Now I want this information to
- 1:14be presented in the form of a list. I
- 1:17need to put my cursor over here and then
- 1:20I need to click on data then data
- 1:24validation
- 1:25and then again I need to click on list
- 1:29then I need to select the values which I
- 1:32wish to highlight in those list. So
- 1:34select these range of values that is 0
- 1:370.4
- 1:390.8 and 1 and click okay.
- 1:47Similarly, the rate of return in this
- 1:49question is 0.08,
- 1:540.10
- 1:56and 0.15.
- 1:58Now again want this information to be
- 2:00presented in the same way that we have
- 2:02highlighted in dividend payout ratio. So
- 2:05I again need to click on this cell and
- 2:07then go to data data validation
- 2:11then click on list and select these
- 2:14values that is 0.08
- 2:170.10 and 0.15
- 2:20and click okay.
- 2:25Now cost of equity in this numerical is
- 2:270.10
- 2:29earning per share is 10 rupees. Now we
- 2:32all know that for calculating the price
- 2:35of an equity share using Walter's model
- 2:38the formula is dividend per share
- 2:42divided by cost of equity plus return
- 2:47divided by cost of equity multiplied by
- 2:50earning per share minus dividend per
- 2:53share whole divided by cost of equity.
- 2:56So in this case my dividend per share is
- 2:58nothing but a multiplication of dividend
- 3:00payout ratio and the earning per share.
- 3:04So equals to the dividend payout ratio
- 3:07is represented by B1 cell multiplied by
- 3:12the earning per share.
- 3:15Now dividend per share divided by K
- 3:18which is equals to
- 3:20C11
- 3:22divided by
- 3:240.10 1 0 it comes out to be 40 rupees.
- 3:28So in this way I have calculated the
- 3:30first component that is 40 rupees of
- 3:32this Walter's module. The second is the
- 3:35component R divided by K * E minus G
- 3:40whole divided by K. So I have divided
- 3:43into different components. So R K equals
- 3:47to
- 3:48B5 divided by 0.1.
- 3:53It comes out to be 0.8. 8
- 3:56earning per share is 10 rupees
- 3:59represented by B9 minus dividend per
- 4:03share represented by C11. The value
- 4:06comes out to be 6 rupees that is E minus
- 4:08D.
- 4:11The next is R by K multiplied by E minus
- 4:14G that is C113
- 4:17multiplied by the subsequent cell that
- 4:20is C14.
- 4:22It comes out to be 4.8. take now whole
- 4:25this entire value must be divided by K
- 4:32that is 48 rupees so adding both these
- 4:34figures 40 rupees plus 48 so in this way
- 4:39I have calculated the price of an equity
- 4:42share using the Walters model wherein
- 4:46the division payout ratio is 40% and the
- 4:49rate of return is 8%. Let me enter this
- 4:53information here. Now if the rate of
- 4:55return is 80% and the dividend payout
- 4:58ratio is 0% the value comes out to be 80
- 5:02rupees. So I need to enter this
- 5:04information here in this cell.
- 5:08Similarly
- 5:10if the dividend payout ratio is 80% and
- 5:14the rate of return is again 8% the value
- 5:18comes out to be 96 rupees.
- 5:21And lastly
- 5:23if it is 100% the value comes out to be
- 5:27100 rupees. The second column wherein
- 5:30the rate of return is 10%.
- 5:35Let me select the rate of return as 10%.
- 5:39Now again if the dividend payout ratio
- 5:42is zero your answer is again 100.
- 5:47If the dividend payout ratio is 40%.
- 5:50It would be again 100.
- 5:55If it is 80 then again your answer will
- 5:57be 100. And finally even if it is 100%.
- 6:03Your answer will remains the same that
- 6:06is 100 rupees.
- 6:08Now move on to the next column wherein
- 6:10the rate of return is 15%.
- 6:18So if the rate of return is 15% and the
- 6:20dividend payout ratio is zero, it comes
- 6:23out to be 150.
- 6:26Similarly, if the dividend payout ratio
- 6:29is 40%, then the value is 130.
- 6:35If it is 80%, the value is 110.
- 6:40And finally if it is 100% then the value
- 6:43comes out to be 100 rupees. Now based
- 6:47upon this information we can draw
- 6:49certain conclusion. So for the growing
- 6:52firm wherein the rate of return is
- 6:55greater than cost of equity that is the
- 6:58last case wherein r is equals to 15%.
- 7:02And cost of equity is 10%. We can see
- 7:05that the market price is highest in this
- 7:08cell where the dividend payout ratio is
- 7:110%.
- 7:12Similarly, with the increase in the
- 7:14payout ratio from 0 to 40 to 80 and
- 7:18100%. We can see a fall in the market
- 7:22price of the share from 150 to 130
- 7:28and finally 100 rupees. The second
- 7:30conclusion is for the declining firm
- 7:33whose rate of return is 8% and the cost
- 7:36of equity is 10%. So if the R is less
- 7:40than K then it is a case of declining
- 7:43firm. We can again see that the market
- 7:46price is highest when the dividend
- 7:48payout ratio is 100%. And with the fall
- 7:52in the dividend payout ratio from 100 to
- 7:5580 to 40 and zero the market price keeps
- 7:59on declining. So market price is lowest
- 8:01when the dividend payout ratio is zero
- 8:04and it is highest when the dividend
- 8:07payout ratio is 100%.
- 8:10The third case it's a case of a normal
- 8:12firm where the rate of return is equals
- 8:15to cost of equity. we can see that the
- 8:18firm does not have an optimum payout
- 8:21ratio and every payout ratio is as good
- 8:24as any other. So in this way we can
- 8:27solve the numerical of Walter's model in
- 8:30MSXL. Now let's move on to the next
- 8:33segment that is the Gordon's model. The
- 8:35formula for the guidance model is
- 8:38earning per share multiplied by 1 minus
- 8:41B wherein the B represents the retention
- 8:44ratio divided by cost of equity minus
- 8:48growth rate. Growth rate or we can say
- 8:51retention ratio multiplied by rate of
- 8:53return that is B R. So here the 1 minus
- 8:56B is nothing but your dividend payout
- 8:58ratio which is equals to
- 9:02B1.
- 9:04Similarly, E 1 minus B. So, earning per
- 9:07share is 10 rupees represented by B9
- 9:12multiplied by 0.4
- 9:15represented by I1. Press enter. It comes
- 9:19out to be 4 rupees. Now BR here the BR
- 9:23is equals to
- 9:261 minus division payout ratio
- 9:31multiplied by the return that is 0.08.
- 9:35It comes out to be 0.048.
- 9:38So retention ratio is nothing but equals
- 9:41to 1 minus dividend payout ratio. And
- 9:44the last one is K minus BR that is the
- 9:47denominator part of this formula. So
- 9:49equals to B8 minus 0.048
- 9:56it comes out to be 0.052.
- 9:58Now putting all this information in this
- 10:00formula of Gordian's model
- 10:03numerator 4 rupees divided by 0.052
- 10:09the value comes out to be 77 rupees
- 10:11approximately.
- 10:13Let me enter here.
- 10:16Now if the dividend payout ratio is zero
- 10:19then the value of price of shares using
- 10:23Gordon's model comes out to be zero.
- 10:27If it is 40% then the value is 77
- 10:30rupees.
- 10:33If it is 80% then the value is
- 10:35approximately 95 rupees. If it is 100%
- 10:39then the value is 100 rupees. Similarly
- 10:43if the rate of return is 10%. So let me
- 10:46select the rate of return as 10%.
- 10:50And if the dividend payout ratio is zero
- 10:53then it is showing an error which means
- 10:56not defined.
- 11:02If the payout ratio is 40% then the
- 11:05value is 100 rupees.
- 11:08If the payout ratio is 80% then the
- 11:11value is again 100 rupees. And finally
- 11:14even if the payout ratio is 100 rupees
- 11:17then the value will remains the same
- 11:18that is 100 rupees.
- 11:22Now move on to the last column wherein
- 11:24the rate of return is 15%.
- 11:29So if the dividend payout ratio is zero
- 11:32then again your answer will be zero.
- 11:35If the payout ratio is 40% then it will
- 11:38be 400.
- 11:42If the payout ratio is 80% then it will
- 11:45be 104.3
- 11:50and if the payout ratio is 100% the
- 11:53answer will be 100. So based upon this
- 11:56information we can draw certain
- 11:58conclusion. Conclusion one is that if a
- 12:02firm adopts a zero dividend payout
- 12:04policy then the investor may not be
- 12:07willing to offer any price. So for the
- 12:09growing firm whose rate of return that
- 12:12is 15% in this this case is greater than
- 12:16the cost of equity that is 10%. Which is
- 12:20also greater than the growth rate of
- 12:234.8% or 0.048.
- 12:26Then we can see in this column that the
- 12:29market price decreases with the increase
- 12:32in the dividend payout ratio that is
- 12:34from 400 goes down 214 and again goes
- 12:39down 200 rupees. Similarly for the
- 12:42declining firm whose rate of return is
- 12:458% which is less than the cost of equity
- 12:49of 10% but greater than the growth rate
- 12:52of 4.8%.
- 12:54we can see the increase in the dividend
- 12:57payout ratio the price of an equity
- 13:00share also increases. So the market
- 13:03price increases with the increase in the
- 13:06payout ratio. And finally the last case
- 13:10where the R is equals to K that is R in
- 13:13this case it's 10%. Which is also equals
- 13:17to cost of equity but it is greater than
- 13:20the growth rate of 4.8%.
- 13:23then the division policy becomes
- 13:25irrelevant and the market price remains
- 13:28100 and all the remaining scenarios of
- 13:3140 80 and 100%. While if it is 0% then
- 13:36we can't define it. So that's all from
- 13:38my side. We'll meet you in my next
- 13:40session. Have a nice day.
About this transcript
This page contains the full transcript of Walter and Gordon Dividend Model in MS Excel (Asst. Prof) #BCH #BCP by GYAN PRASAR KENDRA , generated from the public captions YouTube serves with the video. The transcript has 1,716 words across 261 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.
What you can do with it
Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.
Free YouTube transcript tool
YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.