Debt capital is the amount of money that a company or organization borrows from lenders or investors to finance its operations or projects. Debt capital has a cost, which is the interest rate that the borrower has to pay to the lender. The cost of debt capital can affect the value of the company and its weighted average cost of capital (WACC).
To calculate the cost of debt capital in Excel, we can use the following formula:
Cost of Debt = (1 – Tax Rate) * Interest Expense
Where:
- Tax Rate is the effective tax rate of the company
- Interest Expense is the annual interest payment on the debt
For example, if a company has $100,000 of debt and pays $6,000 of interest each year, and its tax rate is 25%, then its cost of debt is:
Cost of Debt = (1 – 0.25) * 6,000 Cost of Debt = 0.75 * 6,000 Cost of Debt = 4,500
To express the cost of debt as a percentage, we can divide it by the amount of debt:
Cost of Debt (%) = Cost of Debt / Debt Cost of Debt (%) = 4,500 / 100,000 Cost of Debt (%) = 0.045
To convert the decimal to a percentage, we can multiply it by 100 or use the percentage format in Excel:
Cost of Debt (%) = 0.045 * 100 Cost of Debt (%) = 4.5%
Alternatively, we can use the RATE function in Excel to calculate the cost of debt. The RATE function returns the interest rate per period of an annuity, which is a series of fixed payments over a certain period. The syntax of the RATE function is:
RATE(nper, pmt, pv, [fv], [type], [guess])
Where:
- nper is the total number of payment periods
- pmt is the payment amount per period
- pv is the present value of the annuity
- fv is the future value of the annuity (optional, default is 0)
- type is the payment type: 0 for end of period, 1 for beginning of period (optional, default is 0)
- guess is an initial estimate of the interest rate (optional, default is 0.1)
For example, if a company issues a 10-year bond with a face value of $100,000 and a coupon rate of 6%, and its tax rate is 25%, then we can use the RATE function to calculate its cost of debt as follows:
- nper is 10, since the bond has 10 years to maturity
- pmt is -6,000, since the bond pays $6,000 of interest each year (negative sign indicates cash outflow)
- pv is 100,000, since the bond has a face value of $100,000
- fv is 0, since the bond has no residual value at maturity
- type is 0, since the bond pays interest at the end of each year
- guess is 0.1, as an initial estimate of the interest rate
Using the RATE function, we get:
Cost of Debt = RATE(10, -6000, 100000, 0, 0, 0.1) Cost of Debt = 0.06
This is the before-tax cost of debt. To get the after-tax cost of debt, we multiply it by (1 – Tax Rate):
Cost of Debt = 0.06 * (1 – 0.25) Cost of Debt = 0.045
To convert the decimal to a percentage, we can multiply it by 100 or use the percentage format in Excel:
Cost of Debt (%) = 0.045 * 100 Cost of Debt (%) = 4.5%
We can see that the result is the same as the previous method.
To illustrate the use of the cost of debt formula in Excel, let us consider the following scenario:
A company wants to finance a new project that requires an initial investment of $500,000 and is expected to generate cash flows of $100,000 per year for 10 years. The company has two options to raise the funds: issue a 10-year bond with a face value of $500,000 and a coupon rate of 8%, or issue 50,000 shares of common stock at $10 per share. The company’s tax rate is 30%, and its cost of equity is 12%. Which option should the company choose to minimize its WACC and maximize its project value?
To answer this question, we need to calculate the WACC for each option and compare them. The WACC is the weighted average of the cost of debt and the cost of equity, and it reflects the overall cost of capital for the company. The formula for the WACC is:
WACC = (We x Ke) + (Wd x Kd)
Where:
- We is the proportion of equity in the capital structure
- Ke is the cost of equity
- Wd is the proportion of debt in the capital structure
- Kd is the cost of debt
Using Excel, we can create a table to calculate the WACC for each option as follows:
| Option | Debt | Equity | Total | We | Ke | Wd | Kd | WACC |
|---|---|---|---|---|---|---|---|---|
| Bond | 500,000 | 0 | 500,000 | 0 | 12% | 1 | 5.6% | 5.6% |
| Stock | 0 | 500,000 | 500,000 | 1 | 12% | 0 | 0% | 12% |
To calculate the cost of debt for the bond option, we use the RATE function as follows:
Kd = RATE(10, -40000, 500000, 0, 0, 0.1) * (1 – 0.3) Kd = 0.08 * (1 – 0.3) Kd = 0.056
To calculate the proportion of debt and equity for each option, we divide the debt and equity by the total capital:
We = Equity / Total Wd = Debt / Total
To calculate the WACC for each option, we use the WACC formula:
WACC = (We x Ke) + (Wd x Kd)
From the table, we can see that the bond option has a lower WACC of 5.6%, compared to the stock option, which has a WACC of 12%. This means that the bond option is cheaper and more efficient for the company to finance its project. Therefore, the company should choose the bond option to minimize its WACC and maximize its project value.
