FR3202 Real Estate Finance and Funding
2021 Coursework
Group 29
Name

Student ID

Brandon Stanford Romo

170058203

Nicolas Munoz Khavov

180053249

Matteo Parente

180020483

Question 1
1 Assumptions

Debt Charge or debt service.
Since the bank will likely require a minimum DSCR of 1.5x, we can then calculate the maximum debt charge which includes interest and principal payments.
$975,000/1.5 = $650,000
We assumed a fully amortised 20year loan with a fixed interest rate. The amortisation allows risk to be redistributed among both parties and reduces the annual interest payments.

Annual Interest Rate.
The total annual interest depends typically on the sum of a reference rate plus a margin. Since a fixed interest rate is preferred, we will assume the current swap rates for a 20year tenor, as the loan has an amortisation of the same period plus a 3.75% margin.
As of Feb 22, the 20year GBP interest swap had a rate of 1.15% according to https://www.theice.com/marketdata/reports/180
This implies a total annual interest of 1.15% + 3.75% = 4.90%

Loan underwriting fees, transaction fees, and prepayment penalties are ignored for simplicity.

No corporate tax or income tax are assumed for this analysis.

Calculations of Equity free cash flow, IRR, and MoM Return
Firstly, we assume an LTV loan of 50%. With all our assumptions formulated, we ran a Debt Schedule (sheet 2) showing the following outputs and their functions:
Beginning of the year balance (last year’s ending balance), Interest payment (IPMT), Principal payment (PPMT), Total det charge (PMT), and the ending balance (Beginning balance – principal payment, or FV).
We obtained an annual total debt charge below $650,000 so we ran it again until we obtained the maximum debt charge, leading us to an LTV of 54.5%. Such LTV would bring the borrowed amount to $8,169,563, with 45.5% of equity financing equal to $6,830,437.
At the end of year five, the property will be sold for $17,957,062, and the proceedings will be used to pay the outstanding debt balance of $6,792,614. This can be seen in the ending balance of year 5 in the debt schedule table. The proceedings after paying the debt would be $11,682,617, which will be distributed to shareholders.
The payment of $650,000 would meet the required DCSR of 1.5x, calculated by dividing net income by debt repayments. The Interest Cover Rate would start at 2.44x in year one and increase to 3.56x in year five as the interest component of the debt service decreases while NOI increases gradually.
By calculating the IRR of the net cash flows for equity holders we obtain a rate of 15.17%, which is lower than the desired 17.5%. Therefore, we as borrowers would negotiate with the lender to either approve us a higher LTV or lower the loan’s interest rate.
The cashoncash or moneyonmoney multiple is the total dollar amount received by the end of the project divided by the total amount invested. After running the calculation, we obtain a multiple of 1.94x which is moderate for a 5year investment. Again, better terms should be negotiated.
The entry cap rate, which is NOI/Market Value, at the beginning of the investment is 6.5% and at the end of the investment it grows to 6.9%. This is realistic as Real Estate asset prices tend to increase in the longterm.
Additionally, the lender would be quite happy that the LTV decreases from 54.5% to 45.3% in just 5 years making the loan even safer to them.

Break Even Analysis
The BreakEven ratio is the occupancy level at which the effective gross income satisfies all operating expenses and debt service. To calculate this metric, we determine the project's annual operating expenses and debt service, sum them, and divide the total by potential gross operating income.
The percentages represent the breakeven point and give a strong indication of the property's cash flow potential. The BE point indicates how much a real estate investment property is susceptible to default on the debt, should the rental income decrease.
A BE ratio of 71% implies that we would cover all ongoing payments with a vacancy rate as high as 29%. Most lenders will accept a BE ratio of 85% or less, therefore the ratio of our project is a good indicator.
In a distress situation the vacancy rate rises to more than 29% and cashflows would not be sufficient to pay for all operating expenses, debt charges, and Capex in this order. While in deficit, there is a 5% Capex margin that can be distributed to debt holders as a safe cushion. Urgent action should be taken in order to improve the cashflows or to negotiate with stakeholders possible solutions.
In the case that cashflow is distressed even further, leaving the Capex rate at 0%, then the building will fall into default and if covenants are not met quickly or renegotiated with the lender, then the lender can claim possession on the asset.
However, this scenario is very unlikely as the cash flow projection expects a vacancy rate of 0% by year three and only an unforeseen event can cause the building to go into default.

Conclusion
As explained in section 2, the project would not achieve the desired IRR with the current assumptions. Therefore, we have computed an IRR Analysis Table (shown below) to show the different scenarios in which the IRR is equal or higher to the target rate (green) of 17.5%. Given the information generated, we would try to negotiate a minimum LTV of 60% with a maximum Interest margin of 2.25%, while anything beyond that would make the investment even more favourable.
Since the lender has lent us funds before, and we have proven to be a constant and punctual borrower, we believe that it is very likely that they would approve our requests. However, in a worstcase scenario, we could negotiate that the lender runs the building’s Capex account and that the amount received each year increases. This way, if we fail to raise the necessary cash to pay the debt obligations, the lender may run down the annual charges from the Capex reserve. It is more likely that the project’s rental income will generate enough cash to pay all debt obligations promptly and that shareholders will receive back the remainder of the Capex account once the building is sold.
Contributions:
Brandon: Created the Excel file and wrote the Assumptions section in the Word document. Helped Matteo in both the Calculations and the Breakeven section.
Matteo: Wrote the Calculations section and the breakeven analysis section in the Word document.
Nicolas: Helped create IRR analysis table and to conclude.
Page
Share with your friends: 