Using VBA to Model the Forecast of a Bond’s Cash Flows

Business description. Beneficial Financial Group is a life insurance company headquartered in Salt Lake City that is currently in “run-off”—the company no longer issues insurance policies, so it is gradually shrinking rather than growing.  As such, Beneficial is currently focusing its efforts on servicing the policies it has already issued, which mainly entails investment and actuarial management.  One of the reasons that Beneficial has gone into run-off is that mortgage-backed securities (MBS) had made up such a large proportion of its investment portfolio at the time the 2007-2008 financial crisis hit. After the almost-overnight drop in value of the MBS market in 2007, companies like Beneficial struggled to survive if they had been improperly assessing the risk of their MBS portfolio (as nearly the entire market had been). Since the crisis, Beneficial has been working to improve its procedures as much as possible to understand the true risk inherent in the mortgage-backed assets it holds.

Overview of problem and VBA solution. One of the procedures Beneficial is currently undertaking to assess the risk of its MBS portfolio is to dissect its mortgage-backed bonds into the expected cash flows of the underlying loans that back the security.  This is done in an effort to better understand the individual loans that account for the profitability of the bond, something that had been largely neglected prior to the crisis. The process of dissecting these bonds, however, can be somewhat tedious.  Each bond has loan data contained in a workbook unique to the bond. Beneficial keeps another workbook which contains a model for forecasting the cash flows of the loans underlying the bond. The firm keeps a third “master” workbook which opens the bond workbook, extracts the loan data, opens the model workbook, and exports the loan data into the model to forecast the cash flows. 

In order to relinquish the need to keep track of so many workbooks, analysts at Beneficial are interested in simplifying the process by maintaining one workbook for each bond/model combination.  They have sent me the workbook of a manufactured housing bond they hare holding, along with the model workbook and the master workbook.  The master workbook contains a macro that had been previously written by analysts to calculate the principal and interest (P&I) from an individual bond’s loan data, but no code had been written to convert the P&I into a forecast of cash flows classified by loan type (which they currently use the model workbook to create). I have written code which calls the macro that calculates the P&I, creates a cash flow forecast identical to the one created by the model workbook, and then pastes it into new sheets in the bond workbook.  The code I’ve written eliminates the need for the model and master workbooks altogether. Furthermore, I’ve created a tab in the ribbon with buttons to simplify the analysis process, along with an add-in that Beneficial can use to access the model from any workstation. 

• Project Write-up: http://files.gove.net/shares/files/14w/kunzler3/Final_Project_Write-Up.pdf
• Project Workbook: http://files.gove.net/shares/files/14w/kunzler3/393505NC2.xlsm
• Created Add-in: http://files.gove.net/shares/files/14w/kunzler3/393505NC2_with_model.xlam
• Model Spreadsheet Used by Beneficial: http://files.gove.net/shares/files/14w/kunzler3/GT_1996-5_M1_Model_1.xlsx
• Master Spreadsheet Used by Beneficial: http://files.gove.net/shares/files/14w/kunzler3/Master1.xlsm