How to calculate the interest coverage ratio in Excel

Step 1: EBIT from a P&L list with SUMIFS

The sheet holds one quarter's profit and loss lines with a Type column: Revenue, Operating cost, Depreciation or Interest. Your bookkeeping software's P&L report gives you the same lines; add the Type column yourself.

EBIT is revenue minus every operating cost and depreciation, before interest (and before tax). =SUMIFS(C2:C9,B2:B9,"Revenue")-SUMIFS(C2:C9,B2:B9,"Operating cost")-SUMIFS(C2:C9,B2:B9,"Depreciation") gives $524,500 - $427,700 - $9,800 = $87,000.

If your report starts from net profit instead, EBIT is net profit plus interest plus tax added back. Either route should land on the same number.

Step 2: Coverage = EBIT / interest

Interest expense is =SUMIFS(C2:C9,B2:B9,"Interest"): $17,150 for the quarter. Divide: =F5/G5 is 5.07. Operating profit was a little over five times the interest bill.

Use the interest expense from the P&L, not the loan payments. A loan payment includes principal, and principal isn't an expense. If you want to test whole payments, that's the debt service coverage ratio.

Keep both numbers for the same period. A quarter's EBIT over a year's interest makes coverage look four times worse.

A quarterly trend

One quarter can be lumpy, so line up the last four. The table in E2:H5 has EBIT and interest for each quarter with =F2/G2 copied down. Coverage went 4.34, 3.51, 4.45 and 5.07.

Q1 is the weak one: EBIT fell to $58,900 while interest stayed about the same. For a seasonal business, compare each quarter with the same quarter last year as well.

For a yearly figure, add the four quarters of EBIT and the four of interest before dividing: =SUM(F2:F5)/SUM(G2:G5) gives about 4.35. Averaging the four ratios gives a slightly different and less accurate answer.

What a higher rate does

If your loan has a variable rate, the same EBIT covers less interest when rates rise. The settings hold the loan balance ($980,000) and the current rate (7%), which gives =K2*K3/4 = $17,150 a quarter, the same as the P&L.

Put a rate you want to test in K4. At 9%, quarterly interest is =K2*K4/4 = $22,050, and coverage drops from 5.07 to =F5/(K2*K4/4) = 3.95.

To see how far EBIT could fall before coverage hits a level you want to test, multiply interest by that level: at 9% and a coverage of 2 (in K5), EBIT would need to stay above $44,100. Any minimum a lender sets is in your loan documents; 2 here is only a sample.

The EBITDA version

Some lenders and reports use EBITDA (earnings before interest, taxes, depreciation and amortisation) instead of EBIT. Leave out the depreciation line: =(SUMIFS(C2:C9,B2:B9,"Revenue")-SUMIFS(C2:C9,B2:B9,"Operating cost"))/G5 gives $96,800 / $17,150 = 5.64.

EBITDA coverage is always at least as high as EBIT coverage, so check which one a document means before comparing. If EBIT is negative, the ratio goes negative and stops meaning anything useful: show a note instead with =IF(F5<=0,"No operating profit",F5/G5).