How to Calculate Cost of Debt in Excel (3 Simple Ways)

Debt financing and equity financing are two important types of financing. Any business that wants to operate must obtain loans from a variety of sources. On that loan, the corporation must pay interest. The Cost of Debt is the term used to describe this actual interest rate. We often consider the cost of debt after taxes. However, we can use Microsoft Excel to perform these calculations. We’ll walk you through three quick ways to calculate cost of debt in Excel. Tax deductions are available for debt payments made after taxes, but not for those made before taxes.

Download Practice Workbook

What Is Cost of Debt?

The effective interest rate that a business pays on its debts, such as bonds and loans, is known as the cost of debt. The cost of debt may be expressed as either the before-tax cost of debt, which is the amount owed by the business before taxes, or the after-tax cost of debt. The fact that interest expenses are tax deductible accounts for the majority of the difference between the cost of debt before and after taxes.

Formula to Calculate Cost of Debt

The Cost of Debt can be calculated by just a simple formula. The formula is stated below:

We are gonna use this formula to calculate the cost of debt in the later part.

3 Simple Ways to Calculate Cost of Debt in Excel

1. Applying General Formula to Calculate Cost of Debt

As there is a direct formula for the cost of debt, we can use it with the necessary particulars to find the cost of debt value. This method is explained in the following section.

Steps:

• Gather the information related to loans or bonds and organize them in a dataset. Here, I have created a dataset with the Amount of Loan, Company Tax Rate, Interest Rate, and Interest Expenses columns.

• Now, input the following formula to calculate the cost of debt.

Here,
C5 = Company Tax Rate
C7 = Interest Expenses

• Finally, press the ENTER button to have the cost of debt.

This is just a piece-of-cake method to have our desired output.

Read More: How to Calculate Current Portion Of Long Term Debt in Excel

2. Calculate Cost of Debt Using Total Interest and Total Debt

Another very simple way to calculate the cost of debt is by using the total amount of interest and debt. In this case, we will have the cost of debt value in percentage.

Steps:

• Collect the information on Total Debt and Total Interest.

• Apply the formula mentioned below to have the cost of debt value.

Here,
C5 = Total Interest
C4 = Total Debt

• Press ENTER to have the value in decimal.

• Now, go to the Home tab.
• Click on the Percentage option from the ribbon to convert it into percentage.

Read More: How to Calculate Annual Debt Service in Excel (3 Ideal Examples)

3. Applying Excel RATE Function

We can also apply the RATE Function to calculate the cost of debt both before tax and after tax time. Here, we will also have the cost of debt value in percentage.

Steps:

• Collect the information on Par Value, Current Market Price, Coupon Rate, and Terms.
• Next, input the following formula to have the Interest Expenses after a fixed period (i.e. Half Yearly Interest Expenses).

Here,
C6 = Coupon Rate
C4 = Par Value

• Hit the ENTER button to have the Half Yearly Interest Expenses.

• Then, apply the following formula to have the Half Yearly Interest Rate.

C7 = Terms
As the interest rate is compounded every six months, we have multiplied the terms by two.
C8 = Semi-annual interest expense
C5 = Current Market Price
As this is a cash outflow, this value is negative.
C4 = Par Value

• Now, press ENTER to have the Half Yearly Interest Rate.

• Again, apply this formula to have the Before Tax Cost of Debt.

Here, the Half Yearly Interest Rate is multiplied by 2.

• Press ENTER to have the output.

• Consider the Company Tax Rate (i.e. 27%).
• After that, apply the following formula to have the After Tax Cost of Debt

Here,
C11 = Company Tax Rate
C10 =  Before Tax Cost of Debt

• Finally, press ENTER to have the result.

Read More: How to Create Debtors Ageing Report in Excel Format

Practice Section

You can practice here inputting the necessary particulars to have the Cost of Debt value.

Conclusion

That’s all for this article. In the end, I like to add that I have tried to explain three quick ways to calculate cost of debt in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our Exceldemy site for more details on Excel.

Related Articles

• How to Make Credit Card Debt Reduction Calculator for Excel
• How to Use Debt to Equity Ratio Formula in Excel (3 Examples)
• Debt Snowball VS Debt Avalanche Method in Excel Spreadsheet
• Debt to Income Ratio Calculator in Excel (Create with Easy Steps)
• How to Create Pay off Credit Card Debt Calculator in Excel
• Debt Service Coverage Ratio Formula in Excel