How to Create Mortgage Loan Pipeline Management in Excel

If you need to keep track of mortgage loan status, Excel can help you greatly. In Excel, you can easily create a table of the various stages and regularly update the loan status. In this article, we will walk you through five easy steps on how to create a mortgage loan pipeline management in Excel effectively, with appropriate illustrations.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

5 Easy Steps to Create Mortgage Loan Pipeline Management in Excel

In this article, we will demonstrate five easy steps to create a mortgage loan pipeline management tool in Excel. We will create a dataset to keep track of the loan status. Here’s an overview of the dataset for today’s article.

Step 1: Create Borrower and Lender Columns

In this step, we will make a list of borrower and lender names to create a mortgage loan pipeline management tool in Excel.

• First of all, create two columns with Borrower and Lender as titles.
• Then fill out the cells with the names of borrowers and lenders.

Read More: How to Create Loan Pipeline Report in Excel (with Easy Steps)

Step 2: Add File Numbers and Types of Loan

In the second step, we will add file numbers and types of loans for each borrower in different columns.

• First, add two columns. Name them File No. and Types of Loan.
• Then type file numbers and loan types in the cells.

Read More: How to Create Pipeline Report in Excel (With Easy Steps)

Step 3: Insert Columns for Each Stage of the Process

Now we will add different stages of the mortgage loan pipeline and the amount of money given as loans in those stages.

• First, insert columns for each of the stages such as prospect, disclosure sent, disclosure signed, etc.
• Add as many columns as you need and name them whatever you prefer.
• In this example, we added three columns named Stage 1, Stage 2, and Stage 3.
• Additionally, we insert two more columns CTC (Clear to Close) and Closed.

Read More: How to Create Project Pipeline in Excel (2 Suitable Methods)

Step 4: Set up a Due Date Column

To keep track of the loan, we need to set up a due date column.

• In the beginning, insert a column and name it Due Date.
• Next, input the correct due date for each loan.

Read More: How to Create Commercial Pipeline in Excel (With Easy Steps)

Step 5: Check Loan Status

After all the data is set correctly in the cells, it’s time to keep track of the loans by adding a Status column. We will use the IF function to automatically update the status column.

• First of all, select cell L5 and write down the following formula.

• Then, Autofill the formula to the rest of the cell to obtain the status of all loans.

• You can use Conditional Formatting to change the color and font of text and cells to make it more appealing. For the “Unpaid” status we want to color the cell yellow.
• To do it, firstly, select cells from L5 to L12. Hence, from your Home tab, go to,

Home → Styles → Conditional Formatting → Highlight Cells Rules → Text that Contains

• As a result, a Text That Conations pop-up box will appear. Hence, type “Unpaid” in the Format cells that contain the text: box, and choose the color you prefer.

• We chose the yellow color in this example like below screenshot.

• Consequently, all cells having “Unpaid” status will become yellow.

• Similarly, follow the same procedure to format cells with other statuses.

• Now you can change any data and the status will automatically get updated.

Conclusion

Thanks for making it this far. I hope you find this article useful. Now you know how to create a mortgage loan pipeline management tool in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.

Related Articles

• How to Create a Sales Pipeline in Excel (with Easy Steps)
• Build a Sample Sales Pipeline in Excel (with Easy Steps)