YouTube2Text

Walter and Gordon Dividend Model in MS Excel (Asst. Prof) #BCH #BCP — Transcript

by GYAN PRASAR KENDRA · 1,716 words · 261 segments · language en · Watch on YouTube

Full transcript

  1. 0:00A very good morning to one and all. I am
  2. 0:02Dr. Rishab Gupta from Shahid Bhagat
  3. 0:04Singh College, University of Delhi. And
  4. 0:07in this video, we shall be practicing
  5. 0:09the numericals of Walter and Gordon's
  6. 0:12division model in MSXL. So let's begin.
  7. 0:16Following are the details regarding the
  8. 0:19ABC limited. So return is 8%, 10%, and
  9. 0:2415%.
  10. 0:26Cost of equity is again 10%. earning per
  11. 0:29share is rupees chen per share.
  12. 0:32Calculate the value of the equity share
  13. 0:34of ABC limited using Walters and
  14. 0:38Gordon's model at different rate of
  15. 0:40return when the dividend payout ratio is
  16. 0:440% 40%
  17. 0:4780% and 100%. So based upon this
  18. 0:51information I need to calculate the
  19. 0:53price of equity share through Walter's
  20. 0:55model as well as through Gordon's model
  21. 0:57in MSXL.
  22. 0:59So let's begin. In this case the D
  23. 1:02represents dividend payout ratio which
  24. 1:05is 0%
  25. 1:070.4
  26. 1:090.8
  27. 1:10and 100%. Now I want this information to
  28. 1:14be presented in the form of a list. I
  29. 1:17need to put my cursor over here and then
  30. 1:20I need to click on data then data
  31. 1:24validation
  32. 1:25and then again I need to click on list
  33. 1:29then I need to select the values which I
  34. 1:32wish to highlight in those list. So
  35. 1:34select these range of values that is 0
  36. 1:370.4
  37. 1:390.8 and 1 and click okay.
  38. 1:47Similarly, the rate of return in this
  39. 1:49question is 0.08,
  40. 1:540.10
  41. 1:56and 0.15.
  42. 1:58Now again want this information to be
  43. 2:00presented in the same way that we have
  44. 2:02highlighted in dividend payout ratio. So
  45. 2:05I again need to click on this cell and
  46. 2:07then go to data data validation
  47. 2:11then click on list and select these
  48. 2:14values that is 0.08
  49. 2:170.10 and 0.15
  50. 2:20and click okay.
  51. 2:25Now cost of equity in this numerical is
  52. 2:270.10
  53. 2:29earning per share is 10 rupees. Now we
  54. 2:32all know that for calculating the price
  55. 2:35of an equity share using Walter's model
  56. 2:38the formula is dividend per share
  57. 2:42divided by cost of equity plus return
  58. 2:47divided by cost of equity multiplied by
  59. 2:50earning per share minus dividend per
  60. 2:53share whole divided by cost of equity.
  61. 2:56So in this case my dividend per share is
  62. 2:58nothing but a multiplication of dividend
  63. 3:00payout ratio and the earning per share.
  64. 3:04So equals to the dividend payout ratio
  65. 3:07is represented by B1 cell multiplied by
  66. 3:12the earning per share.
  67. 3:15Now dividend per share divided by K
  68. 3:18which is equals to
  69. 3:20C11
  70. 3:22divided by
  71. 3:240.10 1 0 it comes out to be 40 rupees.
  72. 3:28So in this way I have calculated the
  73. 3:30first component that is 40 rupees of
  74. 3:32this Walter's module. The second is the
  75. 3:35component R divided by K * E minus G
  76. 3:40whole divided by K. So I have divided
  77. 3:43into different components. So R K equals
  78. 3:47to
  79. 3:48B5 divided by 0.1.
  80. 3:53It comes out to be 0.8. 8
  81. 3:56earning per share is 10 rupees
  82. 3:59represented by B9 minus dividend per
  83. 4:03share represented by C11. The value
  84. 4:06comes out to be 6 rupees that is E minus
  85. 4:08D.
  86. 4:11The next is R by K multiplied by E minus
  87. 4:14G that is C113
  88. 4:17multiplied by the subsequent cell that
  89. 4:20is C14.
  90. 4:22It comes out to be 4.8. take now whole
  91. 4:25this entire value must be divided by K
  92. 4:32that is 48 rupees so adding both these
  93. 4:34figures 40 rupees plus 48 so in this way
  94. 4:39I have calculated the price of an equity
  95. 4:42share using the Walters model wherein
  96. 4:46the division payout ratio is 40% and the
  97. 4:49rate of return is 8%. Let me enter this
  98. 4:53information here. Now if the rate of
  99. 4:55return is 80% and the dividend payout
  100. 4:58ratio is 0% the value comes out to be 80
  101. 5:02rupees. So I need to enter this
  102. 5:04information here in this cell.
  103. 5:08Similarly
  104. 5:10if the dividend payout ratio is 80% and
  105. 5:14the rate of return is again 8% the value
  106. 5:18comes out to be 96 rupees.
  107. 5:21And lastly
  108. 5:23if it is 100% the value comes out to be
  109. 5:27100 rupees. The second column wherein
  110. 5:30the rate of return is 10%.
  111. 5:35Let me select the rate of return as 10%.
  112. 5:39Now again if the dividend payout ratio
  113. 5:42is zero your answer is again 100.
  114. 5:47If the dividend payout ratio is 40%.
  115. 5:50It would be again 100.
  116. 5:55If it is 80 then again your answer will
  117. 5:57be 100. And finally even if it is 100%.
  118. 6:03Your answer will remains the same that
  119. 6:06is 100 rupees.
  120. 6:08Now move on to the next column wherein
  121. 6:10the rate of return is 15%.
  122. 6:18So if the rate of return is 15% and the
  123. 6:20dividend payout ratio is zero, it comes
  124. 6:23out to be 150.
  125. 6:26Similarly, if the dividend payout ratio
  126. 6:29is 40%, then the value is 130.
  127. 6:35If it is 80%, the value is 110.
  128. 6:40And finally if it is 100% then the value
  129. 6:43comes out to be 100 rupees. Now based
  130. 6:47upon this information we can draw
  131. 6:49certain conclusion. So for the growing
  132. 6:52firm wherein the rate of return is
  133. 6:55greater than cost of equity that is the
  134. 6:58last case wherein r is equals to 15%.
  135. 7:02And cost of equity is 10%. We can see
  136. 7:05that the market price is highest in this
  137. 7:08cell where the dividend payout ratio is
  138. 7:110%.
  139. 7:12Similarly, with the increase in the
  140. 7:14payout ratio from 0 to 40 to 80 and
  141. 7:18100%. We can see a fall in the market
  142. 7:22price of the share from 150 to 130
  143. 7:28and finally 100 rupees. The second
  144. 7:30conclusion is for the declining firm
  145. 7:33whose rate of return is 8% and the cost
  146. 7:36of equity is 10%. So if the R is less
  147. 7:40than K then it is a case of declining
  148. 7:43firm. We can again see that the market
  149. 7:46price is highest when the dividend
  150. 7:48payout ratio is 100%. And with the fall
  151. 7:52in the dividend payout ratio from 100 to
  152. 7:5580 to 40 and zero the market price keeps
  153. 7:59on declining. So market price is lowest
  154. 8:01when the dividend payout ratio is zero
  155. 8:04and it is highest when the dividend
  156. 8:07payout ratio is 100%.
  157. 8:10The third case it's a case of a normal
  158. 8:12firm where the rate of return is equals
  159. 8:15to cost of equity. we can see that the
  160. 8:18firm does not have an optimum payout
  161. 8:21ratio and every payout ratio is as good
  162. 8:24as any other. So in this way we can
  163. 8:27solve the numerical of Walter's model in
  164. 8:30MSXL. Now let's move on to the next
  165. 8:33segment that is the Gordon's model. The
  166. 8:35formula for the guidance model is
  167. 8:38earning per share multiplied by 1 minus
  168. 8:41B wherein the B represents the retention
  169. 8:44ratio divided by cost of equity minus
  170. 8:48growth rate. Growth rate or we can say
  171. 8:51retention ratio multiplied by rate of
  172. 8:53return that is B R. So here the 1 minus
  173. 8:56B is nothing but your dividend payout
  174. 8:58ratio which is equals to
  175. 9:02B1.
  176. 9:04Similarly, E 1 minus B. So, earning per
  177. 9:07share is 10 rupees represented by B9
  178. 9:12multiplied by 0.4
  179. 9:15represented by I1. Press enter. It comes
  180. 9:19out to be 4 rupees. Now BR here the BR
  181. 9:23is equals to
  182. 9:261 minus division payout ratio
  183. 9:31multiplied by the return that is 0.08.
  184. 9:35It comes out to be 0.048.
  185. 9:38So retention ratio is nothing but equals
  186. 9:41to 1 minus dividend payout ratio. And
  187. 9:44the last one is K minus BR that is the
  188. 9:47denominator part of this formula. So
  189. 9:49equals to B8 minus 0.048
  190. 9:56it comes out to be 0.052.
  191. 9:58Now putting all this information in this
  192. 10:00formula of Gordian's model
  193. 10:03numerator 4 rupees divided by 0.052
  194. 10:09the value comes out to be 77 rupees
  195. 10:11approximately.
  196. 10:13Let me enter here.
  197. 10:16Now if the dividend payout ratio is zero
  198. 10:19then the value of price of shares using
  199. 10:23Gordon's model comes out to be zero.
  200. 10:27If it is 40% then the value is 77
  201. 10:30rupees.
  202. 10:33If it is 80% then the value is
  203. 10:35approximately 95 rupees. If it is 100%
  204. 10:39then the value is 100 rupees. Similarly
  205. 10:43if the rate of return is 10%. So let me
  206. 10:46select the rate of return as 10%.
  207. 10:50And if the dividend payout ratio is zero
  208. 10:53then it is showing an error which means
  209. 10:56not defined.
  210. 11:02If the payout ratio is 40% then the
  211. 11:05value is 100 rupees.
  212. 11:08If the payout ratio is 80% then the
  213. 11:11value is again 100 rupees. And finally
  214. 11:14even if the payout ratio is 100 rupees
  215. 11:17then the value will remains the same
  216. 11:18that is 100 rupees.
  217. 11:22Now move on to the last column wherein
  218. 11:24the rate of return is 15%.
  219. 11:29So if the dividend payout ratio is zero
  220. 11:32then again your answer will be zero.
  221. 11:35If the payout ratio is 40% then it will
  222. 11:38be 400.
  223. 11:42If the payout ratio is 80% then it will
  224. 11:45be 104.3
  225. 11:50and if the payout ratio is 100% the
  226. 11:53answer will be 100. So based upon this
  227. 11:56information we can draw certain
  228. 11:58conclusion. Conclusion one is that if a
  229. 12:02firm adopts a zero dividend payout
  230. 12:04policy then the investor may not be
  231. 12:07willing to offer any price. So for the
  232. 12:09growing firm whose rate of return that
  233. 12:12is 15% in this this case is greater than
  234. 12:16the cost of equity that is 10%. Which is
  235. 12:20also greater than the growth rate of
  236. 12:234.8% or 0.048.
  237. 12:26Then we can see in this column that the
  238. 12:29market price decreases with the increase
  239. 12:32in the dividend payout ratio that is
  240. 12:34from 400 goes down 214 and again goes
  241. 12:39down 200 rupees. Similarly for the
  242. 12:42declining firm whose rate of return is
  243. 12:458% which is less than the cost of equity
  244. 12:49of 10% but greater than the growth rate
  245. 12:52of 4.8%.
  246. 12:54we can see the increase in the dividend
  247. 12:57payout ratio the price of an equity
  248. 13:00share also increases. So the market
  249. 13:03price increases with the increase in the
  250. 13:06payout ratio. And finally the last case
  251. 13:10where the R is equals to K that is R in
  252. 13:13this case it's 10%. Which is also equals
  253. 13:17to cost of equity but it is greater than
  254. 13:20the growth rate of 4.8%.
  255. 13:23then the division policy becomes
  256. 13:25irrelevant and the market price remains
  257. 13:28100 and all the remaining scenarios of
  258. 13:3140 80 and 100%. While if it is 0% then
  259. 13:36we can't define it. So that's all from
  260. 13:38my side. We'll meet you in my next
  261. 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.