How to Calculate Before Tax Cost of Debt in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

There are two significant sources of financing: debt financing and equity financing. Any company needs to borrow from various sources to finance its operations. The company needs to pay interest on that debt. This effective interest rate is known as the cost of debt. Most of the time, we evaluate the cost of debt after tax. However, we will show you 3 quick methods to calculate before tax cost of debt in Excel. The after-tax cost of debt is tax deductible, but the before tax cost of debt is not.


How to Calculate Before Tax Cost of Debt in Excel: 3 Handy Approaches

We will show you the methods in 3 different data sets. Here is a quick view of one of the solutions.

How to Calculate Before Tax Cost of Debt in Excel


1. Calculating Before Tax Cost of Debt by Applying General Formula

We will use a simple formula for the first method. If we divide the total interest by the total debt, then we will find the before tax cost of debt.

How to Calculate Before Tax Cost of Debt in Excel 2

Steps:

  • To begin with, type the following data. We know the first 2 values.
    • Total Interest → $6,500.
    • Total Debt → $38,000.
    • Cost of Debt → We will calculate this by dividing $6,500 by $38,000.

How to Calculate Before Tax Cost of Debt in Excel 3

  • Next, type the following formula in cell C6.

=C4/C5

How to Calculate Before Tax Cost of Debt in Excel 4

  • Then, press ENTER.
  • Lastly, change the Number Format to Percentage.

How to Calculate Before Tax Cost of Debt in Excel 5

Read More: How to Use Debt to Equity Ratio Formula in Excel


2. Calculating Before Tax Cost from After Tax Cost of Debt

In this section, we know the after-tax cost of debt and will find the amount of before tax cost of debt using that information. If we divide the “After Tax Cost of Debt” by the (1-Company Tax Rate), then we can find the “Before Tax Cost of Debt”.

How to Calculate Before Tax Cost of Debt in Excel 6

Steps:

  • Firstly, type the following things:
    • Cost of Debt After Tax → $135,640.
    • Company Tax Rate (CTR) → 35%.
    • 1 – CTR → We will find this by deducting 1 from 35%.
    • Cost of Debt Before Tax → This is our goal. We will see this value by dividing $135,640 by 65% (from 1-35%).

How to Calculate Before Tax Cost of Debt in Excel 7

  • Secondly, type the following formula in cell C6 and press ENTER.

=1-C5

How to Calculate Before Tax Cost of Debt in Excel 8

  • Thirdly, type another formula in cell C7 to find the cost of debt before tax.

=C4/C6

How to Calculate Before Tax Cost of Debt in Excel 9

  • Lastly, press ENTER and we will find the cost of debt before applying any taxes.

How to Calculate Before Tax Cost of Debt in Excel 10


3. Using RATE Function to Calculate Before Tax Cost of Debt

For the last method, we will use the RATE function to find the half-yearly interest rate and use that value to calculate the before tax cost of debt.

Steps:

  • First, type these values:
    • Par Value → $1,000.
    • Current Market Price → $1,050.
    • Coupon Rate → 8.15%.
    • Terms (Years) → 10.
    • Half Yearly Interest Rate → We will find this value by dividing the Coupon Rate by the Par Value.
    • Half Yearly Interest Rate → Using the RATE function, we will find this value.
    • Before Tax Cost of Debt → Our aim is to find this value.

How to Calculate Before Tax Cost of Debt in Excel 11

  • Afterward, type this formula in cell C8 and press ENTER. Here, we have divided by 2 as the interest is semi-annually compounded.

=C6/2*C4

Using RATE Function

  • Then, type another formula in cell C9.

=RATE(C7*2,C8,-C5,C4)

Applying Function

  • After that, press ENTER.

Function Output

Formula Breakdown

  • Firstly, we have multiplied the terms by 2 as the interest rate is semi-annually compounded.
  • Then, we input the half-yearly interest expense.
  • After that, the current market price is entered as a present value. Moreover, this is a negative value because we are considering this as a cash outflow.
  • Finally, we insert the par value as the future value.
  • Lastly, type the following formula in cell C10 and press ENTER to find our desired value.

=C9*2

Another Function


Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

Practice Dataset


Download Practice Workbook


Conclusion

We have shown you 3 quick ways to calculate before tax cost of debt in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo