What Is a Tickler System and How Do I Use It In Excel?

Posted by Fiscal on September 20, 2023

For decades, banks and credit unions have used tickler systems to manage commercial lending accounts and ensure they are staying on top of documentation. Once done on paper and kept in filing cabinets, today’s tickler tracking systems are digital, often maintained in spreadsheet software like Excel. This approach works, but does have some drawbacks; tracking in Excel lacks efficiency and is error prone.

For those new to setting up their own tickler system, this post will provide everything you need to get started, as well as information on tickler tracking software when you’re ready to upgrade to a more automated, streamlined process.

What Is a Tickler System?

A tickler system is not unique to banking and lending; it’s also a useful tool for people in the medical and legal industries as well as administrators who need to keep track of tasks and due dates. This unusual name is shorthand for reminder–the idea is that it “tickles” your mind to complete the task before the due date.

Bank loan tickler tracking is a way to track items that must be documented throughout the life of the loan. It often includes due dates for customers to submit insurance policy renewals, financial statements, tax returns, and UCC continuations. A good system makes it easy to see when the last document was submitted, how often they should be submitted and when the next submission is due. These items will include both recurring items, as well as reminders to get any missing one-time items.

A good tickler system is an essential tool for commercial lenders to effectively manage their loan portfolios, mitigate risks, and improve efficiency. It also offers a clear record of the borrower, making compliance easier to demonstrate for auditors and examiners.

What Items Are Found in a Tickler Tracking System

Lending institutions and even individual loan officers will set up their own tickler tracking system to their liking. However, there are some common features to help keep them on track.

  • Borrower name and contact information
  • Guarantors and Related entities
  • Current loan balance
  • Insurance renewal due date
  • Business tax return due date
  • Personal tax return and financial statement due date
  • Date to send letters to borrowers
  • Fields for notes and waivers

How to Create a Tickler Tracking System in Excel

Many community banks and credit unions rely on Excel for both tickler tracking and financial statement spreading. If you’ve never set up a tickler system in Excel, here’s how to get started.

  • Open a new Excel workbook and create a header that includes columns for each piece of information you want to track, such as loan name, interest rate, personal financial statement due date, etc.
  • In the item due date column, you can add a formula to calculate the number of days until the next item is due based on today’s date and the item due date. For example, if the item’s due date is April 1 and today's date is March 1, the formula would calculate "30" in the “days until due” column (since there are 30 days from March 1 to April 1). 
  • You can also use conditional formatting to highlight cells that fall within a certain range of dates, highlighting when items are due soon or have fallen past due. You could use a red cell color to flag items that are past due, or yellow for items due within 30 days, for example.
  • Another option is to apply a Filter to the header row that allows you to automatically sort your data by the due date so that you always see loans with upcoming documents due at the top.
  • Using Microsoft 365 features, you can set up reminders in Outlook to alert you when items are coming due.

While we’re talking about Excel, are you looking for help creating your financial statement spreading spreadsheets in Excel? Try our free template.

Common Features of Tickler System Software

For those who have experience with tickler systems in Excel, you may be looking to upgrade to a system with more features. Here are a few of the capabilities you’ll find in tickler tracking software.

  • Automated reminders: Get automatic notifications before a specific date or deadlines.
  • Tracking checklist templates: Quickly set up ticklers for new borrowers or loans with tracking lists already created for you.
  • Customizable scheduling: Set up custom schedules and deadlines for different types of loans or borrowers for your specific needs.
  • Reporting capabilities: Generate exceptions reports grouping and filtering by Officer, Category of Exception, Due Date, and more.
  • Integration with other systems: Integrate your tickler system with other software applications, such as your bank’s core system, to quickly import borrower information and ensure data on borrowers is up-to-date.
  • Compliance monitoring: Maintain regulatory compliance by creating alerts for missing or late documentation and other potential risks or violations related to loan terms.
  • Letter generation: create letters and emails to customers requesting items as they are coming due, or after they fall past due, with just a few clicks.

Tracking Software vs. Tracking in Excel

Tracking software offers you more efficiency and accuracy, as well as significant time-saving automation. If you want to learn more about FISCAL’s tracking capabilities, contact us for a one-on-one demo. We can review the Excel system you’re using today and discuss the advantages to switching to a software solution.