How to Settle an AmWest Deal from an Investor Email

Learn how to accurately settle an AmWest deal by updating closing dates, balances, and final prices in the master tape using an investor settlement email.

May 28, 2026 By Eddy Kup

This guide demonstrates how to settle an AmWest deal by extracting data from an investor settlement email and updating the master tape. Completing this workflow ensures all loan balances, closing dates, and final prices are accurately recorded in the database prior to funding.

This process applies to capital markets analysts and funding specialists responsible for updating and verifying loan data during the trade settlement phase.

Retrieve Settlement Data

Step #1: Double-click the investor settlement email to open the thread and locate the attached documentation. — Double-click the investor settlement email to open the thread and locate the attached documentation.

Step #2: Open the AmWest Settlement internal funding memo and verify the investor prices. — Open the AmWest Settlement internal funding memo and verify the investor prices.

Step #3: Save the associated Excel attachments from the email to your local workspace. — Save the associated Excel attachments from the email to your local workspace.

Match Closing Dates

4

Type =XLOOKUP( in the targeted cell to start mapping closing dates from the funding memo into your master tape.

5

Press Enter to apply the formula.

Filter and Validate Loan Records

Not all loans fund simultaneously. Adjust closing dates accurately to match the pools that are actively funding.

Step #6: Open the filter dropdown on the Seller column and select Amwest. — Open the filter dropdown on the **Seller** column and select **Amwest**.

Step #7: Click OK to apply the seller filter. — Click OK to apply the seller filter.

Step #8: Open the filter menu on the Closing Date column and search for the target year. — Open the filter menu on the **Closing Date** column and search for the target year.

Step #9: Check the box for the specific expected closing date, such as the 25th of the month. — Check the box for the specific expected closing date, such as the 25th of the month.

Step #10: Click OK to apply the date filter. — Click OK to apply the date filter.

Handle Dropped Loans

Step #11: Filter the Paid Through Date at Acquisition column to select #N/A, identifying loans that are out of population. — Filter the **Paid Through Date at Acquisition** column to select **#N/A**, identifying loans that are out of population.

13

Type NA (or enter a 0% delivery rate) to clear the closing dates and fund names for these non-funding loans, then press Enter.

Update Balances and Dates

Step #14: Copy your initial XLOOKUP formula and paste it into the Scheduled Balance and Final Price columns, adjusting the referenced columns to match your summary sheet. — Copy your initial XLOOKUP formula and paste it into the **Scheduled Balance** and **Final Price** columns, adjusting the referenced columns to match your summary sheet.

15

Enter the agreed cut-off date in the corresponding column and press Enter.

16

Type =EDATE([cell], 1) to automatically calculate the payday date as one month after the interest date.

Ensure there are no missing data points (TBDs). Update your TPR vendors to the appropriate party, such as Pathfinder Corp (APC).

Finalize the Data Tape

17

Clear all active filters on the tape and press Ctrl + A to select the entire dataset.

18

Copy the data and paste it as special values to strip away the formulas and lock in the final data.

Q: What if a loan is returning an N/A value and is not in the population?

A: For loans returning an N/A, change the closing date and fund names to NA, or set the delivery to 0% since they are likely to be removed before funding.

Q: How do we handle pools that are separated during funding?

A: Adjust and update the closing dates as accurately as possible based on the memos. If a definitive target closing date is unknown, add one to two weeks to the current date.

Q: Why do we multiply the scheduled pay-to date by one in Excel?

A: Multiplying by one forces Excel to convert text-formatted date strings into actual numeric date formats, preventing calculation errors.

Term	Definition
Master Tape	The central database or spreadsheet containing comprehensive details of all loans in a transaction.
TPR Vendor	Third-Party Review vendor responsible for conducting independent diligence on the loan files.
Cut-off Date	The date on which loan balances and accrued interest are calculated for the purpose of the sale.
XLOOKUP	An Excel function used to search a range or array and return the corresponding item from another column.