SUMPRODUCT() in Excel Explained | Cost of Capital and Capital Structure — Transcript
Full transcript
- 0:00Question one. A firm is financed 70% by
- 0:03equity costing 14% a year and 30% by
- 0:06debt costing 9% a year before tax.
- 0:09The tax rate is 25%.
- 0:11What is the firm's weighted average cost
- 0:13of capital, its WACC?
- 0:16Take a second and think.
- 0:18What do you expect the answer to be?
- 0:20Pause here if you like and try it
- 0:21yourself.
- 0:23Right. Let us solve it together in
- 0:24Excel.
- 0:28First, the function. We use SUMPRODUCT =
- 0:32SUMPRODUCT open bracket weights, {comma}
- 0:35costs close bracket.
- 0:37Weights are the share of each source in
- 0:39total capital in cells B2 to B3, 70%
- 0:43equity, 30% debt.
- 0:45Costs are the cost of each source after
- 0:47tax in cells D2 to D3. 14% equity, 6.75%
- 0:54debt.
- 0:55So, the live formula just points at
- 0:56those cells. = SUMPRODUCT B2 to B3,
- 1:01{comma} D2 to D3, close bracket.
- 1:04SUMPRODUCT multiplies each weight by its
- 1:07cost then adds in one step with no
- 1:10helper column.
- 1:11And one honest note about this video,
- 1:14what you see is rendered Excel look
- 1:15frames, not a genuine screen recording,
- 1:18but every figure is real, checked in
- 1:20Python and in Calc, and the narration is
- 1:23a computer voice. Let us open the Q1
- 1:25sheet and set it up just like you would.
- 1:28Equity weight in B2, 70%. Debt weight in
- 1:32B3, 30%. Total weights in B4, = SUM B2
- 1:38to B3, close bracket. 100% as it must
- 1:42be.
- 1:43Equity cost in C2, 14% a year. Debt cost
- 1:47before tax in C3, 9% a year.
- 1:51Tax rate in B5, 25%.
- 1:54After tax equity cost in D2, = C2, 14%.
- 2:00Because equity no tax shield.
- 2:02After-tax debt cost in D3 equals C3 open
- 2:06bracket 1 minus B5 close bracket. 9% *
- 2:1175% which is 6.75%
- 2:15Good. The inputs are ready. Now I click
- 2:17on cell B6 and type the formula. Equals
- 2:21SUMPRODUCT B2 to B3 {comma} D2 to D3
- 2:26{close bracket} Then I press enter.
- 2:29There it is. 11.83%
- 2:3211.825%
- 2:33before rounding. And the manual check in
- 2:36row seven agrees exactly.
- 2:3870% of 14% is 9.8%
- 2:4230% of 6.75%
- 2:45is 2.025%
- 2:479.8 + 2.025
- 2:50is 11.825
- 2:52The function and the hand calculation
- 2:54match. That is the point.
- 2:58So what is WACC really?
- 3:00It is a weighted average. Nothing more
- 3:02mysterious than that.
- 3:04A firm is paid for by its sources of
- 3:05capital and each source has its own
- 3:07price.
- 3:09Equity costs 14% here. Debt costs 6.75%
- 3:13after tax.
- 3:14WACC blends those prices giving each
- 3:17source a weight equal to its share of
- 3:19total capital.
- 3:20That is exactly what SUMPRODUCT does. It
- 3:23multiplies each weight by its cost then
- 3:25adds the results.
- 3:2770% * 14% + 30% * 6.75%
- 3:32in a single step.
- 3:34And the order matters. Debt enters after
- 3:36tax because interest is tax deductible.
- 3:40At 25% tax, 9% before tax is only 6.75%
- 3:45after tax.
- 3:46And that one adjustment is the whole
- 3:48difference between the right WACC and
- 3:50the wrong one. Question two. A second
- 3:53firm is financed 60% by equity costing
- 3:5614% a year and 40% by debt costing 9% a
- 4:00year before tax.
- 4:01The tax rate is still 25%.
- 4:04What is its WACC?
- 4:06Take a second and think.
- 4:08More debt this time. Do you expect a
- 4:10higher or a lower answer?
- 4:12Pause here if you like and try it
- 4:13yourself.
- 4:17The function again with the new weights
- 4:19equals sum product open bracket weights
- 4:22{comma} costs close bracket.
- 4:25Weights in B2 to B3 60% equity 40% debt.
- 4:31After tax costs in D2 to D3 14% equity
- 4:356.75%
- 4:37debt.
- 4:38The live formula is the same shape as
- 4:40before equals sum product B2 to B3
- 4:44{comma} D2 to D3 close bracket. Only the
- 4:48inputs changed. The formula did not. On
- 4:50the Q2 sheet equity weight in B2 60%
- 4:55debt weight in B3 40%
- 4:58total weights in B4 equals SUM B2 to B3
- 5:03100% again.
- 5:04Equity cost in C2 14%
- 5:08debt cost before tax in C3 9%
- 5:12tax rate in B5 25%
- 5:15after tax equity cost in D2 equals C2
- 5:1914%.
- 5:20After tax debt cost in D3 equals C3 open
- 5:24bracket 1 minus B5 close bracket. 6.75%
- 5:29as before. I click on cell B6 and type
- 5:33equals sum product B2 to B3 {comma} D2
- 5:37to D3 close bracket then enter. 11.10%
- 5:43The manual check agrees. 60% of 14% is
- 5:478.4%.
- 5:4840% of 6.75%
- 5:51is 2.7%
- 5:53and 8.4 plus 2.7 is 11.10.
- 5:57More debt, cheaper debt. So a lower WACC
- 6:01than question one.
- 6:02Now watch what happens when we change
- 6:04the mix itself.
- 6:06Let us change one input live. I change
- 6:09the equity weight in B2 from 60% to 50%
- 6:12and the debt weight in B3 from 40% to
- 6:1550%. Notice, the weights still sum to
- 6:18100%, 50 + 50. I've not broken the rule.
- 6:22I have rebalanced within it. Total
- 6:24weights in B4 still reads 100% and the
- 6:28WACC falls to 10.38%
- 6:3110.375%
- 6:33before rounding. The manual check. 50%
- 6:36of 14% is 7%. 50% of 6.75%
- 6:40is 3.375%
- 6:43and 7 + 3.375
- 6:45is 10.375.
- 6:48This is the why of cell referencing.
- 6:50Change one input cell and every answer
- 6:52that points at it updates itself. No
- 6:55retyping, no new formula.
- 6:58Question three, firm A.
- 7:01Firm A is financed 80% by equity and 20%
- 7:04by debt. Firm B is financed 40% by
- 7:07equity and 60% by debt.
- 7:10Both pay 14% on equity and 9% on debt
- 7:13before tax and both pay tax at 25%.
- 7:17What is firm A's WACC?
- 7:20Think first, then watch.
- 7:25The function again, equals sumproduct B2
- 7:28to B3, {comma} D2 to D3, {close
- 7:32bracket}. Weights in B2 to B3, 80%
- 7:36equity, 20% debt.
- 7:38After-tax costs in D2 to D3, 14% equity,
- 7:436.75%
- 7:44debt. On the firm A sheet, equity weight
- 7:47in B2, 80%. Debt weight in B3, 20%.
- 7:52Total weights in B4, 100%.
- 7:56Equity cost in C2, 14%. Debt cost before
- 8:00tax in C3, 9%. Tax rate in B5, 25%.
- 8:06After-tax equity cost in D2, 14%.
- 8:10After-tax debt cost in D3, 6.75%.
- 8:15I click on cell B6 and type equals
- 8:18sumproduct B2 to B3, {comma} D2 to D3,
- 8:22{close bracket}. Then, enter. Firm A,
- 8:2612.55%.
- 8:28Manual check. 80% of 14% is 11.2%.
- 8:3320% of 6.75%
- 8:36is 1.35%.
- 8:38And 11.2 + 1.35
- 8:41is 12.55.
- 8:43A mostly equity firm pays mostly the
- 8:45expensive source, so its WACC is high.
- 8:49Now, firm B.
- 8:52Question three. Firm B.
- 8:54Same costs as firm A. Equity 14% a year,
- 8:58debt 9% a year before tax, tax 25%.
- 9:02But firm B is financed 40% by equity and
- 9:0560% by debt.
- 9:07What is firm B's WACC?
- 9:10And which firm is cheaper to finance?
- 9:12Think first, then watch.
- 9:17The function again, equals sumproduct B2
- 9:21to B3, {comma} D2 to D3, {close
- 9:24bracket}.
- 9:25Weights in B2 to B3, 40% equity, 60%
- 9:29debt.
- 9:30After-tax costs in D2 to D3, 14% equity,
- 9:356.75%
- 9:36debt. On the firm B sheet, equity weight
- 9:39in B2, 40%.
- 9:42Debt weight in B3, 60%.
- 9:45Total weights in B4, 100%.
- 9:48Equity cost in C2, 14%.
- 9:52Debt cost before tax in C3, 9%.
- 9:56Tax rate in B5, 25%.
- 9:59After-tax equity cost in D2, 14%.
- 10:03After-tax debt cost in D3, 6.75%.
- 10:08I click on cell B6 and type equals sum
- 10:11product B2 to B3 {comma} D2 to D3 {close
- 10:16bracket} then enter. Firm B 9.65%
- 10:21manual check 40% of 14% is 5.6%
- 10:2760% of 6.75%
- 10:29is 4.05%
- 10:32and 5.6 + 4.05 is 9.65
- 10:37lower than firm A's 12.55%
- 10:42The verdict? Firm B is cheaper to
- 10:44finance by 2.9 percentage points because
- 10:47after-tax debt at 6.75%
- 10:50costs less than half of equity at 14%.
- 10:53That is the whole logic of the graph you
- 10:55will see next. As the debt weight rises,
- 10:57WACC falls step-by-step from 14% at no
- 11:02debt to 6.75%
- 11:04at all debt.
- 11:05But here's the honest caveat. The lowest
- 11:08WACC is not automatically the best
- 11:10capital structure.
- 11:12More debt also means more risk. Fixed
- 11:15interest must be paid even in a bad year
- 11:17and lenders will eventually charge more
- 11:19or refuse to lend at all.
- 11:21So use WACC to price capital, not to
- 11:24pick a capital structure on its own. A
- 11:27cheaper WACC bought with dangerous debt
- 11:30is no bargain. Now the traps. Trap one
- 11:33on the traps sheet. Weights of 80%
- 11:36equity and 30% debt
- 11:38total weights in B4 reads 110%
- 11:42not 100.
- 11:43And yet sum product still returns a
- 11:45number
- 11:4613.23%
- 11:48in red.
- 11:49It looks like an answer and it is not a
- 11:51WACC at all.
- 11:54Re-normalized 80 over 110 30 over 110
- 11:59the true WACC is 12.02%.
- 12:03Always check row four. Total weights
- 12:05must read 100%. Two more traps to dodge.
- 12:09Trap two. Debt before tax.
- 12:12For question one using the 9% pre-tax
- 12:14debt cost gives 70% of 14% plus 30% of
- 12:189% 12.50%
- 12:22not 11.825%
- 12:25That 0.675 point error is the whole tax
- 12:28shield thrown away.
- 12:30Debt always enters after tax in column
- 12:32D.
- 12:33Trap three, ranges of different sizes.
- 12:36Equals sumproduct B2 to B3, {comma} D2
- 12:41to D4. Two weights against three costs
- 12:44returns the error hash value in Excel.
- 12:47Both ranges must cover the same sources
- 12:49in the same order or the function
- 12:51refuses to answer. Now the graph. Step
- 12:54one, select the debt weights with their
- 12:56WACC values. Step two, click the insert
- 13:00tab then charts. Step three, choose line
- 13:03not column because this is a profile
- 13:06across weights, a line that falls.
- 13:08Step four, Excel inserts the chart. Now
- 13:11replace the placeholder title. Finished.
- 13:14A proper title, access titles, and a
- 13:16label on every point. Here it is.
- 13:19WACC against debt weight, a line chart.
- 13:23At 0% debt, WACC is 14% pure equity.
- 13:29At 20% debt, 12.55%
- 13:33At 40%, 11.10%
- 13:37At 60%, 9.65%
- 13:40At 80%, 8.20%
- 13:44At 100% debt, 6.75%
- 13:48pure after-tax debt.
- 13:50Every point is a sumproduct of that mix
- 13:53and the line falls smoothly because
- 13:55after-tax debt is cheaper than equity at
- 13:57every step. Takeaway, weighted average
- 13:59cost of capital equals sumproduct B2 to
- 14:03B3, {comma} D2 to D3, {close bracket}
- 14:07weights in column B, after-tax costs in
- 14:09column D. One step, no helper column.
- 14:13WACC is a weighted average. Question
- 14:16one, 70% of 14% plus 30% of 6.75%
- 14:22is 11.825%.
- 14:25Debt enters after tax. 9% before tax at
- 14:2825% tax is 6.75%
- 14:32after tax. Using the pre-tax cost gives
- 14:3512.50%.
- 14:37Wrong by the whole tax shield. Question
- 14:39two, 60% equity and 40% debt gives
- 14:4311.10%
- 14:45and moving to 50/50 drops WACC
- 14:48to 10.38%.
- 14:51Weights still summing to 100%.
- 14:53Firm B at 9.65%
- 14:56is cheaper to finance than firm A at
- 14:5912.55%
- 15:01but more debt also means more risk. So,
- 15:03the lowest WACC is not automatically
- 15:06best. And the traps? Weights must sum to
- 15:09100%. Debt must be after tax and both
- 15:13sum product ranges must be the same size
- 15:15in the same order.
- 15:17One last honest note, this video uses
- 15:19rendered Excel look frames, not a
- 15:21genuine screen recording, but every
- 15:23figure was verified and the companion
- 15:25workbook lets you check them yourself.
- 15:28Your turn. Try it yourself. A firm is
- 15:30financed 50% by equity costing 15% a
- 15:33year and 50% by debt costing 10% a year
- 15:36before tax. The tax rate is 30%. What is
- 15:40its WACC?
- 15:42Pause here and work it out. The answer
- 15:44follows after the pause.
- 15:50The function one more time equals sum
- 15:52product B2 to B3, {comma} D2 to D3,
- 15:56close bracket. Weights in B2 to B3, 50%
- 16:01equity, 50% debt. After tax costs in D2
- 16:05to D3, 15% equity and debt after tax,
- 16:0910% times 70%, 7%. On the your turn
- 16:13sheet, equity weight in B2, 50%. Debt
- 16:17weight in B3 50%
- 16:20Total weights in B4 100%
- 16:23Equity cost in C2 15%
- 16:27Debt cost before tax in C3 10%
- 16:31Tax rate in B5 30% After tax equity cost
- 16:36in D2 15%
- 16:38After tax debt cost in D3 = C3 (1 - B5)
- 16:457%
- 16:47I click on cell B6 and type =
- 16:50SUMPRODUCT(B2:B3,D2:D3)
- 16:57then enter. The answer? 11% After tax
- 17:01debt cost is 7% Manual check? 50% of 15%
- 17:06is 7.5%
- 17:0850% of 7% is 3.5%
- 17:11and 7.5 + 3.5 is 11% Did you get the
- 17:15same?
- 17:16If you did, you now own SUMPRODUCT for
- 17:19WACC.
- 17:20Weights, after tax costs, one function,
- 17:23and always check that the weights sum to
- 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.