Tax & Accounting Blog

Driving Revenue with UltraTax CS Data Mining, Part 5: Finding clients who may benefit from a Roth IRA

CS Tax & Asset Management Solutions July 22, 2014

As you know, a Roth IRA is a great way for some clients to proactively limit their tax burdens once they’ve retired. They’re particularly helpful to clients who expect to receive significant income during their retirement. And for most Roth candidates, it’s important to start their IRA as soon as possible. 

The planning of the Roth IRA itself can be a good revenue opportunity for your firm, and even clients who don’t choose to open one will be impressed that you’re looking out for their best interests. Identifying Roth IRA candidates is another task that’s a lot easier with UltraTax CS Data Mining. 

Below, I’ve outlined the steps for creating a custom report and using two searches to populate that report. The searches I’ve described will find clients who are currently contributing to a traditional IRA, as well as clients who have an existing traditional IRA balance of $50,000 or more.

Building a report with key traditional and Roth IRA figures 

This custom report makes it easy to review clients who may benefit from a Roth IRA. Here’s how to create it. 

  1. From within UltraTax CS, choose Utilities > Data Mining.
  2. Select the 1040 Database and click the Continue (Step 2)  button.
  3. Click the Design Custom Reports button near the top right.
  4. Click Add  to add a new report.
  5. Enter a description for the new report, but do not select a template. Then, click Continue.
  6. Select over the following items from the General category of the Productdrop-down.
    1. Client ID (change the Field length on the right to 11)
    2. Client name (change the Field length on the right to 20)
    3. Filing status(abbr) (change the Field length on the right to 3)
  7. Change the Product:drop-down to Federal and select over the following values:
    1. 1040, Ln 38 – Adjusted Gross Income
    2. 1040, Traditional IRA Deduction (Tp)
    3. 1040, Traditional IRA Deduction (Sp)
  8. On the left, click Input screen field.
  9. Click the Select Field button to the right.
  10. Navigate to the Retirement folder, click the IRA tab, highlight the field in the Taxpayer column for Traditional IRA value on 12/31/13 (including rollovers), and click Select at the bottom.
  11. Repeat Steps 9 and 10 to select the field in the Spouse column.
  12. In the right pane, mark the Total checkbox next to the columns you added in steps 9 and 10.
  13. In the Preview pane at the bottom, decrease the Font size to 8.
  14. Click OK and then click Done.

Creating a new search to identify clients with traditional IRA balances above a certain threshold 

Now that you’ve created a report, you’ll create a search to populate it. The search below will find all clients where either a taxpayer or a spouse has a traditional IRA balance over $50,000. You can change this amount if you prefer a lower or higher threshold. Here’s how to create the search.

  1. From within UltraTax CS, choose Utilities > Data Mining, select the 1040 Database and click the Continue (Step 2) button.
  2. Click the Add/Modify Searches button on the right.
  3. Click the Add button at the bottom left.
  4. Enter a search name, such as Clients with Tp/Sp Trad IRA balance >$50k.
  5. Click the Input screen field option.
  6. Click the Add/Modify button below the list of conditions.
  7. Scroll down the list until you locate IRA, Sp trad IRA value on 12/31/13 > 0. Highlight it, and then click the Another button.
  8. Change the Operator drop-down to Greater Than, enter a value of 50,000, and click Enter.
  9. Repeat the process described in steps 7 and 8 for the IRA, Tp trad IRA value on 12/31/13 > 0 item.
  10. Click Done to exit the Add/Modify Search Criteria dialog.
  11. Select the following search criteria items to the right pane.
    • US: IRA, Sp trad IRA value on 12/31/13 > 50000
    • US: IRA, Tp trad IRA value on 12/31/13 > 50000
  12. Click Done at the bottom and continue to the next section.

Combining the new search with a pre-defined search to find all potential Roth IRA candidates

UltraTax CS Data Mining includes several pre-defined searches, including one that finds all clients who are currently making contributions to a traditional IRA. You can combine this with the custom search you created above to find all clients who may benefit from a Roth IRA. Here’s how to combine the two searches.

  1. While still viewing the list of searches in the Add/Modify Searches window, and with the newly created Clients with Tp/Sp Trad IRA Balance >$50k search still highlighted, click the Combine Searches button.
  2. Locate and highlight the Traditional IRA Contributions search, then click the Select button.

    Note: Clients with nondeductible traditional IRA contributions are particularly good Roth IRA candidates, since they’re already accustomed to making retirement contributions with no immediate tax benefit. You can identify them by selecting the Traditional IRA Contributions, nondeductible predefined search instead of Traditional IRA Contributions in Step 2.

  3. Click Done, then Done again on the Add/Modify Searches listing.

Running the search and creating a finished report

Once you’ve created a custom report and combined the two searches, you’ll run the search and create your finished report. Here’s how to create the report.

  1. If not already selected, select the custom search you created earlier. It will be combined with the Traditional IRA Contribution search.
  2. In the Available to print pane in the top-left of the window, scroll to the bottom of the list of reports highlight our new report,Trad & Roth IRA Info, and then click Select.
  3. Click Continue (Step 3)  to perform the search and generate the report. This process may take a few minutes. When finished, you can print or preview the report.
  4. If you’d like to export the results to Excel after printing or previewing, choose Tools > Export after Data Mining returns to Step 1. 

Come back on August 5 for my next blog entry, where I’ll show you how to use UltraTax CS Data Mining to find self-employed clients who may benefit from retirement planning services. Until then, feel free to post your questions and comments below.