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:

Cost of Debt = (1 - Tax Rate) * Interest Expense

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.

How to Calculate Cost of Debt in Excel

  • Now, input the following formula to calculate the cost of debt.
=(1-C5)*C7

Here,
C5 = Company Tax Rate
C7 = Interest Expenses

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

How to Calculate Cost of Debt in Excel

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.
=C5/C4

Here,
C5 = Total Interest
C4 = Total Debt

How to Calculate Cost of Debt in Excel

  • 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.

How to Calculate Cost of Debt in Excel

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).
=C6/2 * C4

Here,
C6 = Coupon Rate
C4 = Par Value

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

How to Calculate Cost of Debt in Excel

  • Then, apply the following formula to have the Half Yearly Interest Rate.
=RATE(C7*2,C8,-C5,C4)

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.

How to Calculate Cost of Debt in Excel

  • Again, apply this formula to have the Before Tax Cost of Debt.
=C9*2

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

  • Press ENTER to have the output.

How to Calculate Cost of Debt in Excel

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

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

  • Finally, press ENTER to have the result.

How to Calculate Cost of Debt in Excel

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

 

 

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo