Saquib Ahmad Shuvo
If you are looking for some special tricks to use the interest coverage ratio formula in Excel, you’ve come to the right place. There are numerous ways to use the interest coverage ratio formula in Microsoft Excel. This article will discuss two methods to use the interest coverage ratio formula in Excel. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
What Is Interest Coverage Ratio?
The interest coverage ratio measures an entity’s ability, so that, interest payments on its debts can be made when they come due. An increasing ratio indicates a company’s ability to meet its interest expense obligations comfortably. In determining the company’s liquidity, lenders, such as banks and investors, use this ratio.
Types of Interest Coverage Ratio
There are two types of interest coverage ratios. The first type is obtained by dividing EBIT by Interest Expenses. And the second type is obtained by dividing EBITDA by Interest Expenses. The Formulas are given below:
- Interest Coverage Expense= (EBIT)/Interest Expense
- Interest Coverage Expense= (EBITDA)/ Interest Expense
Here, EBIT means Earnings before interest and tax. And EBITDA means earnings before interest, tax, depreciation, and amortization.
Significance of Interest Coverage Ratio
- An interest coverage ratio of 1 indicates that the company is in a risky condition. Financial institutions will never lend to these types of companies since they are in a poor financial situation.
- When a company pays interest on its debt, the interest coverage ratio must be greater than 1. If the interest coverage ratio is between 1 and 1.5, the company is in red alert condition.
Simple Example of Interest Coverage Ratio
Here, we are going to show the simple calculation of interest coverage. The following image shows a company’s net operating profit, interest income, and interest expense for 2020.
In order to use the interest coverage ratio formula of the company, we need to add net profiting income with interest income. Then divided it by interest expense. For the above data, we have to do the following calculation:
Interest Coverage Ratio=($20000+$4000)/$6000
As a result, we will get an interest coverage ratio of 4. This value means the above company’s profits are able to cover the interest 4 times.
2 Suitable Ways to Use Interest Coverage Ratio Formula in Excel
We will use two effective and tricky methods to use the interest coverage ratio formula in Excel. This section provides extensive details on the two methods. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge.
1. Using EBITDA Method
Here, we will demonstrate how to calculate the interest coverage ratio in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. The dataset provides details of the expenses and income of a company over the last four years. Our goal now is to calculate the company’s interest coverage ratio over the last four years. Let’s walk through the steps to calculate the interest coverage ratio.
📌 Steps:
- Firstly, enter the amount of money of the company in the Revenue, Operating Expenses, Interest Income, and Interest Expense section.
- Next, we want to calculate the total sales revenue of the company. Therefore, we will use the following formula in the cell C8:
=C6+C7
Here, cell C6 is the income of the company by the completion of the project. Cell C7 is the company’s other income.
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s total sales revenue over the last four years.
- Next, we want to calculate gross profit . Therefore, we will use the following formula in the cell C12:
=C10-C11
Here, cell C10 is the amount of the company’s total sales revenue. Cell C11 is the direct cost of the company.
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s gross profit over the last four years.
- Next, we want to calculate the total operating expenses of the company. Therefore, we will use the following formula in the cell C17:
=C14+C15+C16
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s total operating expenses over the last four years.
- Next, we want to calculate the net operating profit of the company. Therefore, we will use the following formula in the cell C21:
=C19-C20
Here, cell C19 is the amount of the company’s gross profit. Cell C20 is the total operating expenses of the company.
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s net operating profit over the last four years
- Next, we want to calculate the EBITDA of the company. Therefore, we will use the following formula in the cell C23:
=C21+C22
Here, cell C21 is the amount of the company’s net operating profit. Cell C22 is the interest income of the company.
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s EBITDA over the last four years
- Next, we want to calculate the Interest Coverage Ratio of the company. Therefore, we will use the following formula in the cell C25:
=C23/C24
Here, cell C23 is the amount of the company’s EBITDA. Cell C24 is the interest expense of the company.
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s interest coverage ratio over the last four years
we will get an interest coverage ratio of 4.40 which means the above company’s profits are able to cover the interest 4.4 times. From the above calculation, we see that the interest coverage ratio increases every year. This is a good sign for the company. That means the company is in stable condition.
Read More: How to Do Ratio Analysis in Excel Sheet Format (with Quick Steps)
Similar Readings
- How to Calculate Current Ratio in Excel (2 Suitable Examples)
- Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)
- How to Calculate Ratio Between Two Numbers in Excel (5 Ways)
- Calculate Compa Ratio in Excel (3 Suitable Examples)
- How to Calculate Average Ratio in Excel (2 Simple Ways)
2. Utilizing EBIT Method
Here, we will use another method to calculate the interest coverage ratio of the company. In this method, the interest coverage ratio is obtained by dividing EBIT by Interest Expenses. Let’s walk through the steps to calculate the interest coverage ratio.
📌 Steps:
- Firstly, enter the amount of money of the company in the Revenue, Operating Expenses, interest income, and interest expense section.
- Next, we will use the following formula in the cell C8:
=C6+C7
Here, cell C6 is the income of the company by the completion of the project. Cell C7 is the company’s other income.
- Press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s total sales revenue over the last four years.
- Next, we will use the following formula in the cell C12:
=C10-C11
Here, cell C10 is the amount of the company’s total sales revenue. Cell C11 is the direct cost of the company.
- Then, press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s gross profit over the last four years.
- Next, we want to calculate the total operating expenses of the company. Therefore, we will use the following formula in the cell C17:
=C14+C15+C16
- Then, press Enter
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s total operating expenses over the last four years.
- Next, we want to calculate the net operating profit of the company. Therefore, we will use the following formula in the cell C21:
=C19-C20
Here, cell C19 is the amount of the company’s gross profit. Cell C20 is the total operating expenses of the company.
- Then, press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s net operating profit over the last four years
- Next, we want to calculate the EBITDA of the company. Therefore, we will use the following formula in the cell C23:
=C21+C22
Here, cell C21 is the amount of the company’s net operating profit. Cell C22 is the interest income of the company.
- Then, press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s EBITDA over the last four years
- Next, we want to calculate the EBIT of the company. Therefore, we will use the following formula in the cell C27:
=C25-C26
Here, cell C25 is the amount of the company’s EBITDA. Cell C26 is the depreciation of the company.
- Then, press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s EBIT over the last four years
- Next, we want to calculate the Interest Coverage Ratio of the company. Therefore, we will use the following formula in the cell C29:
=C27/C28
Here, cell C27 is the amount of the company’s EBIT. And cell C28 is the interest expense of the company.
- Then, press Enter.
- Next, drag the Fill Handle icon to the right.
- Therefore, you will obtain the company’s interest coverage ratio over the last four years
we will get an interest coverage ratio of 3.57 which means the above company’s profits are able to cover the interest 3.57 times. From the above calculation, we see that the interest coverage ratio increases every year. This is a good sign for the company. That means the company is in stable condition.
Read More: How to Calculate Ratio in Excel (4 Handy Methods)
Conclusion
That’s the end of today’s session. I strongly believe that from now you may calculate the interest coverage ratio in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
