FR3202 Real Estate Finance & Funding Coursework 2024
Please work in groups of 4 only to answer BOTH Questions below by the hand in
date of 20th March 2024. You are encouraged to self-select within 2 weeks, otherwise
you can be allocated to a group. The coursework is based on previous exam style
questions and is designed to assess your quantitative and qualitative skills.
All calculations must be in Excel showing the formula or functions for each output. Do
not use hard coded calculations or Goal Seek or Solver, otherwise you will lose marks.
All written work including explanations and justification of your assumptions must be in
PDF format, which includes cross referencing to the calculation elements in Excel. The
written elements of the question should be concise but provide a full answer with
relevant focus.
You will be expected to apply theoretical concepts to practical outcomes, so ensure your
work is well referenced. Marks will be awarded for clarity, succinctness and presentation
of your solutions, supporting explanations and references to relevant texts, papers or
articles. Please use logical, well referenced solutions.
All work must be uploaded electronically to Moodle, both the excel file and PDF.
In your submission, state the name of each group member and their contribution to the
course work. Name your PDF / Excel file REFF2024_Group X, where X is your allocated
Group reference. Please note it is not necessary to label files to differentiate Excel and
PDF e.g., REFF2024_Group X Excel.
To avoid duplication and to ensure everyone has the same information, please raise any
questions about this coursework in the Coursework Forum, not via email, any question
raised via email will not be answered.
All coursework files will be downloaded at the Cut-off date and time. Late submissions
will be penalised as set out in the University’s coursework guidelines.
Answer BOTH Questions below:
Question 1
As an analyst working for Bunhill Investment Ltd, you are asked to assess the viability of
the purchase of The Geary Office Park, which is for sale with an asking price of
£32,150,000. You have been presented with the partially completed cashflow below:
a) Using a 4 year investment time horizon and assuming that income growth and
vacancy continue at the rates indicated and that Opex continues at the same
percentage rate as year 3, complete the currently blank before debt net cash flow
for year 4. For the capital value at sale, you may assume the asset is sold at a yield
of 5.75% based on the year 4 net operating income. Assuming initial market yields
for this type of property in this location are 6.50%, is this asset at purchase,
underpriced, overpriced or priced about right? (Ignore purchasers’ costs and sales
costs)
(10 Marks)
Page 1 of 3
b) Assuming you can obtain a bank loan from a senior lender, which has a full
amortisation period of 25 years, an all in interest rate of 5.10% (which includes the
credit margin), with a debt service cover ratio covenant in year 1 of 1.50x net
operating income and no arrangement fees. Use the before debt net cashflow line
for years 0 to 4 (which includes your answer from part a, for year 4) as your starting
point to:
• produce a 4 year after debt net cash flow from year 0 to the end of year 4.
• calculate the mortgage constant, an initial loan to value (LTV) in year 0 and
exit loan to value (LTV) at the end of year 4.
(25 Marks)
c) Using the information provided and your completed ungeared and geared
cashflows together with any further assumptions you deem necessary, calculate
the following metrics for this property and discuss the implications of each of your
solutions and who might be interested in each metric and why?
• Bank Exit Ratio
• Cash on Cash Return year 1
• Breakeven Ratio in year 1
(15 Marks)
Clearly set out all your calculations, avoid the use of Goal Seek or Solver and cite all
references you have relied on in producing your solution.
Question 2
A lender bank has agreed senior debt terms to a client at a loan to value ratio of 50% to
part finance a value-added real estate investment, which has some initial vacancy and is
expected to be fully let in year 4, at which time it will be sold.
The clients after senior debt net cash flow is shown below.
After Senior Debt only Year 0 Year 1 Year 2 Year 3 Year 4
Net Cash Flows -6,100,000 71,464 113,921 152,251 9,697,462
DSCR (Before
Capex) 1.55 1.67 1.79 1.95
You have been asked by the client to look at the potential to increase the debt facility
further, by including a lookback mezzanine structure. Current mezzanine providers for
this type of asset look for a minimum 10% IRR exclusive of fees, but the facility must be
cash flow positive out of operating cash flows, after applying the mezzanine finance
structure.
You are required to:
a) Propose terms you would recommend for the mezzanine piece sufficient to meet
the client’s minimum required rate of return of 14.50%.
(15 Marks)
Page 2 of 3
b) Show the client’s 4 year cash flow after adding in the mezzanine piece and also
show the mezzanine provider’s 4 year cashflow, ensuring you fully explain all
your inputs and calculations.
(30 Marks)
c) If the client now required a minimum geared return of 15% and the mezzanine
facility must remain cash flow positive out of operating cash flows, explain how
the senior and mezz lending structure might be modified to provide an equitable
outcome?
(5 Marks)
Clearly set out all your calculations, avoid the use of Goal Seek or Solver and cite all
references you have relied on in producing your solution.
Page 3 of 3
