Objective
In this article, we will be talking about one of the ways you can identify customers who have paid you high interest amounts over the last two years. We will explore the following topics:
- What report works best for finding your loan customers and data dependencies for this particular activity
- What report metrics we want to target in Excel to find the highest value loan customers
What report works best for finding these HELOC customers?
For this audience, we will want to use the Loan Listing report, which can be found within the Account Level tab of the Actionable Reports menu. This report allows you to query specific loan-related information on an account-by-account basis. In this case, we will be looking for those with any particular loan product that might make sense when it comes to interest-based revenue, something that we will need to use Excel to query.
For this particular activity, you will need to make sure that you are giving us the following pieces of data from your core: Loan Origination Amount, Interest Paid YTD, Interest Paid Last Year, Interest Accrued.
Which report metrics work best for finding this HELOC audience?
For this report, the following report criteria works best:
- Customer Type - Depending on your preferences, you can set this field to look at only ‘Nonbusiness’ customers for the purposes of this audience. If you don't care about whether the customers are businesses or not, then you can skip this field and leave it blank.
- Service Grouping - For this field, we want to include our most important Loan product groupings. Anything that is interest-bearing and counts towards a high-value customer, in your opinion, should be included in this field.
- Current Status - In this area, we can include any account status relating to the product being Open and Active or Paid Off. Most likely, you will just have this one status to mark each of these states. If it matters to you, then be sure to include any other custom statuses for these products to exclude anyone with Charged-Off accounts.
Finding Interest-Based Revenue in Excel
Now, we will need to export our results and move them into Excel to find those with a high-level of interest-based revenue over the last two years. To do this, we will want to create a pivot table in a new tab using our Loan Listing report results. In order to build this table, you can look at the criteria we have shown in the example above. You can see we are choosing to highlight the Customer Type and Full Name in the rows for our table. Then, we are adding in the SUM of the Original Amount, Interest Paid Last Year, and Interest Paid YTD. With all these items in the table, we can right click on the column for our interest revenues and sort by highest to lowest. This should allow you to see who has paid you the most interest on their loan over the last year, or within the prior year. A sum of these two numbers would give you the total interest-based revenue from these customers, giving you a full two-year view.
You might also be interested in adding the sum of the Interest Accrued, which is the remaining interest balance on the loan over the course of the term. Some of our customers have found this interesting as it can signify future high-value customers based on potential revenue.