YouTube2Text

SUMPRODUCT() in Excel Explained | Cost of Capital and Capital Structure — Transcript

by srccan Academy · 2,023 words · 348 segments · language en · Watch on YouTube

Full transcript

  1. 0:00Question one. A firm is financed 70% by
  2. 0:03equity costing 14% a year and 30% by
  3. 0:06debt costing 9% a year before tax.
  4. 0:09The tax rate is 25%.
  5. 0:11What is the firm's weighted average cost
  6. 0:13of capital, its WACC?
  7. 0:16Take a second and think.
  8. 0:18What do you expect the answer to be?
  9. 0:20Pause here if you like and try it
  10. 0:21yourself.
  11. 0:23Right. Let us solve it together in
  12. 0:24Excel.
  13. 0:28First, the function. We use SUMPRODUCT =
  14. 0:32SUMPRODUCT open bracket weights, {comma}
  15. 0:35costs close bracket.
  16. 0:37Weights are the share of each source in
  17. 0:39total capital in cells B2 to B3, 70%
  18. 0:43equity, 30% debt.
  19. 0:45Costs are the cost of each source after
  20. 0:47tax in cells D2 to D3. 14% equity, 6.75%
  21. 0:54debt.
  22. 0:55So, the live formula just points at
  23. 0:56those cells. = SUMPRODUCT B2 to B3,
  24. 1:01{comma} D2 to D3, close bracket.
  25. 1:04SUMPRODUCT multiplies each weight by its
  26. 1:07cost then adds in one step with no
  27. 1:10helper column.
  28. 1:11And one honest note about this video,
  29. 1:14what you see is rendered Excel look
  30. 1:15frames, not a genuine screen recording,
  31. 1:18but every figure is real, checked in
  32. 1:20Python and in Calc, and the narration is
  33. 1:23a computer voice. Let us open the Q1
  34. 1:25sheet and set it up just like you would.
  35. 1:28Equity weight in B2, 70%. Debt weight in
  36. 1:32B3, 30%. Total weights in B4, = SUM B2
  37. 1:38to B3, close bracket. 100% as it must
  38. 1:42be.
  39. 1:43Equity cost in C2, 14% a year. Debt cost
  40. 1:47before tax in C3, 9% a year.
  41. 1:51Tax rate in B5, 25%.
  42. 1:54After tax equity cost in D2, = C2, 14%.
  43. 2:00Because equity no tax shield.
  44. 2:02After-tax debt cost in D3 equals C3 open
  45. 2:06bracket 1 minus B5 close bracket. 9% *
  46. 2:1175% which is 6.75%
  47. 2:15Good. The inputs are ready. Now I click
  48. 2:17on cell B6 and type the formula. Equals
  49. 2:21SUMPRODUCT B2 to B3 {comma} D2 to D3
  50. 2:26{close bracket} Then I press enter.
  51. 2:29There it is. 11.83%
  52. 2:3211.825%
  53. 2:33before rounding. And the manual check in
  54. 2:36row seven agrees exactly.
  55. 2:3870% of 14% is 9.8%
  56. 2:4230% of 6.75%
  57. 2:45is 2.025%
  58. 2:479.8 + 2.025
  59. 2:50is 11.825
  60. 2:52The function and the hand calculation
  61. 2:54match. That is the point.
  62. 2:58So what is WACC really?
  63. 3:00It is a weighted average. Nothing more
  64. 3:02mysterious than that.
  65. 3:04A firm is paid for by its sources of
  66. 3:05capital and each source has its own
  67. 3:07price.
  68. 3:09Equity costs 14% here. Debt costs 6.75%
  69. 3:13after tax.
  70. 3:14WACC blends those prices giving each
  71. 3:17source a weight equal to its share of
  72. 3:19total capital.
  73. 3:20That is exactly what SUMPRODUCT does. It
  74. 3:23multiplies each weight by its cost then
  75. 3:25adds the results.
  76. 3:2770% * 14% + 30% * 6.75%
  77. 3:32in a single step.
  78. 3:34And the order matters. Debt enters after
  79. 3:36tax because interest is tax deductible.
  80. 3:40At 25% tax, 9% before tax is only 6.75%
  81. 3:45after tax.
  82. 3:46And that one adjustment is the whole
  83. 3:48difference between the right WACC and
  84. 3:50the wrong one. Question two. A second
  85. 3:53firm is financed 60% by equity costing
  86. 3:5614% a year and 40% by debt costing 9% a
  87. 4:00year before tax.
  88. 4:01The tax rate is still 25%.
  89. 4:04What is its WACC?
  90. 4:06Take a second and think.
  91. 4:08More debt this time. Do you expect a
  92. 4:10higher or a lower answer?
  93. 4:12Pause here if you like and try it
  94. 4:13yourself.
  95. 4:17The function again with the new weights
  96. 4:19equals sum product open bracket weights
  97. 4:22{comma} costs close bracket.
  98. 4:25Weights in B2 to B3 60% equity 40% debt.
  99. 4:31After tax costs in D2 to D3 14% equity
  100. 4:356.75%
  101. 4:37debt.
  102. 4:38The live formula is the same shape as
  103. 4:40before equals sum product B2 to B3
  104. 4:44{comma} D2 to D3 close bracket. Only the
  105. 4:48inputs changed. The formula did not. On
  106. 4:50the Q2 sheet equity weight in B2 60%
  107. 4:55debt weight in B3 40%
  108. 4:58total weights in B4 equals SUM B2 to B3
  109. 5:03100% again.
  110. 5:04Equity cost in C2 14%
  111. 5:08debt cost before tax in C3 9%
  112. 5:12tax rate in B5 25%
  113. 5:15after tax equity cost in D2 equals C2
  114. 5:1914%.
  115. 5:20After tax debt cost in D3 equals C3 open
  116. 5:24bracket 1 minus B5 close bracket. 6.75%
  117. 5:29as before. I click on cell B6 and type
  118. 5:33equals sum product B2 to B3 {comma} D2
  119. 5:37to D3 close bracket then enter. 11.10%
  120. 5:43The manual check agrees. 60% of 14% is
  121. 5:478.4%.
  122. 5:4840% of 6.75%
  123. 5:51is 2.7%
  124. 5:53and 8.4 plus 2.7 is 11.10.
  125. 5:57More debt, cheaper debt. So a lower WACC
  126. 6:01than question one.
  127. 6:02Now watch what happens when we change
  128. 6:04the mix itself.
  129. 6:06Let us change one input live. I change
  130. 6:09the equity weight in B2 from 60% to 50%
  131. 6:12and the debt weight in B3 from 40% to
  132. 6:1550%. Notice, the weights still sum to
  133. 6:18100%, 50 + 50. I've not broken the rule.
  134. 6:22I have rebalanced within it. Total
  135. 6:24weights in B4 still reads 100% and the
  136. 6:28WACC falls to 10.38%
  137. 6:3110.375%
  138. 6:33before rounding. The manual check. 50%
  139. 6:36of 14% is 7%. 50% of 6.75%
  140. 6:40is 3.375%
  141. 6:43and 7 + 3.375
  142. 6:45is 10.375.
  143. 6:48This is the why of cell referencing.
  144. 6:50Change one input cell and every answer
  145. 6:52that points at it updates itself. No
  146. 6:55retyping, no new formula.
  147. 6:58Question three, firm A.
  148. 7:01Firm A is financed 80% by equity and 20%
  149. 7:04by debt. Firm B is financed 40% by
  150. 7:07equity and 60% by debt.
  151. 7:10Both pay 14% on equity and 9% on debt
  152. 7:13before tax and both pay tax at 25%.
  153. 7:17What is firm A's WACC?
  154. 7:20Think first, then watch.
  155. 7:25The function again, equals sumproduct B2
  156. 7:28to B3, {comma} D2 to D3, {close
  157. 7:32bracket}. Weights in B2 to B3, 80%
  158. 7:36equity, 20% debt.
  159. 7:38After-tax costs in D2 to D3, 14% equity,
  160. 7:436.75%
  161. 7:44debt. On the firm A sheet, equity weight
  162. 7:47in B2, 80%. Debt weight in B3, 20%.
  163. 7:52Total weights in B4, 100%.
  164. 7:56Equity cost in C2, 14%. Debt cost before
  165. 8:00tax in C3, 9%. Tax rate in B5, 25%.
  166. 8:06After-tax equity cost in D2, 14%.
  167. 8:10After-tax debt cost in D3, 6.75%.
  168. 8:15I click on cell B6 and type equals
  169. 8:18sumproduct B2 to B3, {comma} D2 to D3,
  170. 8:22{close bracket}. Then, enter. Firm A,
  171. 8:2612.55%.
  172. 8:28Manual check. 80% of 14% is 11.2%.
  173. 8:3320% of 6.75%
  174. 8:36is 1.35%.
  175. 8:38And 11.2 + 1.35
  176. 8:41is 12.55.
  177. 8:43A mostly equity firm pays mostly the
  178. 8:45expensive source, so its WACC is high.
  179. 8:49Now, firm B.
  180. 8:52Question three. Firm B.
  181. 8:54Same costs as firm A. Equity 14% a year,
  182. 8:58debt 9% a year before tax, tax 25%.
  183. 9:02But firm B is financed 40% by equity and
  184. 9:0560% by debt.
  185. 9:07What is firm B's WACC?
  186. 9:10And which firm is cheaper to finance?
  187. 9:12Think first, then watch.
  188. 9:17The function again, equals sumproduct B2
  189. 9:21to B3, {comma} D2 to D3, {close
  190. 9:24bracket}.
  191. 9:25Weights in B2 to B3, 40% equity, 60%
  192. 9:29debt.
  193. 9:30After-tax costs in D2 to D3, 14% equity,
  194. 9:356.75%
  195. 9:36debt. On the firm B sheet, equity weight
  196. 9:39in B2, 40%.
  197. 9:42Debt weight in B3, 60%.
  198. 9:45Total weights in B4, 100%.
  199. 9:48Equity cost in C2, 14%.
  200. 9:52Debt cost before tax in C3, 9%.
  201. 9:56Tax rate in B5, 25%.
  202. 9:59After-tax equity cost in D2, 14%.
  203. 10:03After-tax debt cost in D3, 6.75%.
  204. 10:08I click on cell B6 and type equals sum
  205. 10:11product B2 to B3 {comma} D2 to D3 {close
  206. 10:16bracket} then enter. Firm B 9.65%
  207. 10:21manual check 40% of 14% is 5.6%
  208. 10:2760% of 6.75%
  209. 10:29is 4.05%
  210. 10:32and 5.6 + 4.05 is 9.65
  211. 10:37lower than firm A's 12.55%
  212. 10:42The verdict? Firm B is cheaper to
  213. 10:44finance by 2.9 percentage points because
  214. 10:47after-tax debt at 6.75%
  215. 10:50costs less than half of equity at 14%.
  216. 10:53That is the whole logic of the graph you
  217. 10:55will see next. As the debt weight rises,
  218. 10:57WACC falls step-by-step from 14% at no
  219. 11:02debt to 6.75%
  220. 11:04at all debt.
  221. 11:05But here's the honest caveat. The lowest
  222. 11:08WACC is not automatically the best
  223. 11:10capital structure.
  224. 11:12More debt also means more risk. Fixed
  225. 11:15interest must be paid even in a bad year
  226. 11:17and lenders will eventually charge more
  227. 11:19or refuse to lend at all.
  228. 11:21So use WACC to price capital, not to
  229. 11:24pick a capital structure on its own. A
  230. 11:27cheaper WACC bought with dangerous debt
  231. 11:30is no bargain. Now the traps. Trap one
  232. 11:33on the traps sheet. Weights of 80%
  233. 11:36equity and 30% debt
  234. 11:38total weights in B4 reads 110%
  235. 11:42not 100.
  236. 11:43And yet sum product still returns a
  237. 11:45number
  238. 11:4613.23%
  239. 11:48in red.
  240. 11:49It looks like an answer and it is not a
  241. 11:51WACC at all.
  242. 11:54Re-normalized 80 over 110 30 over 110
  243. 11:59the true WACC is 12.02%.
  244. 12:03Always check row four. Total weights
  245. 12:05must read 100%. Two more traps to dodge.
  246. 12:09Trap two. Debt before tax.
  247. 12:12For question one using the 9% pre-tax
  248. 12:14debt cost gives 70% of 14% plus 30% of
  249. 12:189% 12.50%
  250. 12:22not 11.825%
  251. 12:25That 0.675 point error is the whole tax
  252. 12:28shield thrown away.
  253. 12:30Debt always enters after tax in column
  254. 12:32D.
  255. 12:33Trap three, ranges of different sizes.
  256. 12:36Equals sumproduct B2 to B3, {comma} D2
  257. 12:41to D4. Two weights against three costs
  258. 12:44returns the error hash value in Excel.
  259. 12:47Both ranges must cover the same sources
  260. 12:49in the same order or the function
  261. 12:51refuses to answer. Now the graph. Step
  262. 12:54one, select the debt weights with their
  263. 12:56WACC values. Step two, click the insert
  264. 13:00tab then charts. Step three, choose line
  265. 13:03not column because this is a profile
  266. 13:06across weights, a line that falls.
  267. 13:08Step four, Excel inserts the chart. Now
  268. 13:11replace the placeholder title. Finished.
  269. 13:14A proper title, access titles, and a
  270. 13:16label on every point. Here it is.
  271. 13:19WACC against debt weight, a line chart.
  272. 13:23At 0% debt, WACC is 14% pure equity.
  273. 13:29At 20% debt, 12.55%
  274. 13:33At 40%, 11.10%
  275. 13:37At 60%, 9.65%
  276. 13:40At 80%, 8.20%
  277. 13:44At 100% debt, 6.75%
  278. 13:48pure after-tax debt.
  279. 13:50Every point is a sumproduct of that mix
  280. 13:53and the line falls smoothly because
  281. 13:55after-tax debt is cheaper than equity at
  282. 13:57every step. Takeaway, weighted average
  283. 13:59cost of capital equals sumproduct B2 to
  284. 14:03B3, {comma} D2 to D3, {close bracket}
  285. 14:07weights in column B, after-tax costs in
  286. 14:09column D. One step, no helper column.
  287. 14:13WACC is a weighted average. Question
  288. 14:16one, 70% of 14% plus 30% of 6.75%
  289. 14:22is 11.825%.
  290. 14:25Debt enters after tax. 9% before tax at
  291. 14:2825% tax is 6.75%
  292. 14:32after tax. Using the pre-tax cost gives
  293. 14:3512.50%.
  294. 14:37Wrong by the whole tax shield. Question
  295. 14:39two, 60% equity and 40% debt gives
  296. 14:4311.10%
  297. 14:45and moving to 50/50 drops WACC
  298. 14:48to 10.38%.
  299. 14:51Weights still summing to 100%.
  300. 14:53Firm B at 9.65%
  301. 14:56is cheaper to finance than firm A at
  302. 14:5912.55%
  303. 15:01but more debt also means more risk. So,
  304. 15:03the lowest WACC is not automatically
  305. 15:06best. And the traps? Weights must sum to
  306. 15:09100%. Debt must be after tax and both
  307. 15:13sum product ranges must be the same size
  308. 15:15in the same order.
  309. 15:17One last honest note, this video uses
  310. 15:19rendered Excel look frames, not a
  311. 15:21genuine screen recording, but every
  312. 15:23figure was verified and the companion
  313. 15:25workbook lets you check them yourself.
  314. 15:28Your turn. Try it yourself. A firm is
  315. 15:30financed 50% by equity costing 15% a
  316. 15:33year and 50% by debt costing 10% a year
  317. 15:36before tax. The tax rate is 30%. What is
  318. 15:40its WACC?
  319. 15:42Pause here and work it out. The answer
  320. 15:44follows after the pause.
  321. 15:50The function one more time equals sum
  322. 15:52product B2 to B3, {comma} D2 to D3,
  323. 15:56close bracket. Weights in B2 to B3, 50%
  324. 16:01equity, 50% debt. After tax costs in D2
  325. 16:05to D3, 15% equity and debt after tax,
  326. 16:0910% times 70%, 7%. On the your turn
  327. 16:13sheet, equity weight in B2, 50%. Debt
  328. 16:17weight in B3 50%
  329. 16:20Total weights in B4 100%
  330. 16:23Equity cost in C2 15%
  331. 16:27Debt cost before tax in C3 10%
  332. 16:31Tax rate in B5 30% After tax equity cost
  333. 16:36in D2 15%
  334. 16:38After tax debt cost in D3 = C3 (1 - B5)
  335. 16:457%
  336. 16:47I click on cell B6 and type =
  337. 16:50SUMPRODUCT(B2:B3,D2:D3)
  338. 16:57then enter. The answer? 11% After tax
  339. 17:01debt cost is 7% Manual check? 50% of 15%
  340. 17:06is 7.5%
  341. 17:0850% of 7% is 3.5%
  342. 17:11and 7.5 + 3.5 is 11% Did you get the
  343. 17:15same?
  344. 17:16If you did, you now own SUMPRODUCT for
  345. 17:19WACC.
  346. 17:20Weights, after tax costs, one function,
  347. 17:23and always check that the weights sum to
  348. 17:25100%.

About this transcript

This page contains the full transcript of SUMPRODUCT() in Excel Explained | Cost of Capital and Capital Structure by srccan Academy, generated from the public captions YouTube serves with the video. The transcript has 2,023 words across 348 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.