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.

**Table of Contents**hide

## Download Practice Workbook

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

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

### 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**.

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

- Total Interest →

- Next, type the following formula in cell
.*C6*

`=C4/C5`

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

**Read More: ****How to Calculate Current Portion Of Long Term Debt 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**”.

**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%).

- Cost of Debt After Tax →

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

`=1-C5`

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

`=C4/C6`

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

**Similar Readings**

**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**

### 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:**

- At 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
, we will find this value.*the RATE function* - Before Tax Cost of Debt → Our aim is to find this value.

- Par Value →

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

`=C6/2*C4`

- Then, type another formula in cell
.*C9*

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

- After that, press
.*ENTER*

**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
and press*C10*to find our desired value.*ENTER*

`=C9*2`

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

## Practice Section

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

## 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. Moreover, you can visit our site **ExcelDemy** for more Excel-related articles. Thanks for reading, keep excelling!