How to Update the BI Database for Investor Settlements

Learn how to update the BI format database using XLOOKUPs after receiving an investor settlement email from transaction management.

May 18, 2026 By Eddy Kup

This guide explains how to update records in the BI format database after receiving an investor settlement confirmation email. The goal is to accurately map loan population data, updating statuses, balances, and critical dates to reflect the newly settled transaction.

This workflow is designed for data operations and transaction management teams. It should be performed immediately after receiving a settlement email from Dewey specifying the seller, loan count, servicing details, and purchasing entities (such as APCA or AP2).

Access the Settlement Details

The settlement email contains critical details about the transaction, including the seller, the number of loans, whether servicing is released or retained, and the purchasing and financing entities.

Step #1: Double-click the settlement email in your inbox to open it in a new window. — Double-click the settlement email in your inbox to open it in a new window.

Double-click the attached funding memo Excel file (e.g., LenderMac - Ares Settlement Memo.xlsx) to open it.

Step #3: Navigate to the Population tab at the bottom of the funding memo to view the loan balances and purchase prices. — Navigate to the **Population** tab at the bottom of the funding memo to view the loan balances and purchase prices.

Filter the Database

Step #4: Switch to your underlying BI database spreadsheet. — Switch to your underlying BI database spreadsheet.

Open the filter menu for the Seller column and check the box next to the appropriate seller (e.g., LenderMac).

Click OK to apply the seller filter.

Step #7: Open the filter menu for the Closing Date column and select the specific settlement date provided in the email. — Open the filter menu for the **Closing Date** column and select the specific settlement date provided in the email.

Click OK to apply the closing date filter.

Map Loan Attributes

Once the database is filtered to the correct seller and closing date, use XLOOKUP formulas to pull in updated balances, prices, and dates from the funding memo.

Field Mapping Reference

Target Database Field	Source Document	Formula Logic / Target Column
Balance at Acquisition	Funding Memo (Population)	Column B
Final Purchase Price	Funding Memo (Population)	Column I (Excludes accrued interest)
Interest Pay-through Date	Funding Memo (Population)	Multiply value by `1` to force text format
Due Date	Calculated via Formula	`=EDATE([Pay-through Date], 1)`
Seller AM3 ID	Loan IDs Attachment	Append `&""` to force text format

Use the XLOOKUP function to map the Balance at acquisition and Final purchase price from the funding memo into the database.

When pulling in the Interest pay-through date, multiply the XLOOKUP result by 1 (e.g., *1). This forces the database to store the result as text, preventing issues with scientific notation formatting.

Calculate the Due Date by wrapping the pay-through date in an =EDATE() formula, advancing it by exactly one month.

Manually update the Transaction Stage column to Acquired - Pending Activation.

Map Loan IDs

The settlement email also includes a secondary attachment specifically for mapping internal and seller IDs.

Open the attached Loan IDs file from the original email.

Write an XLOOKUP to pull the Seller AM3 ID into the database, appending &"" to the end of the formula to format the ID as text.

FAQ

Q: Why do we multiply the Interest Pay-through Date by 1?

A: Multiplying the retrieved value by 1 forces the database to format the date string as text. This prevents the spreadsheet from accidentally converting the value into scientific notation.

Q: How is the Due Date determined?

A: The Due Date is always exactly one month after the Interest Pay-through Date. You can automatically calculate this in Excel by using the =EDATE([cell], 1) function.

Q: Why do we add an ampersand and quotes to the Loan ID formula?

A: Appending &"" to the end of your XLOOKUP formula coerces the retrieved Loan ID into a text string. This ensures alphanumeric IDs are stored consistently and prevents formatting errors in the BI database.