About this lesson
SUMPRODUCT in Google Sheets: weighted averages, like a company's cost of debt
In Google Sheets, SUMPRODUCT multiplies two ranges row by row and adds the results, so =SUMPRODUCT(B2:B6,C2:C6) is the total interest on each facility's balance times its rate. Divide by the total, =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6), for a weighted average. A plain AVERAGE of the rates ignores how big each facility is.
Using Excel? Do this lesson in Excel.
=SUMPRODUCT(B2:B6,C2:C6)What you'll practise
Budget a freight group's interest bill: multiply and add in one step with SUMPRODUCT, turn it into a weighted average rate, weight only the floating facilities, then take tax off.
4 steps · 40 points · the cells outlined in gold below
- SUMPRODUCT
- weighted average
- SUMIF
- cost of debt
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Facility | Drawn ($m) | Rate | Type |
| 2 | Term loan A | 250.0 | 5.8% | Floating |
| 3 | Term loan B | 180.0 | 6.4% | Floating |
| 4 | Revolving facility | 95.0 | 6.9% | Floating |
| 5 | Medium-term notes | 350.0 | 4.4% | Fixed |
| 6 | Equipment finance | 62.0 | 7.2% | Fixed |
| 7 | ||||
| 8 | Annual interest ($m) | |||
| 9 | Weighted average rate | |||
| 10 | Floating facilities only | |||
| 11 | Company tax rate | 30% | ||
| 12 | After-tax cost of debt |