Bank statement income calculator

Get detailed analytics from a borrower's bank statements in Excel format.

The Bank Statement Income Calculator (BSIC) is a powerful tool designed to provide in-depth financial analysis derived from a borrower's bank statements. This analysis includes key insights such as NSF (non-sufficient funds) transactions, large deposits, and statement balances, which are crucial for assessing a borrower's financial health and creditworthiness.

The BSIC allows for adjustments and generates automatic output for final qualifying income calculations to streamline the loan underwriting process. By automating income calculations, the BSIC helps reduce processing time, mitigate risk, and increase loan volume.

The BSIC is available as a spreadsheet, which makes it compatible with widely used programs such as Microsoft Excel and Google Sheets.

Prerequisites

To use the BSIC, ensure that you have an active Ocrolus Dashboard user profile. If you need access to the Ocrolus Dashboard, please contact your account manager.

Uploading new bank statements

This section outlines the steps for creating a <glossary:Book> and uploading <glossary:Documents> to utilize the BSIC using the Ocrolus Dashboard.

To upload the bank statement, perform the following steps:

  1. Log in to the Ocrolus Dashboard.

  2. Click on the NEW BOOK button.

    1600
  3. Enter the name of the Book and click on SUBMIT.

  4. On the File Uploader popup, select either Classify Uploads or Bank Statement from the Choose Your Form Type dropdown list, upload the desired bank statement, and then click SUBMIT.

  5. Once the bank statement is uploaded and submitted for processing, the Book name will appear on the Book List page. Initially, the Book status will display as Verifying, and after the system completes processing, the status will update to Verification Complete.

    950

Transactions page

After the bank statement is uploaded, the system captures all available transactions from the statement and displays the details in the table within the Transaction section. This section explains how to view the extracted bank transaction information before downloading the BSIC.

To view the extracted transactions, perform the following steps:

  1. On the Book list page, click the Book you want to access the transaction. To find a specific Book, use the search bar.

  2. At the left corner of your screen, click on the arrow to expand the Book Summary pan.

  3. Click on the Transactions menu. The table below outlines the available fields in the Transactions table, along with their corresponding descriptions:


    FieldDescription
    DescriptionA brief description captured from this transaction.
    StarredA transaction that is marked for future reference by a Dashboard user.
    DateDate of the transaction.
    AccountThe account in which the transaction occurred.
    AmountThe monetary value of the transaction.
    RevenueIndicates whether the transaction is included in the revenue calculations for the Book. The transaction is included when toggled ON (blue and to the right). When toggled OFF (gray and to the left), the transaction is excluded.
    TagsAnnotations that indicate certain properties of the transaction. A full list of available tags can be found on the Enriched Transactions and Ocrolus analytics data dictionary pages.
    CounterpartyWhen it is available, the counterparty is involved in the transaction.
    CommentsUser-entered notes in the Comment section of the transaction.

Searching, filtering, and sorting transactions

The Transactions page has a search bar that allows you to search for transactions or keywords. The results will automatically filter as they type. Furthermore, you can filter by date, tags, enriched transactions, or specific column values. You can also sort by date or amount for a more refined view.

To learn more about searching, sorting, and filtering transactions, see the searching, filtering, and sorting transactions page.

Viewing transaction details

Click on a specific transaction to view detailed information about it in the Capture Details panel, which appears on the right side of the screen. This panel provides key information, including the transaction description, editable transaction tags, and editable revenue calculation status. To learn more about viewing transaction details, see the View transaction details.

Excluding or including transactions

Include or exclude specific transactions from the income calculation, individually or in bulk. To review which transactions are included or excluded, use the filter in the Revenue column of the transactions table. Select Yes to view included transactions and No to view excluded ones.

To learn more about updating transaction details individually or in bulk, see Updating transaction details.

Additionally, you can adjust the Include in Revenue Calculation toggle while reviewing the transaction image. A pop-up window will display the selected transaction with the transaction description as the header. The toggle for Include in Revenue Calculation allows you to include or exclude the selected transaction from the revenue calculation.

Generate income calculator

Generate the income calculator by accessing either the EXPORT menu or the table within the Book details page. Both options allow for a streamlined and efficient way to produce the calculator, to ensure you have the necessary data for accurate income analysis.

Using EXPORT menu

To generate the income calculator using the EXPORT menu, perform the following steps:

  1. On the Book list page, click the Book for which you want to generate an income calculator. To find a specific Book, use the search bar.

  2. Click on the EXPORT dropdown menu and select the XLSX link with the Bank Statement Income option from the dropdown menu. The income calculator for the selected Book is generated in the XLSX format.

Using Book details page

To generate the income calculator using the Book details page, perform the following steps:

  1. On the Book list page, click on the ellipses menu on the right-hand side of the Book from which you'd like to generate an income calculator.

  2. The income calculator for the selected Book is generated in the XLSX format.

Inspecting the Excel output

The income calculator is formatted as an Excel spreadsheet separated into different sections. This allows you to inspect each transaction detail and the inputs/outputs for the income.

Some fields in the Excel spreadsheet are tagged as Add data here. It indicates fields that require manual entry from the end user.

Book Summary tab

The Book Summary tab in the BSIC spreadsheet provides a comprehensive and structured overview of income-related data across all uploaded bank accounts. It is designed to offer a clear and concise summary of key financial details, making it easier to analyze and verify income calculations.

Instead of manually reviewing individual bank statements, you can rely on this summary to gain a holistic view of financial data. The Book Summary tab also includes a Print to PDF functionality to allow you to quickly generate a PDF version of the summary.

Borrower/loan information table

This table provides a consolidated view of the total qualifying income across all uploaded bank accounts. In addition to system-generated data, you are required to manually enter certain details, such as the borrower’s name and loan number, as these fields cannot always be extracted directly from the bank statements processed by Ocrolus. This manual input ensures that all necessary loan-related information is accurately recorded for reporting and verification purposes.

Account totals table

For each uploaded bank account, the Book Summary tab presents key financial details, including:

  • Qualifying Income – The income amount considered for calculations.
  • Total Deposits – The total sum of all deposits during the statement period.
  • Excluded Deposits Count – The number of deposits that were excluded from income calculations.
  • NSF (Non-Sufficient Funds) Days – The number of days with insufficient funds.
  • Large Deposits Count – The number of high-value deposits flagged for review.

These details allow you to quickly assess an account’s financial standing without the need for manual transaction reviews.

Excluded deposits table

This table provides a breakdown of deposit transactions that were not included in the income calculation. By reviewing this table, you gain insight into how income is determined and can verify exclusions.

📘

Note

If you manually change a deposit transaction from Included to Excluded or vice versa, the Excluded Deposits Table in the Book Summary tab will not update dynamically, as this functionality is not currently supported by Ocrolus.

Account Summary tab

The Account Summary tab provides a detailed breakdown of a bank account’s financial data to offer deeper insights beyond the high-level overview in the Book Summary tab. Review all deposit transactions, examine statement overview details, and analyze key financial patterns to better understand income trends.

This tab highlights the top 10 recurring depositors, making it easier to identify consistent income sources, and displays large deposit threshold values for each month, ensuring visibility into significant transactions. A key enhancement to the statement overview table is the Declining Income Indicator, which helps you quickly spot downward trends in income, allowing for more informed decision-making.

Deposits table

The Deposits Table provides a comprehensive view of all transaction-level details extracted from the uploaded bank statements. These deposit transactions are crucial in determining the qualifying income and directly impact the final computed amount.

Within this table, certain transactions may be excluded from the qualifying income calculation based on predefined criteria. The Include/Exclude field indicates whether a transaction has been considered in the income assessment, and you can manually adjust this by selecting the appropriate option from the dropdown menu.

Statement Overview table

The Statement Overview table summarizes the statement periods in the uploaded bank statements. It supports up to 36 months of data within the income worksheet, providing a clear view of statement balances, total deposit amounts, and the number of NSF transactions.

This table also includes:

  • A declining income indicator, which highlights periods where income trends downward, helping identify potential financial instability.
  • A missing month indicator, which notifies you of any gaps in the statement period to ensure completeness in the income assessment.

Top 10 Recurring Depositors table

The Top 10 Recurring Depositors table highlights a bank account's most frequent deposit sources. This table helps you assess the stability and reliability of income by identifying depositors who contribute regularly over time. The table displays the average deposit amount for each recurring depositor, allowing you to evaluate the significance of each income source quickly.

NSF Transaction # of days table

The NSF Transactions # Number of Days table provides a detailed month-by-month view of the number of days a bank account had NSF within the statement period. This table helps you assess a borrower’s financial management and liquidity by identifying patterns of insufficient funds at both an overall and monthly level.

Large Deposits Threshold table

The Large Deposit Threshold table allows you to define the criteria for classifying a transaction as a large deposit. By default, a deposit is considered large if it exceeds the computed average deposit amount by at least 50%. Still, you can adjust this threshold by modifying the percentage value in the spreadsheet.
This table also provides a month-level calculation of the large deposit threshold amount, using the average deposit for each month and the user-defined percentage. Based on this threshold, transactions in the Deposits Table are marked as Yes or No under the Large Deposit column to ensure a more dynamic and accurate classification of large deposits over time.

Borrower Information table

The fields in this table represent borrower-specific details used to determine the outputs within the Qualifying Income sections. You must manually enter the Guideline Expense Factor and Borrower(s) % Ownership of Business.

Qualifying income calculations

Ocrolus supports multiple methodologies for calculating qualifying income to ensure flexibility in assessing a borrower's financial position. Each method applies different criteria to determine income and allows you to select the most appropriate approach based on underwriting requirements. The available calculation methods are detailed below:

Standard expense factor calculation

The following are the available standard expense factor calculations:

  • Total Allowable Deposits: The sum of all deposits from column T that are marked as Included in Column V.
  • Qualifying Business-Related Deposits × Expense Factor × % Ownership: The Borrower's % of Ownership of Business multiplied by the difference of the Included Deposits minus the total of Included Deposits multiplied by the Guideline Expense Factor.
  • Qualifying Expense Factor Monthly Income: Qualifying Business-Related Deposits × Expense Factor × % Ownership divided by the Total Months Included.

CPA Licensed tax preparer expense factor calculation

The following are the available CPA-licensed tax preparer expense factor calculations:

  • Total Allowable Deposits: The sum of all deposits from column T that are marked as Included in Column V.
  • CPA/Tax Preparer Expense Factor: An expense factor typically supplied by a CPA or tax preparer. It must be entered manually.
  • Total Allowable Deposits × Expense Factor × % Ownership: The Borrower’s % of Ownership of Business multiplied by the difference of the Included Deposits minus the total of Included Deposits multiplied by the CPA Expense Factor.
  • Qualifying Income: Qualifying Business-Related Deposits × CPA Expense Factor × % Ownership divided by the Total Months Included.

Profit & loss expense factor calculation

The following are the available profit & loss expense factor calculations:

  • Gross Revenue from Profit and Loss Statement: Total gross revenue from a borrower-provided profit and loss statement. It must be entered manually.
  • Expenses from Profit and Loss Statement: Total expenses from a borrower-provided profit and loss statement. It must be entered manually.
  • Expense Factor (Expenses / Gross Revenue): Expenses from Profit and Loss Statement divided by Gross Revenue from Profit and Loss Statement expressed as a percentage.
  • Total Allowable Deposits minus Expenses: Gross Revenue from Profit and Loss Statement minus Expenses from Profit and Loss Statement.
  • Total Allowable Deposits × Expense Factor × % Ownership: Total Allowable Deposits minus Expenses multiplied by Borrower % Ownership of Business.
  • Qualifying Income: Total Allowable Deposits × Expense Factor × % Ownership divided by Total Months Included.
  • Tolerance of Allowable Deposits / Gross Revenue: Total Included Deposits divided by Gross Revenue from Profit and Loss Statement, expressed as a percentage.