These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 16, 2014

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.