Bank statement income calculator
Get detailed analytics from a borrower's bank statements in Excel format.
The Ocrolus Bank Statement Income Calculator (BSIC) provides detailed analytics such as NSF transactions, large deposits, and statement balances. These details are derived from transaction data extracted from the borrower's bank statements. You can easily adjust inputs and receive automatic output for final qualifying income calculations. This tool is ideal for retail lenders offering non-QM bank statement loan products, as well as current non-QM lenders aiming to reduce processing time, lower risk, and increase loan volume.
It is available as an Excel spreadsheet, compatible with programs like 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:
-
Log in to the Ocrolus Dashboard.
-
Click on the NEW BOOK button.
-
Enter the name of the Book and click on SUBMIT.
-
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.
-
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.
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:
-
On the Book list page, click the Book you want to access the transaction. To find a specific Book, use the search bar.
-
At the left corner of your screen, click on the arrow to expand the Book Summary pan.
-
Click on the Transactions menu. The table below outlines the available fields in the Transactions table, along with their corresponding descriptions:
Field Description Description A brief description captured from this transaction. Starred A transaction that is marked for future reference by a Dashboard user. Date Date of the transaction. Account The account in which the transaction occurred. Amount The monetary value of the transaction. Revenue Indicates 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. Tags Annotations 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. Counterparty When it is available, the counterparty is involved in the transaction. Comments User-entered notes in the Comment section of the transaction.
Searching, filtering, and sorting transactions
The Transactions page allows you to search for transactions or keywords using the search bar, with results filtering automatically as you type. Additionally, you can filter by date, tags, enriched transaction, or specific column values, and 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
You can click on a specific transaction to view detailed information about that transaction in the Capture Details panel that 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
You can include or exclude specific transactions from the income calculation, either 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, while reviewing the transaction image, you can adjust the Include in Revenue Calculation toggle. 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 either include or exclude the selected transaction from the revenue calculation.
Generate income calculator
You can 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:
-
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.
-
Click on the EXPORT dropdown menu and select the XLSX link inline 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:
-
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.
-
The income calculator for the selected Book is generated in the XLSX format.
Inspecting the Excel output
This section describes the information available within the income above calculator. 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 rows or fields are color-coded. Their meanings are as follows:
- Yellow highlights indicate fields that require manual entry from the end user.
- Teal highlights indicate qualified income details impacting the DTI ratio.
- Red highlights indicate transactions that exceed the large deposit threshold amount.
- Purple highlights indicate transactions that are excluded from the qualifying income amount.
Borrower transaction details
Borrower transaction details include any loan-level or borrower-level details to be aware of. This will require some manual input from the end user, as some of these details cannot be captured from the bank statements Ocrolus has processed (e.g. Loan Number).
For example, the Borrower/Loan Information table on a sample income calculator spreadsheet for a business named Jill's Coffee. The account holder and number were captured from the provided bank statements. The Borrower Name, Loan Number, and Account Type fields are empty and require manual input.
Deposits
This table of the income calculator includes all of the transaction-level details Ocrolus has extracted from the collected bank statements. These transaction amounts will directly impact the borrower’s computed qualifying income amount.
For example, in the below Deposits table on the income calculator, some transactions are excluded from the overall qualifying income amount. These transactions are highlighted in purple and the Include/Exclude field is set to Exclude.
To include or exclude a specific transaction from the qualifying income amount, select the drop-down field and change the value accordingly.
Large deposits
This table is used to configure the rules by which a transaction is considered large. Large transactions are marked with a Y in their Large Deposit column. Any transaction that exceeds this amount is considered large.
The default threshold is 50%. Any deposit greater than the computed average by at least this ratio is considered large. You can change this amount by modifying the percentage amount in the yellow input field.
NSF transactions
This table lists all NSF (non-sufficient funds) transactions identified in the provided bank statements. We isolate these transactions to show how often the borrower overdraws on their account. You may include or exclude these transactions as you see fit.
For example, in the below screenshot, the NSF Transactions table of the income calculator. Two overdrawn transactions of $35 are listed, one of which is excluded from qualifying income calculations. The Total NSF Count field sitting above this table lists the number of included transactions.
Statement overview
This table contains an overview of the statement periods included in the provided bank statements. We support up to 36 months' worth of bank statements within this income worksheet. You can also see details of the statement balances, total amounts for deposits, and the number of NSF transactions.
Borrower/Loan information
The fields in this table represent borrower-specific details used to determine the outputs within the Qualifying Income sections. The Guideline Expense Factor and Borrower(s) % Ownership of Business must be manually entered by the end user.
Qualifying income
The qualifying income tables include cell-level calculations driven by manual data entry and transaction details collected in the Deposits section from the bank statements extraction. Below is an outline of the calculations that live within each field.
Standard expense factor calculation
- Total Allowable Deposits
- The sum of all deposits from column C that are marked as Included in Column D.
- 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
- Total Allowable Deposits
- The sum of all deposits from column C that are marked as Included in Column D.
- CPA/Tax Preparer Expense Factor
- An expense factor typically supplied by a CPA or tax preparer. 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
- Gross Revenue from Profit and Loss Statement
- Total gross revenue from a borrower-provided profit and loss statement. Must be entered manually.
- Expenses from Profit and Loss Statement
- Total expenses from a borrower-provided profit and loss statement. 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.
FAQs
How does Ocrolus identify deposits?
We consider any transaction with a credit amount greater than $0 a deposit.
How does Ocrolus decide which transactions are included or excluded in the income amount?
Once we capture a transaction's details, we categorize it according to a library of rules based on client feedback and use cases. These rules mainly involve the transaction's description.
Updated 14 days ago