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.

mortgage loan pipeline management excel


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.

Create Borrower and Lender Columns to Create Mortgage Loan Pipeline Management System in Excel

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.

Add File Numbers and Types of Loan to Create Mortgage Loan Pipeline Management System in Excel

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.

 Insert Columns for Each Stage of the Process to Create Mortgage Loan Pipeline Management System in Excel

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.

Set up a Due Date Column to Create Mortgage Loan Pipeline Management System in Excel

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.
=IF(J5 <>"","Paid",IF(K5<TODAY(),"Past Date","Unpaid"))
Formula Breakdown
  • IF(K5<TODAY(),”Past Date”,”Unpaid”) will return “Past Date” if K5 is less than Today (15 december 2022) otherwise “Unpaid”.
  • IF(J5 <>””,”Paid”,IF(K5<TODAY(),”Past Date”,”Unpaid”)) will look into cell J5 If the cell is not empty, it will return “Paid” otherwise “Unpaid”.

  • 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

Check Loan Status to Create Mortgage Loan Pipeline Management System in Excel

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

mortgage loan pipeline management excel

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

Check Loan Status to Create Mortgage Loan Pipeline Management System in Excel

Notes:
  • You can add more columns and name them according to your need.
  • Additionally, you can keep track of the total loans of each stage.

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

Alif Bin Hussain

Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo