Debt to Income Ratio Calculator in Excel (Create with Easy Steps)

This article illustrates how to make a debt to income (DTI) ratio calculator in Excel. Lenders use the debt-to-income ratio to determine whether a person is capable of repaying a certain loan or not. You can easily make a debt-to-income ratio calculator in Excel if you need to do this very often. Follow the article to learn how to do that.

What Is Debt to Income Ratio?

The debt-to-income ratio is simply the ratio of the total monthly debts of a person to his/her total monthly income. It is usually expressed in percentages.

Assume Mr. Smith has a total recurring monthly debt of $2500. On the other hand, his gross monthly income is $7500. Then you can calculate his debt-to-income ratio as follows.

There are mainly two types of debt-to-income ratios:

The front-end,
And the back-end

The front-end debt to income ratio signifies the percentage of your salary that can be expensed to repay the housing expenses.

On the other hand, the back-end debt to income ratio indicates the percentage of salary that can be expensed to repay all of your monthly recurring debts.

Lenders usually follow the debt to income ratio of 28/36 to decide whether to allow a borrower a loan or not. They use this ratio to determine the capability of the borrower to repay the loan. Here, the norm is that 28% of your salary can be expensed to repay the housing-related debts. This is usually the front-end debt to income ratio. On the other hand, 36% of your salary can be used to meet all of the recurring debt payments. Therefore, it is the back-end debt to income ratio.

The higher the debt to income ratio is, the more likely you will face difficulty to repay the loan. A debt to income ratio of 28% or less is generally preferable. But for those with a steady income, a healthy debt may have a debt to income ratio of up to 35%. If the debt to income ratio reaches 43-50%, you should think about reducing your debts by paying off some of your loans. Do not let your debt to income ratio go over 50%. Otherwise, you will almost certainly fall into financial difficulty.

Lenders are now more flexible on this historical 28/36 limit as housing costs and all other prices are much higher. They may allow you a loan even if your debt to income ratio is more than 50%. But you have to pay higher interest than others.

Steps to Make a Debt to Income Ratio Calculator in Excel

Follow the steps below to make a debt-to-income ratio calculator in excel.

📌 Step 1: Calculate Total Recurring Monthly Debt

First, make a list of all recurring monthly debts and enter the corresponding amounts. Then apply the following formula in cell D12 to calculate the total recurring monthly debt.

=SUM(D6:D11)

📌 Step 2: Input Gross Monthly Income

Next, you need to input your gross monthly income. If you have multiple sources of income, then make a list of them. After that, enter the following formula in cell H12 to get the gross monthly income.

=SUM(H6:H11)

📌 Step 3: Calculate Debt to Income Ratio

Now, apply the following formula in cell D14 to calculate the debt to income ratio. Then, apply the Percentage (%) cell format.

=D12/H12

Then, enter the following formula in cell F14 to show remarks based on the DTI ratio.

=IF(D14<=0.28,"Healthy Debt","Reduce Your Debts!")

You can apply Conditional Formatting to make the final result and interpretation look good. To do that-

Select cell F14. Then go to Conditional Formatting from the Home tab and select New Rule.

Next, select “Use a formula to determine which cells to format”. After that, enter the following formula as the rule description. Then, click on Format.

=D14<=0.28

Next, choose the green color from the Fill tab and click OK.

Click Ok again after that.

Then, apply Conditional Formatting to show a different color if the DTI ratio is greater than 0.28. Finally, you will see the following result.

Things to Remember

Don’t forget to apply the Percentage (%) format to get the DTI ratio in percentages.
You can apply multiple conditional formatting in the cells to show different colors based on the DTI ratio.

Download Debt to Income Ratio Calculator

You can download the debt-to-income ratio calculator template for free from the download button below.

Debt to Income Ratio Calculator.xlsx

Conclusion

Now you know how to make a debt to income ratio calculator in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

<< Go Back to Debt Template | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!