How we supported a Real Estate Private Equity firm in creating a fund credit line tracker

The Context

The client, a Real Estate Private Equity firm, wanted the TresVista Team to build a model to forecast drawdowns for the fund and the credit line that would allow overriding manually, particularly for historical repayments of the credit line and unaccounted investor drawdowns. The client wanted to track and measure the impact of new deals on the fund LTV to ensure that it did not cross the limit of 65%.

The Objective

To build a model to monitor fund expenses and determine the funding source for these expenses while maintaining certain fund covenants around cash, investor drawdowns, or the credit line with the bank.

The Approach

The TresVista Team followed the following process:

• Ensured that the model’s input template was the same as the client’s larger fund model from which the inputs were taken
• The team calculated the fund’s portfolio and debt valuation, which tracked the valuation of individual deals in the fund as well as the corresponding debt taken to execute these deals
• Post the calculation of valuations, the credit facility available to the client was calculated
• A funding schedule was built that, through inputs from all the above calculations, determined whether to fulfill the funding requirements using cash, credit line, or drawdowns from the investor account

The Challenges We Overcame

The major hurdles faced by the TresVista Team were:

• Resolving the circular references as the model contained several formulated interlinked functions
• Weekly periodicity of the credit tracker. While the calculations in the model were quarterly, the credit tracker needed weekly cash flow inputs

The team added a circular reference breaker and built two cash account schedules to tackle the issue generated by circular references. To deal with the periodic nature of the credit tracker, the team built a separate weekly cash flow on top of the quarterly cash flow that came from the client’s larger fund model. This new cash flow spread the quarterly amount equally across the weeks.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The TresVista Team created a dashboard containing charts that allowed the client to get a quick snapshot of the model and how levered they were. The dynamic chart showed the deal level expenses and the information about the source which was used to fund them. It also provided the functionality to view charts for different quarters and years. The combo charts depicted the facility available, investor drawdown, and fund LTV. Sources of funds were represented using a stacked chart, and a line chart was used to portray the LTV and headroom on LTV.