Automation of FTP process - full implementation

The previously-implemented semi-automated business-used prototype of the daily fund transfer price (FTP) for the mortgage domain was moved to production from business to IT departments of a large Dutch bank and became an IT system under FAM responsibility.

The resulting fully-automated application was built on a consistent technology stack, which includes .NET, Visual Studio, C#, SQL-Server, SSIS package, QRM. The tool is accompanied by the corresponding graphical user interface (GUI) which was developed in ASP.NET.

This project turned out to be very challenging, especially because of: i) the interactions among various bank departments, including IT, FAM, FTP Centre and Mortgage Group; ii) the large technology stack involved. In all these cases we were in the lead.

Expert Meetings Residential Mortgage Prepayment Model

We helped a Dutch bank establish a vision on the prepayments level(s) of their residential mortgage portfolio by hosting a series of expert meetings. The goal of the sessions was to establish the prepayment level the bank expects in the near future, that is within a horizon of 10 years. To this end the total prepayment level is split in 3 subparts: refinance, curtailments

and relocation. We prepared a list of key factors driving these subparts, which were refined during the first session. For the consecutive session we provide more data and give insights into the impact of the choices of the experts. The final output of the work was a new set of model parameters to use for the banks prepayment model along with the impact on the balance sheet of the update in parameters.

Automation of FTP process

For a large Dutch bank we created a detailed plan plus working prototype to replace the manual daily funds transfer prices (FTP) for the mortgages domain process with an automate system.

The resulting prototype achieved automation using the windows scheduler, Visual Basic scripts and Excel (with macro code). This prototype was the first step to automate the process and greatly reduced the workload for the business team charged with producing the daily prices. From an IT perspective this first round of automation was not enough as the prototype model didn’t fulfill the straight through processing required by IT as it still requires manual steps. In the second phase of the project we investigated which steps can be taken to achieve straight through processing. We suggested to migrate the solution to a more technically robust environment using C#, SISS package and SQL-server.

Pipeline Option Residential Mortgages

We were asked by a large Dutch bank to help model the pipeline option for their residential mortgage portfolio. We developed a model and implemented it in the bank's ALM software.

The pipeline of the mortgage portfolio consists of those mortgages that have been offered to clients, but not yet settled. Two groups of contracts exist: those with the option to pay the lower of the offer rate and the actual mortgage rate at the day of property transfer and those without. In both cases the client doesn’t need to wait till the end of the offer period before drawing funds. The timing of the funding needs of the client is considered to be independent of the option value. Other factors like delivery dates of the property and personal preferences are considered to dominate over economic considerations in this case. The choice was made to use historical client behavioral data to compute the probability of drawing funds at a certain period. This choice was made over considering new clients homo economicus and using option theory to calculate the economic value of this phenomenon.

Another factor we considered is the fact that the customer has the option not to accept the mortgage offer from the bank and seek funding elsewhere (i.e., not all offers convert to actual sales). We call this the acceptance option. Based on historical data the fraction of those offers that convert to sales are estimated for each period.

Mortgages Integration Project at ABN AMRO

We have been working on an integration project at ABN AMRO and their subsidiary ABN AMRO Hypotheken Groep. The project requires the migration of mortgages from an obsolete system to a Quantitative Risk Management (QRM) system.

To manage the project effectively we compiled a scenario for a successful migration, based on information gathered throughout the organization. As part of this process, we studied both the current process of funding the mortgage contracts and the process they to which they are migrating. This presented an opportunity to immerse ourselves the funding practices of banks and link these to implementation, i.e. proper engineering.

The migration requires a move from an existing funding methodology to the industry standard of Funds Transfer Pricing [1. see for more details on FTP] (FTP). Implementing this needed a conversion methodology. After a literature search we suggested a methodology and implemented it. For the implementation we build a Microsoft Excel/VBA tool to do the relevant computations. Migration of the mortgage contract data from the old system required custom built SQL scripts in combination with the QRM Transaction Data Mart tool. Once the migration is complete all the mortgage information will be stored and processed in one place, reducing operation risk.