Over 40,000 units – Residential Portfolio, UK
In approximately four months, we assisted a social business within the residential property sector with over 40,000 residential units to develop a financial model to investigate the financial standing of all their assets, from a unit basis to the whole portfolio. The company’s objective was to interrogate each asset’s cash flow and use this analysis as the financial grounding for any strategic asset management recommendation, for example, change of tenure or ownership type. The final model developed was able to execute this to our client’s satisfaction.
- We developed a robust multiple unit residential financial model in Excel which was transparent, simple and accessible and comprised of:
- Unlevered Cash Flows of multiple residential units located in London and some other parts of England
- Outright sales / Changes of Tenure / Changes of Ownership Type
- Scenario modelling
2. Created bespoke reporting that can be used to identify strategic asset management actions
Our financial model for this company:
- Was able to target the valuations based on target IRR, Gross or Net Yields (Cap Rates) and Vacant Possession automatically
- Enabled mixed tenures, for example, affordable rent to shared ownership, to London affordable rents, market rents and included commercial property units; all dynamically linked and beautifully set up
- Allowed for flexibility in the inputs, for example, different growth and inflation rates, capital expenditure plans and how units are transferred from one tenure to another
€100 Million – Residential Portfolio, Europe
An international outfit needed assistance with creating their underwriting investment financial model to assess the financial returns on a €100 million portfolio of residential units spread across Spain. Cambridge Finance’s challenge was to model all assets’ cash flows in a single spreadsheet, keeping it robust, flexible and allowing ease of use.
The model comprised of 5 sheets within a single workbook:
- General: the general assumptions and cost/sales curves
- Dashboard: project specific data such as name, location, number of units, floor areas
- Inputs: detailed inputs driving calculation of costs and sales and timing thereof
- IRR: consolidated portfolio level cash flows and calculation of returns
- P&L: development pro forma for each project – output of cost calculation per project, sub portfolio or the total portfolio
This financial model was used in the financial due diligence process of the portfolio acquisition. We created a bespoke real estate financial model which comprised of:
- Unlevered quarterly discounted cash flows of 20 periods (5 years) of around 50 property assets in Spain
- Straight line and S-curve cost curves modelled for the period
- Levered pre-tax cash flows using Senior Loan and Capex Facility
- Equity waterfall cash flows
- Levered and unlevered returns data including IRR and P&L
£1 Billion – Mixed Use Development, UK
A borough in London sought the assistance of Cambridge Finance to help them analyse the affordable housing proposals, specifically a mixed-use development made by developers and the Community Infrastructure Levy (CIL) new charging schedule.
Cambridge Finance advised, educated and recreated a bespoke financial model in excel on the mixed-use development while the new CIL charges were examined using the residual valuation method where cash flow appraisals had previously not been incorporated.
The borough was able to successfully assess the developer’s position and advise the Planning Committee about the impact of CIL charges on the viability of new developments.
UK Alternative Finance Lender
A UK alternative finance lender needed specialised support to create from scratch a new loan cash-flow model allowing them to provide terms and conditions to the borrower different from the terms and conditions negotiated with its funders, without losing track of the deal’s economics.
The client had the following requirements:
1. The model needed to be both flexible and robust to allow the underwriting of tickets worth > £20M
2. Provide results on-time, without delays.
We discussed with the members involved in the pricing, sales and management what features they would like to see modelled.
It was agreed that the model would have the following sections:
– Inputs tabs for both borrower’s loan and back-leverage: the end-user of the model can only change variables in these tabs;
– Loan and funding cash-flow tabs: the end-user cannot change any variable or formula in these tabs; no manual entry in these tabs;
– Analysis and Sources and Uses tabs: summary of the loan’s economics and risk.
1. The client is now able to price more complex deals tailored to the needs of the borrowers.
2. They have gained competitive advantage over its peers without compromising its ability to keep track of the loan’s economics and risk.