Methods and systems for portfolio cash flow valuation

[0001] A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

[0002] This invention relates generally to valuation methods for financial instruments and more particularly to analyzing portfolios of distressed financial assets for the purpose of bidding to acquire those assets.

[0003] A large number of assets such as loans, e.g., ten thousand loans or other financial instruments, sometimes become available for sale due to economic conditions, the planned or unplanned divestiture of assets or as the result of legal remedies. These assets are often referred to as distressed financial assets. The sale of thousands of commercial loans or other financial instruments sometimes involving the equivalent of billions of dollars in assets must sometimes occur within a few months. Of course, the seller of assets wants to optimize the value of the portfolio, and will sometimes group the assets in “tranches.” The term “tranche” as used herein is not limited to foreign notes but also includes assets and financial instrument groupings regardless of country or jurisdiction.

[0004] Bidders may submit bids on all tranches, or on only some tranches. In order to win a tranche bid, a bidder typically must submit the highest bid for that tranche. In connection with determining a bid amount to submit on a particular tranche, a bidder often will perform due diligence, such as engaging underwriters to evaluate as many loans as possible within a tranche and within the available limited time. When the time for submitting a bid is about to expire, the bidder will evaluate the loans underwritten at that time, and then attempt to extrapolate a value to the loans that have not yet been analyzed by the underwriters.

[0005] As a result of this process, a bidder may significantly undervalue a tranche and submit a bid that is not competitive or bid higher than the underwritten value and assume unquantified risk. Since the objective is to win each tranche at a price that enables a bidder to earn a return, losing a tranche due to significant undervaluation of the tranche represents a lost opportunity.

[0006] Currently, business enterprises assess an acquisition or sale of assets and portfolios of assets on rapid schedules and often at great distances and varying time zones from the general management teams and functional heads which typically approve the offers for purchase or sale of these assets. Employees, partners and collaborators associated with due diligence regarding the purchase of the assets are typically brought together for a relatively short duration of time to accomplish the due diligence. Typically due diligence activities are conducted in physical proximity to the sources of information associated with the assets. The collaborating personnel often do not have the benefit of training or knowledge of the complete set of analytical tools at their disposal nor do they have “best practices” from previous efforts of a similar nature.

[0007] Consolidation of employees and collaborators into a remote physical location for the duration of the due diligence effort is time consuming and expensive. In addition, persons on due diligence teams search for data and processes in an ad hock fashion, typically relying on a small subset of other personnel who have detailed information about information sources, underwriting, analytical tools, reports, and completed analysis. The subset of individuals who have the information become bottlenecks within a due diligence time line, driving up due diligence costs and adding time that could have otherwise been invested in more value added due diligence.

[0008] In summary, there are several factors that typically prevent a substantive analysis on portfolios of distressed financial assets. Some of these factors include incomplete information, limited time to bid date, alternative possible dispositions or resolutions of each asset, expense associated with gathering information, issues related to underwriting and legal, variation of expected assets resolution tinting, uncertain future expenses related to collection on assets, large number of assets in a portfolio and model development for financial analysis.

[0009] In one aspect, an integrated Internet based system for managing portfolio cash valuation is provided. The system includes at least one computer, and at least one server. The computer is connected to server via a network and is configured to store accumulated assumptions and knowledge in a repository from a prior portfolio cash evaluation, apply consolidated analytical tools to evaluate the portfolio of assets, and generate management reports that analyze the portfolio. In an exemplary embodiment, the system includes a database, which may be utilized to store assumptions and retrieve as necessary. The server is further configured with consolidated analytical tools including at least one of a Cash Flow Model, a Monte Carlo Simulation Model and a Financial Analysis Model. In an exemplary embodiment, these models reside on a client system operationally. In another exemplary embodiment, the Cash Flow Model, the Monte Carlo Simulation Model and the Financial Analysis Model can reside on the server for distribution to client systems. The Financial Model is capable of exporting data from the cash flow model in a predefined format for use in a financial model. The server is further configured with a suite of at least one of business processes, computer systems, analytical tools, data manipulation tools, business process tools, methodologies and analytics. In an exemplary embodiment, the server is further configured with a database that accumulates and organizes data relating to at least one Bank Records, Credit Agencies, Government Agencies, Legal Documents and Contracts, and Underwriting Reports.

[0010] In another aspect, a method for analyzing portfolios of distressed financial assets for the purpose of bidding to acquire those assets is provided. The method utilizes a network-based system including a server system coupled to a centralized database and at least one client system. The method comprises the steps of generating a cash flow data table from various data sources, importing cash flow data from the data table into a cash flow model, automatically segmenting cash flow data by potential asset disposition types utilizing the cash flow model, applying disposition specific cash flow and expense timings based on cash flow model assumptions and rolling up discounted projections into total deal level cash flow projections, performing a sensitivity analysis using a Monte Carlo Simulation Model to provide different scenarios based on a variety of assumptions retrieved from the database, and exporting cash flow projections into a pre-determined format to develop financially attractive bids, which have strong probability of expected return on investment after taking into account a variety of foreseeable risks.

[0011] In yet another aspect, a computer program embodied on a computer readable medium is provided. The computer program includes a code segment that sets up a directory structure to organize information into a centralized database and provides users access to a specific set of data stored in the centralized database to facilitate decision making in response to an inquiry. Additionally, the computer program includes a code segment that downloads valuation assessment from the database, develops monthly income projections from individual loan valuations, develops monthly expense projections from pre-determined asset management scenarios, aggregates loan cash flows into portfolio cash flows, adjusts portfolio cash flow expenses against pre-determined asset management targets, calculates financial ratios for asset management planning, simulates various scenarios based on pre-defined assumptions, and calculates confidence assessment for portfolio investment.

[0012] In yet a further aspect, a centralized database is provided which includes data corresponding to at least one of Cash Flow Data, Assumptions Data, Potential Asset Disposition Type Data, Standardized Data, and Worksheets & Code Modules Data, data corresponding to financial models and business process tools, data corresponding to best practices, and data corresponding valuation process and underwriting. In yet another aspect, a method for analyzing a deal utilizing a borrower level pricing process is disclosed. The method includes calculating a borrower-specific price for each borrower in the portfolio, determining influence of each borrower on a given portfolio utilizing influence metrics, and selecting a group of borrowers based on borrowers individual ranking for further review.

[0027] Exemplary embodiments of systems and processes that facilitate integrated network-based electronic reporting and workflow process management related to a Portfolio Cash Valuation System (PCVS) are described below in detail. The systems and processes facilitate, for example, electronic submission of information using a client system, automated extraction of information, and web-based assessment reporting and management.

[0028] The systems and processes are not limited to the specific embodiments described herein. In addition, components of each system and each process can be practiced independent and separate from other components and processes described herein. Each component and process also can be used in combination with other components and processes.

[0029] In an exemplary embodiment, the application is implemented as a Centralized Database utilizing a Structured Query Language (SQL) with a client user interface front-end for administration and a web interface for standard user input and reports. The application is web enabled and runs on a business entity's intranet. In a further exemplary embodiment, the application is fully accessed by individuals having authorized access outside the firewall of the business entity through the Internet. In another exemplary embodiment, the application is run in a Windows NT environment or simply on a stand alone computer system. In yet another exemplary embodiment, the application is practiced by simply utilizing spreadsheet software or even through manual process steps. The application is flexible and designed to run in various different environments without compromising any major functionality.

[0030] FIG. 1 is a block diagram of PCVS 10 that includes a server sub-system 12, sometimes referred to herein as server 12 or server system 12, and a plurality of customer devices 14 connected to server 12. PCVS 10 can be applied to distressed asset portfolio analysis. Computerized modeling and grouping tools, as described below in more detail, are stored in server 12 and can be accessed by a requester at any one of computers 14. In one embodiment, devices 14 are computers including a web browser, and server 12 is accessible to devices 14 via a network such as an intranet or a wide area network such as the Internet. In an alternative embodiment, devices 14 are servers for a network of customer devices. Customer device 14 could also be any client system capable of interconnecting to the Internet including a web based digital assistant, a web-based phone or other web-based connectable equipment. In another embodiment, server 12 is configured to accept information over a telephone, for example, at least one of a voice responsive system where a user enters spoken data, or by a menu system where a user enters a data request using the touch keys of a telephone as prompted by server 12.

[0031] Devices 14 are interconnected to the network, such as a local area network (LAN) or a wide area network (WAN), through many interfaces including dial-in-connections, cable modems and high-speed lines. Alternatively, devices 14 are any device capable of interconnecting to a network including a web-based phone or other web-based connectable equipment. Server 12 includes a database server 16 connected to a centralized database 20. In one embodiment, centralized database 20 is stored on database server 16 and is accessed by potential customers at one of customer devices 14 by logging onto server sub-system 12 through one of customer devices 14. In an alternative embodiment, centralized database 20 is stored remotely from server 12.

[0032] In one exemplary embodiment, PCVS 10 is configured to store accumulated assumptions and knowledge in a repository from a prior portfolio cash evaluation, apply consolidated analytical tools to evaluate the portfolio of assets, and generate management reports that analyze the portfolio. In an exemplary embodiment, system 10 utilizes database 20 to store assumptions and retrieve as necessary. Server 12 is further configured with consolidated analytical tools including at least one of a Cash Flow Model, a Monte Carlo Simulation Model and a Financial Analysis Model. In an exemplary embodiment, these models reside on a customer devices 14 operationally. In another exemplary embodiment, the Cash Flow Model, the Monte Carlo Simulation Model and the Financial Analysis Model can reside on server 12 for distribution to customer devices 14. The Financial Model is capable of exporting data from the cash flow model in a predefined format for use in a financial model. Server 12 is further configured with a suite of at least one of business processes, computer systems, analytical tools, data manipulation tools, business process tools, methodologies and analytics. In an exemplary embodiment, server 12 is further configured with a database that accumulates and organizes data relating to at least one Bank Records, Credit Agencies, Government Agencies, Legal Documents and Contracts, and Underwriting Reports.

[0033] In yet another exemplary embodiment, server 12 is configured to execute a computer program embodied on a computer readable medium. Customer device 14 accesses the computer program that has been stored on server 12 to analyze portfolios using portfolio cash valuation analysis. The computer program includes a code segment that sets up a directory structure to organize information into a centralized database and provides users access to a specific set of data stored in the centralized database to facilitate decision making in response to an inquiry. Additionally, the computer program includes a code segment that downloads valuation assessment from the database, develops monthly income projections from individual loan valuations, develops monthly expense projections from pre-determined asset management scenarios, aggregates loan cash flows into portfolio cash flows, adjusts portfolio cash flow expenses against pre-determined asset management targets, calculates financial ratios for asset management planning, simulates various scenarios based on pre-defined assumptions, and calculates confidence assessment for portfolio investment.

[0034] FIG. 2 is an expanded version block diagram of an exemplary embodiment of a server architecture of a PCVS 22. PCVS 22 is implemented for the complex environment. Components in PCVS 22, identical to components of system 10 (shown in FIG. 1), are identified in FIG. 2 using the same reference numerals used in FIG. 1. PCVS 22 includes server sub-system 12 and customer devices 14. Server sub-system 12 includes database server 16, an application server 24, a web server 26, a fax server 28, a directory server 30, and a mail server 32. A disk storage unit 34 is coupled to database server 16 and directory server 30. Servers 16, 24, 26, 28, 30, and 32 are coupled in a local area network (LAN) 36. In addition, a system administrator workstation 38, a workstation 40, and a supervisor workstation 42 are coupled to LAN 36. Alternatively, workstations 38, 40, and 42 are coupled to LAN 36 via an Internet link or are connected through an intranet.

[0035] Each workstation 38, 40, and 42 is a personal computer including a web browser. Although the functions performed at the workstations typically are illustrated as being performed at respective workstations 38, 40, and 42, such functions can be performed at one of many personal computers coupled to LAN 36. Workstations 38, 40, and 42 are illustrated as being associated with separate functions only to facilitate an understanding of the different types of functions that can be performed by individuals having access to LAN 36.

[0036] Server sub-system 12 is configured to be communicatively coupled to various individuals or employees 44 and to third parties, e.g., a customer 46 via an ISP Internet connection 48. The communication in the exemplary embodiment is illustrated as being performed via the Internet, however, any other wide area network (WAN) type communication can be utilized in other embodiments, i.e., the systems and processes are not limited to being practiced via the Internet. In addition, and rather than a WAN 50, local area network 36 could be used in place of WAN 50.

[0037] In the exemplary embodiment, any employee 44 or customer 46 having a workstation 52 can access server sub-system 12. One of customer devices 14 includes a workstation 54 located at a remote location. Workstations 52 and 54 are personal computers including a web browser. Also, workstations 52 and 54 are configured to communicate with server sub-system 12. Furthermore, fax server 28 communicates with employees 44 and customers 46 located outside the business entity and any of the remotely located customer systems, including a customer system 56 via a telephone link. Fax server 28 is configured to communicate with other workstations 38, 40, and 42 as well.

[0038] The systems described in FIGS. 1 and 2 are configured to implement a methodology to analyze distressed portfolios, based upon actual historical cost data, predicted operations and cost data and actual cost data, where applicable. By determining a likely expenses stream, a net present value can be placed on the portfolios and its underlying assets and establish target bids that will result into profitability, if accepted.

[0039] FIG. 3 shows a configuration of database 20 within database server 16 of server system 12 shown in FIG. 1. Database 20 is coupled to several separate components within server system 12. These separate components perform specific tasks as required to achieve the system functionality.

[0040] Server system 12 includes a collection component 64 for collecting information from users into centralized database 20, a tracking component 66 for tracking information, a displaying component 68 to display information, a receiving component 70 to receive a specific query from client system 14, and an accessing component 72 to access centralized database 20. Receiving component 70 is programmed for receiving a specific query from one of a plurality of users. Server system 12 further includes a processing component 76 for searching and processing received queries against data storage device 34 containing a variety of information collected by collection component 64. An information fulfillment component 78, located in server system 12, downloads the requested information to the plurality of users in the order in which the requests were received by receiving component 70. Information fulfillment component 78 downloads the information after the information is retrieved from data storage device 34 by a retrieving component 80. Retrieving component 80 retrieves, downloads and sends information to client system 14 based on a query received from client system 14 regarding various alternatives.

[0041] Retrieving component 80 further includes a display component 84 configured to download information to be displayed on a client system's graphical user interface and a printing component 88 configured to print information. Retrieving component 80 generates various reports requested by the user through client system 14 in a pre-determined format. System 10 is flexible to provide alternative reports and is not constrained to the options set forth above.

[0042] In an exemplary embodiment, database 20 is divided into a Cash Flow Data Section (CFDS) 90, Models Algorithm Section (MAS) 92, Assumptions Section (AS) 94, Standardized Data section (SDS) 96, and Worksheets & Code Modules Section (WCMS) 98. Sections 90, 92, 94, 96 and 98 within database 20 are interconnected to update and retrieve the information as required. Each Section is further divided into several individualized sub-sections to store data in various different categories. In yet another exemplary embodiment, customized sections are developed using key evaluation metrics.

[0043] The architecture of system 10 as well as various components of system 10 are exemplary only. Other architectures are possible and can be utilized in connection with practicing the processes described below.

[0044] FIG. 4 is an exemplary embodiment of a logical structure 108 of PCVS 10 (shown in FIG. 1) and PCVS 22 (shown in FIG. 2). The PCVS integrates a Structured Database Table 110, a Cash Flow Model 112, and a Monte Carlo Simulation Model 114 to perform the portfolio analysis and provide recommendations to management based on results obtained from the analysis. Database table 110 resides in a relational database designed to standardize the collection and organization of many disparate data sources 116 typically required to properly analyze a large portfolio of assets; (bank records, credit agencies, government agencies, legal documents, underwriting reports, etc.). As part of the data analysis process, database table 110 of projected potential recoveries is populated. In an exemplary embodiment, database table 110 is queried from the EXCEL application to import data records into cash flow model 112.

[0045] Cash flow model 112, also referred to as a Portfolio Cash Valuation Model, includes several worksheets and a series of code modules 118. The code modules control querying the database and processing the retrieved data records.

[0046] In an exemplary embodiment, code modules are developed using Visual Basic. Visual Basic is a programming language and environment developed by Microsoft Corporation. Based on the BASIC language, Visual Basic was one of the first products to provide a graphical programming environment and a paint metaphor for developing user interfaces. Instead of worrying about syntax details, the Visual Basic programmer can add a substantial amount of code simply by dragging and dropping controls, such as buttons and dialog boxes, and then defining their appearance and behavior.

[0047] However, code modules can be generated using any commercialized available software programs. Cash flow model 112 worksheets are arranged into four main categories of worksheets:

• • [0048] a) Data Sheet—Data sheet holds records retrieved from the database for distribution to resolution sheets. Resolution sheets are often referred to as Disposition sheets. Resolution or Disposition sheets describe various disposition asset types. Data sheet contains several additional calculated fields derived from the imported records and assumption sheet parameters.
  • [0049] b) Assumptions Sheet—Assumptions sheets are input sheet for various model parameters such as interest rates, legal fees, expenses, exchange rates, discount rates and various other parameters that are important in predicting the future situation.
  • [0050] c) Cash flow sheet—Cash flow sheet is the net present value model of aggregate projections of cash flows and expenses by likely asset disposition type.
  • [0051] d) Disposition Sheets—Disposition sheet is one sheet for each possible asset disposition strategy that may be employed in the asset collection effort. Possible disposition of assets are, but not limited to, Discounted Cash Payment or Discounted Pay Off (DPO) Disposition, Inferred Disposition, Loan Restructure Disposition, Compliance Disposition, Litigation with Foreclosure Disposition, Litigation with Restructure Disposition, and Deed In Lieu Disposition. Other disposition types may be developed depending on the type of the assets within the portfolio and the business needs.

[0052] Monte Carlo Simulation Model 114 is used with the assumptions and cash flow worksheets to perform sensitivity and “what if” analysis 120 on the projected cash flows. This allows for the impact of variation in the expected timing of recoveries, amount of recoveries, interest rates, expenses, and other variables to be analyzed. Simulation Model 114 provides a probabilistic distribution of possible portfolio value that incorporates the risk associated with uncertainty of future events.

[0053] FIG. 5 describes a portfolio bidding process 160 as implemented by PCVS 10. Portfolio bidding process 160 begins 162 inputting required information relating to portfolio and its underlying assets including but not limited to identifying a Borrower Identification Code, a Loan Identification Code, Strategy description defining Disposition Type, Timing of Payments and other related information. Data Sources 164 includes the information relating to the portfolio and its underlying assets and information from loan underwriters 168, Knowledge Captured from Previous Transactions 170, and Inference Data obtained from Non-Sampled Assets 172. Data sources 164 are updated constantly and become part of a Database 178. Next step requires importing cash flow data from database 178 into Portfolio Cash Flow Valuation Model 180 (also shown in FIG. 4 as reference numeral 112). The user controlled query allows for segments of the portfolio or pool of assets to be evaluated separately. In an exemplary embodiment, Portfolio Cash Flow Valuation Model 180 automatically segments data by potential disposition types. Model 180 can also handle mixed disposition types of assets. Once the analysis is completed as explained below (in FIG. 6), cash flow model's projections are exported in the proper format into a Finance Model 184 for developing a Bid 186 for submission.

[0054] In an exemplary embodiment, a Borrower-level Pricing Process is implemented by PCVS 10. Borrower level Pricing Process is a module or a sub-set of the Cash Flow Model. In addition to retaining all the functionality of the Cash Flow Model, Borrower Level Pricing Process implements a looping feature that allows the user to run the Cash Flow Model for every borrower in the portfolio (as if a borrower were a portfolio of one), thereby calculating each borrower's net present value of cash flows, i,e. each borrower's price. The looping feature allows the user to run an analysis for any subset of portfolio, and calculate the subset's price, sensitivities, total expenses, etc. Once the borrower level pricing is determined, the system can calculate the price influence (also refereed to as “influence”) of each borrower's individual price, on the entire portfolio's price by utilizing “influence metrics”.

[0055] More specifically, in addition to a determination of a portfolio level price, a loop has been built into algorithm to calculate a borrower-specific price for each borrower in the portfolio. The Borrower level Pricing process includes clearing the database and sorting the database by borrower identification codes (i.e. Borrower Id's). All the records associated with each individual Borrower Id's are rolled up to get the overall contribution by each borrower for a given portfolio. The subsequent step of the process further includes price influence ranking which relates to determining influence of each borrower on a given portfolio. Price influence of a particular borrower is determined using:

[0056] $p_{\left(i\right)}=\frac{\sum _{j\ne i}{p}_j{B}_j}{\sum _{j\ne i}{B}_j}$
is borrower i's deleted price (price of portfolio without borrower i)
where
p_i =borrower i price, and B_i is the borrower i unpaid balance
Then

[0057] $I_{\left(i\right)}=p_{\left(i\right)}-\frac{\sum _j{p}_j{B}_j}{\sum _j{B}_j}$
is borrower i's price influence. If I>0, then borrower i is price deflationary, while if I<0, then borrower i is price inflationary. Rank ordering price influence for every borrower illuminates the price sensitivity to individual borrower pricing.

[0058] In an exemplary embodiment, price influence of a particular borrower is determined using “influence metrics”. The “influence metrics” are developed based on historical experience in dealing with various portfolios. Once the price influence ranking is developed for each borrower based on influence metrics, the borrowers are rank-ordered according to their individual ranking. The most influential borrowers are then selected for further review. The process offers significant competitive pricing advantage and shortens the time needed for portfolio evaluation. The process adds accuracy and consistency in development of successful bids by focusing on borrowers that are most influential.

[0059] FIG. 6 describes, in one exemplary embodiment, a various process steps 190 utilized by PCVS 10 in developing a bid that is financially sound. Process steps 190 utilize six different steps. Step One 192, in one exemplary embodiment, includes generating cash flow data table, also referred to as database 178 (shown in FIG. 5) from various data sources 164 (shown in FIG. 5). Data Sources 164 includes the information relating to portfolio and its underlying assets, information from loan underwriters 168 (shown in FIG. 5), Knowledge Captured from Previous Transactions 170 (shown in FIG. 5), and Inference Data obtained from Non-Sampled Assets 172 (shown in FIG. 5). Data sources 164 are updated constantly and become part of a Database 178. Step Two 194 involves importing cash flow data from data table 178 into cash flow valuation model 180 (shown in FIG. 5) utilizing EXCEL VBA program. Cash flow valuation model 180 is also referred to as Portfolio Cash Flow Valuation Model 180. The user controlled query allows the user to segment portfolio or pool of assets that need to be evaluated separately. Step Three 196 utilizes Portfolio Cash Flow Valuation Model 180 to automatically segment data by potential asset disposition types. In an exemplary embodiment, possible disposition of assets are, Discounted Cash Payment or Discounted Pay Off (DPO) Disposition, Inferred Disposition, Loan Restructure Disposition, Compliance Disposition, Litigation with Foreclosure, Litigation with Restructure Disposition, and Deed In Lieu Disposition. Although model 180 can segment data by a specific disposition type, the model with minor adjustments can also handle mixed dispositions. Step Four 198, then includes, applying disposition specific cash flow and expense timings based on model assumptions and rolling up discounted projections into a total deal level cash flow projections. Step Five 200 includes performing sensitivity analysis using Monte Carlo Simulation Model 114. This analysis is often referred to as a “What-If” analysis. This analysis provides different scenarios to the management based on different assumptions related to key parameters. PCVS 10 retrieves assumptions on which various scenarios are developed from the assumptions input sheet (discussed below). Step Six 202, exports cash flow model projections into the proper format for use by the Finance Model 184 (shown in FIG. 5) to develop bids 186 (shown in FIG. 5) that are financially attractive and provides strong probability of expected return on investment after taking into account foreseeable risks.

[0060] FIG. 7 is a diagram illustrating an overlap of portfolio bidding process 160 (shown in FIG. 5) and various process steps 190 (shown in FIG. 6) utilized by PCVS 10 in developing a bid that is financially sound.

[0061] FIG. 8 is an exemplary embodiment of process steps 220 to create cash flow data table, also referred to as database 178 (shown in FIG. 5) from various data sources 164 (shown in FIG. 5). Various queries are ran to create cash flow data from database 178, which in turn generates cash flow table. Data queried from database 178 is placed into a data worksheet in the model 180 workbook.

[0062] FIG. 9 is an exemplary embodiment of a user interface 230 displaying the imported data from the cash flow model 180 (shown in FIG. 5). A Load Cash Flow Model 232 button imports data into the cash flow model 180. A Move Data 234 button performs required computations and displays what is being shown in user interface 230. Move Data 234 command executes the “Move To Disposition” function. Move Data 234 command transfers each record data to the proper disposition type. FIG. 10 below, in an exemplary embodiment, depicts the raw data, which were inputted into database 178. Appropriate assumptions are retrieved from the assumption worksheet shown in FIG. 11 below. Expected Cash flow and Net Present Value (NPV) are computed based on received cash flow information, expenses and timings. The actual data are utilized as and when possible. However, when actual data are not available, model 180 retrieves necessary data from the assumption worksheet, shown in FIG. 11 to complete the analysis.

[0063] A VBA routine displays list of asset pools, or the user can select a specific pool of assets out of possible disposition of assets such as Discounted Cash Payment or Discounted Pay Off (DPO) Disposition 236, Inferred Disposition 238, Loan Restructure Disposition 240, Compliance Disposition 242, Litigation with Foreclosure Disposition 244, Litigation with Restructure Disposition 246, and Deed In Lieu Disposition 248. Alternatively, the user can select to view all available records by selecting a Cash Flow 250 tab. Each of these disposition types is available for viewing under the specific tab identified in FIG. 9.

[0064] Explanations of the calculation of the various disposition types are discussed below. However, it is important to note that the underlying explanations are for a specific point in time on a specific deal (or a transaction) and are only exemplary in nature. Minor adjustments may be made to these methodologies to accommodate a new deal depending on a Country where the transaction is being undertaken, Tax structure of the country, county and state, Tax and other Legal expenses, and general expenses based on the deal size. The model provides flexibility to accommodate these changes and provides reliable NPV and Return on Investment (ROI) values.

[0065] Discounted Pay Off (DPO) Disposition 236

[0066] DPO records are contained in the DPO Disposition 236 tab. DPO strategy involves two potential cash payments at two different timings, T1 and T2. Expenses are incurred at T1 and T2. Total outstanding Unpaid Balance (UPB) decreases as payments are received. The aggregate cash flows and expenses are totaled at the top of the worksheet.

[0067] Timings:

[0068] Various timings are obtained as part of the underwriting data from the database 178. The timings are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions worksheet (shown in FIG. 11). Each record's payment and expense timings are adjusted based on the UPB bin that the record is associated with.

[0069] Expenses:

[0070] Legal expenses are incurred at the time of the cash payments. Legal expenses are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB.” DPO expenses are calculated during the “move data” function and placed in the expense section of the DPO Disposition 236 tab according the payment timing.

[0071] All legal expenses are adjusted using the Value Added Tax (VAT) located on the assumptions worksheet.

[0072] Expected Recovery (ER) is the amount realized from the borrower or a debtor. In other words, ER is the payment from the borrower. In an exemplary embodiment, let us assume that for, ER=$842,238, the borrower is expected to make payments at Time period T1 (T1)=12 and at Time period T2 (T2) =18. Based on the facts of a specific scenario, ReSecured=1 implying that the loan is secured by the real property. In other words, there is a collateral. Based on these facts, the Results computed by the model are as follows:

• • [0073] Cash payment at Time Period 12 of $421,119
  • [0074] Cash payment at Time Period 18 of $421,119

[0075] Expenses are retrieved from assumption worksheet stored in database 178. For example, to obtain Legal Expenses at T1, the model retrieves the data from “legal ReSecured” and determines that the legal expenses are 13.8% of Cash payment at $421,111, which is $58,114 Million ($421,111 Million multiplied by 0.138 factor retrieved from the assumption worksheet). Similarly, Legal Expenses at T2 are 13.8% of Cash payment at $421,118, which is $58,114 Million ($421,111 Million multiplied by 0.138 factor retrieved from the assumption worksheet).

[0076] Inferred Disposition 238

[0077] Inferred records are contained in the Inferred Disposition 238 tab. Inferred strategy involves two potential cash payments, T1 and T2. Expenses are incurred at T1 and T2. Total outstanding Unpaid Balance (UPB) decreases as payments are received. The aggregate cash flows and expenses are totaled at the top of the worksheet.

[0078] Timings:

[0079] Various timings are obtained as part of the underwriting data from the database 178. The timings are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions worksheet (shown in FIG. 11). Each record's payment and expense timings are adjusted based on the UPB bin that the record is associated with.

[0080] Expenses:

[0081] Legal expenses are incurred at the time of the cash payments. Legal expenses are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB.” Inferred expenses are calculated during the “move data” function and placed in the expense section of the Inferred Disposition 238 tab according the payment timing.

[0082] All legal expenses are adjusted using the Value Added Tax (VAT) located on the assumptions worksheet.

[0083] For, Expected Recovery (ER)=$1,092.52, Time period T1 (T1)=26, Time period T2 (T2)=48, ReSecured=1; the Results computed by the model are as follows:

• • [0084] Cash payment at Time Period 26 of $1073.00
  • [0085] Cash payment at Time Period 48 of $19.52

[0086] Expenses are retrieved from assumption worksheet stored in database 178. For example, to obtain Legal Expenses at T1, the model retrieves the data from “legal ReSecured” and determines that the legal expenses are 23% of Cash payment at $1073.00, which is $246.8 Million ($1073 Million multiplied by 0.23 factor retrieved from the assumption worksheet). Similarly, Legal Expenses at T2 are 23% of Cash payment at $19.52, which is $4.49 Million ($19.52 Million multiplied by 0.23 factor retrieved from the assumption worksheet).

[0087] Loan Restructure Disposition 240

[0088] Restructured loans Disposition records are contained in “Restruct” 240 tab. Restructured loans strategy involves a possible cash payment “T1” and a stream of future payments associated with the new restructured loan. The restructured loan may involve a principle moratorium period where in only interest payments are made.

[0089] The cash flows that result from the restructured loan are contained in two sections of the worksheet. The first section contains the T1 cash payment and the principle payments associated with the loan. This section also contains a cash payment associated with any remaining principle value beyond the term of the analysis (48 months), the remaining value is discounted back at a separate “Residual value discount rate” located on the assumptions sheet. Principle payments are calculated using the PPMT excel function. The second section contains the interest payments associated with the loan. The interest rate is obtained from the “Assumed interest rate (Monthly)” value on the assumptions sheet. The IPMT excel function is used to calculate the interest portion of the cash flow stream. Details pertaining to PPMT and IPMT functions, Timings, Expenses, and an illustrative Example describing methodology are summarized in Appendix-A.

[0090] Compliance Disposition 242

[0091] Compliance Disposition records are contained in “Compliance” 242 tab. Compliance records are handled in the same fashion as the DPO strategy using two potential cash payments, T1 and T2. Expenses are incurred at T1 and T2. Total outstanding UPB is decreased as payments are received. There are three types of Compliance records “Grey-White”, “Grey-Black” and “Black”. The aggregate cash flows and expenses for each type are totaled at the top of the worksheet. Details pertaining to Timings, Expenses, and an illustrative Example describing methodology are summarized in Appendix-A.

[0092] Litigation With Restructured Loans 246

[0093] Litigation With Restructured loans are contained in the “LitigateWRes” 246 tab. Litigation With Restructured loans strategy involves a cash payment “T1” and a possible stream of future payments associated with the new restructured loan.

[0094] The cash flows that result from this strategy are contained in two sections of the worksheet. The first section contains the T1 cash payment and the principle payments associated with the loan. This section also contains a cash payment associated with any remaining principle value beyond the term of the analysis (48 months), the remaining value is discounted back at a separate “Residual value discount rate” located on the assumptions sheet. Principle payments are calculated using the PPMT excel function. The second section contains the interest payments associated with the loan. The interest rate is obtained from the “Assumed interest rate (Monthly)” value on the assumptions sheet. The IPMT excel function is used to calculate the interest portion of the cash flow stream. Details pertaining to PPMT and IPMT functions, Timings, Expenses, and an illustrative Example describing methodology are summarized in Appendix-A.

[0095] Litigation With Foreclosure 244

[0096] Litigation With Foreclosure records are contained in the “LitigateForeClose” 244 tab. This strategy involves two events “T1” and “T2”. T1 can be either the event of obtaining ownership via foreclosure or obtaining ownership and selling the property in the same event. If T1 is a sales transaction then there would be no T2 event. T2 will occur when the obtaining ownership event and sales events are at different timings. The first section contains the T1 OR T2 cash payment and represent the recovered amount associated with the sales transaction. Timings are obtained as part of the underwriting data from the database. Timings are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's event timings are adjusted based on the UPB bin that the record is associated with.

[0097] Expenses are calculated in the second section of the worksheet. Legal expenses are incurred at the time of each event and are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB”. In addition to legal expenses, restate transaction expenses are incurred at each event. These expenses are obtained from the “Closing Costs Table LF and DIL (% of GDP)” section of the assumptions sheet.

[0098] Some of these expenses are incurred at both events, (Pub Notary, Pub Registry, State Aq. Tax, Appraisal Fee and Brokerage fees) Other are dependent on the “Resecured” and “InLitigation” fields. Other details pertaining to Litigation With Foreclosure and an illustrative Example describing methodology are summarized in Appendix-A.

[0099] Deed in Lieu 248

[0100] Deed In Lieu records are contained in the “DeedInLieu” 248 tab. Deed In Lieu strategy involves two events “T1” and “T2”. T1 can be either the event of obtaining ownership via foreclosure or obtaining ownership and selling the property in the same event. If T1 is a sales transaction then there would be no T2 event. T2 will occur when the obtaining ownership event and sales events are at different timings.

[0101] The first section contains the T1 OR T2 cash payment and represent the recovered amount associated with the sales transaction. Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's event timings are adjusted based on the UPB bin that the record is associated with. Other details pertaining to Deed in Lieu and an illustrative Example describing methodology are summarized in Appendix-A.

[0102] FIG. 10 is an exemplary embodiment of a spreadsheet 260 depicting the raw data, which were inputted into database 178. The data is segregated by a borrower identification number, a loan identification number, an asset type and other related attributes.

[0103] FIG. 11 is an exemplary embodiment of an Assumption Sheet 270 also referred to as Assumption Data Sheet or Assumption Worksheet. Appropriate assumptions are stored in database 20 and are retrieved as necessary. Expected Cash flow and Net Present Value (NPV) are computed based on received cash flow information, expenses and timings. The actual data are utilized as and when possible. However, when actual data are not available, model 180 retrieves necessary data from the assumption worksheet, shown in FIG. 11 to complete the analysis. Assumption worksheet includes assumptions related to many different variables including, but not limited to, Disposition Discount Rates, Value Added Tax Rates, Set Up Costs, Conversion and Loan Registration Costs, Asset Management expenses, Legal Fees based on Recovered Amount, Closing Costs related to Different Disposition Types, Various Different Rates and Factors, Economic Data, Sensitivity Assumptions and Financial Variables that are necessary in performing financial analysis. Assumptions are also changed by the user to control disposition expenses and discount rates.

[0104] FIG. 12 is an exemplary embodiment of a spreadsheet 280 depicting a roll up disposition cash flow into portfolio cash flow. In this example, each disposition cash flow 282 and expense 284 totals are linked to develop overall portfolio roll up cash flow 286. Portfolio cash flow 286 is made up of cash flows from various disposition types including, but not limited to, Discounted Cash Payment or Discounted Pay Off (DPO) Disposition 288, Inferred Disposition 290, Loan Restructure Disposition 292, Compliance Disposition 294, Litigation with Restructure Disposition 296, Litigation with Foreclosure Disposition 298, and Deed In Lieu Disposition 300. Global expenses and discount rate assumptions are applied from assumption worksheet 302 (also shown in FIG. 11). Monte Carlo Simulation Model 114 is used to test the impact of the uncertainty associated with assumptions. Results are exported in a pre-determined format to feed the financial modeling process (shown in FIG. 14).

[0105] FIG. 13 is an exemplary simulation results. FIG. 13 depicts a spreadsheet 320 identifying Cash flows relating to each disposition type 322, Expenses related to each disposition type 324, Total Cash Flow 326, Total Expenses 328, Net Present Value (NPV) 330, Bid Price 332, and an Internal Rate of Return (IRR) 334. Cash flows and expenses are segregated over a specific period, i.e. month by month 336. Pop up windows 340 allow the user to view the results relating to IRR and Time Adjusted NPV and help forecast the success of a specific bid.

[0106] FIG. 14 is an exemplary embodiment of a spreadsheet 350 showing possible result distributions 354 based on uncertainty and variability of future cash flows. Spreadsheet 350 also depicts sensitivity analysis 358 on key input parameters.

[0107] While the invention has been described in terms of various specific embodiments, those skilled in the art will recognize that the invention can be practiced with modification within the spirit and scope of the claims.

[0108] Calculations for the individual disposition types enclosed here are general in form and can easily be modified to reflect slight changes in disposition structure from deal to deal.

[0109] DPO—Discounted Pay Off—strategy involves two potential cash payments, timing 1 (T1) and timing 2 (T2). Expenses are incurred at T1 and T2. Total outstanding Unpaid balance (UPB) is decreases as payments are received. There are two types of DPO records Easy and Hard DPOs, The aggregate cash flows and expenses for each type are totaled at the top of the worksheet.

[0110] Timings:

[0111] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's payment and expense timings are adjusted based on the UPB bin that the record is associated with.

[0112] Expenses:

[0113] Legal expenses are incurred at the time of the cash payments. These expenses are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB” . DPO expenses are calculated during the “move data” function and placed in the expense section of the DPO tab according the payment timing.

[0114] All legal expense are adjusted using the Value added tax (VAT) tax also located on the assumptions page.

[0115] Expected Recovery (ER)=$842,238, T1=12 months, T2=18 months ReSecured=1 a secured loan

[0116] flag:

[0117] Results:

• [0118] Cash payment at Time 12 of 421,119
• [0119] Cash payment at Time 18 of 421,119

[0120] Expenses:

• [0121] T1 Legal—Lookup in legal ReSecured 421,119=13.8%; Expense=58,114
• [0122] T2 Legal—Lookup in legal ReSecured 421,119=13.8%; Expense=58,114

[0123] These values are calculated and placed in the correct timing slot in on the DPO tab during the move data function execution.

[0124] Inferred records are generated via proprietary technology and involve two potential cash payments, T1 and T2. Expenses are incurred at T1 and T2. Total outstanding UPB is decreases as payments are received. The aggregate cash flows and expenses are totaled at the top of the worksheet.

[0125] Timings:

[0126] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's payment and expense timings are adjusted based on the UPB bin that the record is associated with.

[0127] Expenses:

[0128] Legal expenses are incurred at the time of the cash payments. These expenses are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB”. Inferred expenses are calculated during the “move data” function and placed in the expense section of the Infer tab according the payment timing.

[0129] All legal expense are adjusted using the VAT tax also located on the assumptions page.

• [0130] ER=$1,092.52, T1=26, T2=48 ReSecured=1:

[0131] Results

• [0132] Cash payment at Time 26 of 1073
• [0133] Cash payment at Time 48 of 19.51

[0134] Expenses:

• [0135] T1 Legal—Lookup in legal ReSecured 1073=23%; Expense=246.8
• [0136] T2 Legal—Lookup in legal ReSecured 19.51=23%; Expense=4.487

[0137] Compliance records refer to questionable borrowers due to legal of other circumstances and are handled in the same fashion as the DPO strategy using two potential cash payments, T1 and T2. Expenses are incurred at T1 and T2. Total outstanding UPB is decreases as payments are received. There are three types of Compliance records “Grey-White”, “Grey-Black” and “Black”, The aggregate cash flows and expenses for each type are totaled at the top of the worksheet.

[0138] Timings:

[0139] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's payment timings are adjusted based on the UPB bin that the record is associated with.

[0140] Expenses:

[0141] Legal expenses are incurred at the time of the cash payments. These expenses are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB”. Compliance record expenses are calculated during the “move data” function and placed in the expense section of the Compliance tab according the payment timing.

[0142] All legal expense are adjusted using the VAT tax also located on the assumptions page.

• [0143] ER=$2,675,443, T1=18, ReSecured=1:

[0144] Results:

• [0145] One cash payment at Time 18 of 2,675,443

[0146] Expenses:

[0147] T1 Legal—Lookup in legal ReSecured 2,675,443=7.59%; Expense=203,066

[0148] Restructured loans are contained in the “Restruct” tab. This strategy involves a possible cash payment “T1” and a stream of future payments associated with the new restructured loan. The restructured loan may involve a principle moratorium period where in only interest payments are made.

[0149] Cash Flows:

[0150] The cash flows that result from the restructured loan are contained in two sections of the worksheet. The first section contains the T1 cash payment and the principle payments associated with the loan. This section also contains a cash payment associated with any remaining principle value beyond the term of the analysis (48 months), the remaining value is discounted back at a separate “Residual value discount rate” located on the assumptions sheet. Principle payments are calculated using the PPMT excel function.

[0151] The format of the PPMT function is:

• [0152] Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

[0153] Syntax:

• [0154] PPMT(rate,per,nper,pv,fv,type)
• [0155] Rate is the interest rate per period.
• [0156] Per specifies the period and must be in the range 1 to nper.
• [0157] Nper is the total number of payment periods in an annuity.
• [0158] Pv is the present value ¾ the total amount that a series of future payments is worth now.
• [0159] Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• [0160] Type is the number 0 or 1 and indicates when payments are due.
• [0161] Set type equal to If payments are due (0 or omitted At the end of the period, 1 At the beginning of the period)

[0162] PPMT(Assumptions!$D$5,($L$19:$BG$19)−$F21,($G21-$F21),$E21,0,0) This is applied only if the period/month falls within the loan period.

[0163]

Assumptions!$D$5 =	monthly interest rate
($L$19:$BG$19)-$F21 =	This period/month minus the period/month that
	the interest only payments end.
($G21-$F21) =	Length of loan - Nper - last payment timing
	minus end of interest only period.
$E21 =	Lon Amount
0 =	FV = 0
0 =	Payment received at end of period.

[0164] The second section contains the interest payments associated with the loan. The interest rate is obtained from the “Assumed interest rate (Monthly)” value on the assumptions sheet. The IPMT excel function is used to calculate the interest portion of the cash flow stream.

[0165] The format of the IPMT function is:

[0166] Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions, see PV.

• [0167] Syntax
• [0168] IPMT(rate,per,nper,pv,fv,type)
• [0169] Rate is the interest rate per period.
• [0170] Per is the period for which you want to find the interest and must be in the range 1 to nper.
• [0171] Nper is the total number of payment periods in an annuity.
• [0172] Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.
• [0173] Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
• [0174] Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

[0175] IPMT(Assumptions!$D$5,($BK$19:$DF$19)−D21,($G21−$D21),$E21) This is applied only if a interest payment month is encountered.

[0176]

Assumptions!$D$5 =	monthly interest rate
($BK$19:$DF$19)-D21 =	This period/month minus the T1
	period/month that represent the
	start of the loan.
($G21-$D21) =	The length of the loan period
$E21 =	The Loan Amount.
Omitted =	FV = 0.
Omitted =	Payment received at end of period.

[0177] Timings:

[0178] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's payment timings are adjusted based on the UPB bin that the record is associated with.

• [0179] T1 is the timing of the cash payment.
• [0180] T1+MonthsOfFinancing field=length of loan.
• [0181] T1+MonthsOfPrincMoratorium=length of interest only period.

[0182] Expenses:

[0183] Legal expenses are incurred at the time of the cash payment and at the start of the principle payment period and are calculated as a percentage of the recovered amounts (Cash Payment at T1 and the Loan Amount). The percentage used is obtained from the “Legal fees based on % recovered UPB”.

[0184] All legal expense are adjusted using the VAT tax also located on the assumptions page.

• • [0185] ER=$819,285, T1=9, T2=12, ReSecured=0:

[0186] Results:

• [0187] One cash payment at Time 9 of 40,964
• [0188] Loan start Principle+Interest Time 12, loan Amount=778,320

[0189] Expenses:

• [0190] T1 Legal—Lookup in legal Not secured 40,964=28.75%; Expense=11,777
• [0191] T2 Legal—Lookup in legal Not secured 778,320=11.5%; Expense=89,507

[0192] Litigation With Restructured loans are contained in the “LitigateWRes” tab. This strategy involves a cash payment “T1” and a possible stream of future payments associated with the new restructured loan.

[0193] Cash Flows:

[0194] The cash flows that result from this strategy are contained in two sections of the worksheet. The first section contains the T1 cash payment and the principle payments associated with the loan. This section also contains a cash payment associated with any remaining principle value beyond the term of the analysis (48 months), the remaining value is discounted back at a separate “Residual value discount rate” located on the assumptions sheet. Principle payments are calculated using the PPMT excel function.

[0195] The format of the PPMT function is:

[0196] Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

• [0197] Syntax:
• [0198] PPMT(rate,per,nper,pv,fv,type)
• [0199] Rate is the interest rate per period.
• [0200] Per specifies the period and must be in the range 1 to nper.
• [0201] Nper is the total number of payment periods in an annuity.
• [0202] Pv is the present value of the total amount that a series of future payments is worth now.
• [0203] Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• [0204] Type is the number 0 or 1 and indicates when payments are due.
• [0205] Set type equal to If payments are due (0 or omitted At the end of the period, 1 At the beginning of the period)

[0206] PPMT(Assumptions!$D$5,($M$19:$BH$19)−$F21,($G21−$F21),$E21,0,0) This is applied only if the period/month falls within the loan period.

[0207]

Assumptions!$D$5 =	monthly interest rate
($M$19:$BH$19)-$F21 =	This period/month minus the period/month
	that the interest only payments end.
($G21-$F21) =	Length of loan - Nper - loan end minus loan
	start.
$E21 =	Lon Amount
0 =	FV = 0
0 =	Payment received at end of period.

[0208] The second section contains the interest payments associated with the loan. The interest rate is obtained from the “Assumed interest rate (Monthly)” value on the assumptions sheet. The IPMT excel function is used to calculate the interest portion of the cash flow stream.

[0209] The format of the IPMT function is: Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions, see PV.

• [0210] Syntax
• [0211] IPMT(rate,per,nper,pv,fv,type)
• [0212] Rate is the interest rate per period.
• [0213] Per is the period for which you want to find the interest and must be in the range 1 to nper.
• [0214] Nper is the total number of payment periods in an annuity.
• [0215] Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.
• [0216] Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
• [0217] Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

[0218] IPMT(Assumptions!$D$5,($BK$19:$DF$19)−D21,($G21−$D21),$E21) This is applied only if a interest payment month is encountered.

[0219]

Assumptions!$D$5 =	monthly interest rate
($BK$19:$DF$19)-D21 =	This period/month minus the T1
	period/month that represent the start
	of the loan.
($G21-$D21) =	The length of the loan period
$E21 =	The Loan Amount.
Omitted =	FV = 0.
Omitted =	Payment received at end of period.

[0220] Timings:

[0221] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's payment timings are adjusted based on the UPB bin that the record is associated with.

• [0222] T1 is the timing of the cash payment.
• [0223] T1+MonthsOfFinancing field=length of loan.
• [0224] T1+MonthsOfPrincMoratorium=length of interest only period.

[0225] Expenses:

[0226] Legal expenses are incurred at the time of the cash payment and at the start of the principle payment period and are calculated as a percentage of the recovered amounts (Cash Payment at T1 and the Loan Amount). The percentage used is obtained from the “Legal fees based on % recovered UPB”.

[0227] All legal expense are adjusted using the VAT tax also located on the assumptions page.

• [0228] ER=$9,134,497, T1=36, ReSecured=0

[0229] Results:

• [0230] One cash payment at Time 36 of $9,134,497.

[0231] Expenses:

• [0232] T1 Legal—Lookup in legal Not secured 9,134,497=4.60%; Expense=$420,187

[0233] Litigation With Foreclosure records are contained in the “LitigateForeClose” tab. This strategy involves two events “T1” and “T2”. T1 can be either the event of obtaining ownership via foreclosure or obtaining ownership and selling the property in the same event. If T1 is a sales transaction then there would be no T2 event. T2 will occur when the obtaining ownership event and sales events are at different timings.

[0234] Cash Flows:

[0235] The first section contains the T1 OR T2 cash payment and represent the recovered amount associated with the sales transaction.

[0236] Timings:

[0237] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's event timings are adjusted based on the UPB bin that the record is associated with.

[0238] NOTE: Any Timing beyond 48 months will occur in the 48th month.

[0239] Expenses:

[0240] Expenses are calculated in the second section of the worksheet.

[0241] Legal expenses are incurred at the time of each event and are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB”.

[0242] In addition to legal expenses, restate transaction expenses are incurred at each event. These expenses are obtained from the “Closing Costs Table LF and DIL (% of GDP)” section of the assumptions sheet. some of these expenses are incurred at both events, (Pub Notary,Pub Registry,State Aq. Tax, Appraisal Fee and Brokerage fees)

[0243] Other are dependent on the Resecured and InLitigation fields:

• [0244] If the record is Real-estate secured ReSecured=1 then Certificate filing fees apply
• [0245] If the record is in litigation InLitigation=1 then Litigation Publishing fees apply at T1

[0246] If the record is NOT in litigation InLitigation=0 then New Litigation fees apply at T1

[0247] NOTE: Any Timing beyond 48 months will occur in the 48th month.

[0248] All legal expense are adjusted using the VAT tax also located on the assumptions page.

• [0249] ER=$1,066,835, T1=39, T2=51, ReSecured=0, InLitigation=1:

[0250] Results:

[0251] One cash payment at Time 48 of $1,066,835.

[0252] Expenses:

• [0253] T1 Legal—Lookup in legal Not secured 1,050,000=5.75%; Expense=$61,343
• [0254] Closing—1,050,000 * 3.6212%(sum of Pub Notary, Pub Registry, State Aq. Tax, Appraisal Fee)=$38,632
• [0255] Plus Litigation Publishing cost of $1,438
• [0256] Total T1 expenses=$101,413
• [0257] T2 Legal—Lookup in legal Not secured 1,050,000=5.75%; Expense=$61,343
• [0258] Closing—1,050,000*4.4338%(Brokerage fee)=$47,301
• [0259] Total T2=$108,644

[0260] Deed In Lieu records refer to cases where deed to property is obtained in lieu of loan repayment and are contained in the “DeedinLieu” tab. This strategy involves two events “T1” and “T2”. T1 can be either the event of obtaining ownership via foreclosure or obtaining ownership and selling the property in the same event. If T1 is a sales transaction then there would be no T2 event. T2 will occur when the obtaining ownership event and sales events are at different timings.

[0261] Cash Flows:

[0262] The first section contains the T1 OR T2 cash payment and represent the recovered amount associated with the sales transaction.

[0263] Timings:

[0264] Timings are obtained as part of the underwriting data from the database. They are adjusted during the “move data” function utilizing the “Delays by UPB bin” section on the assumptions page. Each record's event timings are adjusted based on the UPB bin that the record is associated with.

[0265] NOTE: Any Timing beyond 48 months will occur in the 48th month.

[0266] Expenses:

[0267] Expenses are calculated in the second section of the worksheet.

[0268] Legal expenses are incurred at the time of each event and are calculated as a percentage of the recovered amount. The percentage used is obtained from the “Legal fees based on % recovered UPB” .

[0269] In addition to legal expenses, restate transaction expenses are incurred at each event. These expenses are obtained from the “Closing Costs Table LF and DIL (% of GDP)” section of the assumptions sheet. some of these expenses are incurred at both events, (Pub Notary, Pub Registry, State Aq. Tax, Appraisal Fee and Brokerage fees)

[0270] Other are dependent on the Resecured and InLitigation fields:

• [0271] If the record is Real-estate secured ReSecured=1 then Certificate filing fees apply
• [0272] If the record is in litigation InLitigation=1 then Litigation Publishing fees apply at T1
• [0273] If the record is NOT in litigation InLitigation=0 then New Litigation fees apply at T1

[0274] NOTE: Any Timing beyond 48 months will occur in the 48th month.

[0275] All legal expense are adjusted using the VAT tax also located on the assumptions page.

• [0276] ER=$1,050,000, T1=27, T2=51, ReSecured=0, InLitigation=1:

[0277] Results:

[0278] One cash payment at Time 48 of 1,050,000.

[0279] Expenses:

• [0280] T1 Legal—Lookup in legal Not secured deedInLieu 1,050,000=5.75%; Expense=60,375
• [0281] Closing—1,050,000*3.6212%(sum of Pub Notary, Pub Registry, State Aq. Tax, Appraisal Fee)=$38022
• [0282] Plus Litigation Publishing cost of $1,438

[0283] Total T1 expenses=99,835

• [0284] T2 Legal—Lookup in legal Not secured deedinLieu 1,050,000=5.75%; Expense=60,375
• [0285] Closing—1,050,000*4.4338%(Brokerage fee)=$46555
• [0286] Total T2=$106,930