Financial Modelling

Question 1

• What is the investment value?

Answer 1

Investment value is the specific value of an asset or liability to a specific investor.

From a DCF perspective this is the Purchase price plus the NPV calculated using the investors DR. May differ from MV.

Question 2

What part of the Red Book is Investment values referred to?

Answer 2

From VPS 4. Basis of value

Question 3

• How do you calculate IRR and NPV?

Answer 3

There is no formula for IRR – It can be calculated a number of ways on excel, trial and error or linear interpolation.
NPV is the sum of all future cashflows discounted at the DR chosen by investors.

Question 4

• What is a sensitivity analysis?

Answer 4

Simple sensitivity analysis is a tool used to help clients identify the key inputs to our models and there impacts on the outputs.

Usually done using a macro to ensure no data tables are used.

Question 5

What is modelling best practice?

Answer 5

1. Disclaimer - Must be accepted using macro
2. Elegant and simple formula
3. Colour coding inputs and outputs
4. No hardcoding
5. Sensitivity analysis and error checks
6. One formula per row on cashflows

Question 6

What’s the use of financial models?

Answer 6

Creating a system to help with financial decisions i.e. buy, sell, hold

Mathematical model designed to represent a simplified version of a financial assets performance

Question 7

What’s an investment decision?

Answer 7

Investor happy with assumptions, check results align to expectations and IRR is greater than hurdle rate - analyze with sensitivity and scenario analysis.

Question 8

Any RICS information on Discounted Cashflows?

Answer 8

Guidance note : DCF for commercial property investment 2010

Question 9

What does the DCF for commercial property investment 2010 guidance note set out?

Answer 9

ARY used for prop valuation - Factor such as voids, rent reviews are implicit
DCF allows for cashflows to be implicit
Establish investment value or worth = PP + NPV
If >MV buy and vice versa
DCF best assessment of Investment value

Question 10

What are the sections of the DCF 2010 Guidance note?

Answer 10

Intro, Fundamentals, estimating cashflow, estimating exit value, discounting process and selecting DR.

Question 11

What is a discount rate?

Answer 11

Rate of return that adequately compensates an investor for risk taken - often benchmark (GILTS, Risk Free) + Premium

Question 12

How to calculate Discount rate?

Answer 12

Risk free + risk premium

Risk free = GILTS or WACC
Risk premium =
Market Risks (illiquidity, failure to meet forecasts, risk of structural change, legislative change - planning)
+
Specific risk (Tenant default, failure to re-let, different lease structure)

Question 13

What are some alternative approaches to discount rate?

Answer 13

Discount rate for different sectors

Discount rate for different part of the cashflow is suggested by RICS guidance.

Question 14

• What is an error check?

Answer 14

Ensuring that the cashflows align to the output sheets for example – ensure no user errors

Question 15

• What’s in a disclaimer?

Answer 15

CBRE accept no responsibility or liability towards any third party in respect of The Model or the information contained herein.

Question 16

• How would you get round user errors?

Answer 16

Error checks and also locking the model – having an editor view or an input only view.

Question 17

What were some of the key metrics in the fund model?

Answer 17

Total return made up of capital return and income return
Portfolio Value
WAULT to expiry and break
Cash on Cash
IRR

Question 18

• What were the yield growths in the fund model?

Answer 18

This depended on the sector of each individual asset – and the specific asset classes within those sectors
e.g. shopping centres was growing from 9%, WE offices was compressing from 4%

Question 19

• Why was rental growth used in the fund model?

Answer 19

This depended on the sector of each individual asset – and the specific asset classes within those sectors - to incorporate inflation or asset headwinds

Question 20

• How did you evaluate the asset management initiatives in the fund model?

Answer 20

These were not included in the valuations and so had to be appraised in the model. The costs and ERVs were given by the respective asset managers and the exit values calculated on the ERVs at the respective sector yields. This allowed for them to be included in the cash flow

Question 20

• How did you evaluate the asset management initiatives in the fund model?

Answer 20

These were not included in the valuations and so had to be appraised in the model. The costs and ERVs were given by the respective asset managers and the exit values calculated on the ERVs at the respective sector yields. This allowed for them to be included in the cash flow

Question 21

• What did you compare the fund model output to?

Answer 21

MSCI All property index which tracks the performance of all sectors across the UK Real estate market.

Question 22

• How do you access the fund model file?

Answer 22

Through a disclaimer – where CBRE take no responsibility for the assumptions used and that no investment decision should be reliant upon this document.

Question 23

• How would the inputs vary between sectors in the development appraisal model?

Answer 23

Commercial – usually done by an ERV per square foot based on NIA with rent free and void periods including service charge and business rates
Residential is usually calculated on a price per unit or price per sq ft basis

Question 24

• Why would you lock the workings and output sheets?

Answer 24

So that user error is reduced – it means that the original functionality of the model remains and only the inputs can be amended.

Question 25

• What impact would a sell on hold have over a sell on practical completion?

Answer 25

Selling on hold would mean that the developer would benefit from the income stream generated from the property – however selling after PC would inevitably increase the rate of return due to the size of the exit being received earlier.

Question 26

• What output were agreed to show in the development appraisal model?

Answer 26

Developers Profit, Profit on Cost, Income Return, NPV

Question 27

How did you add the debt financing mechanism to the senior living model?

Answer 27

Create the unlevered cashflow. You would then understand the drawdown of debt dependent on the LTV or LTC. The interest rate selected would be charge on this drawdown. The inclusion of fees and the amortization schedule was made optional. Adding the sum of the drawdown (positive), interest, fees, amortisation, and repayment onto the unlevered cashflow would give you your levered cashflow

Question 28

• Why would the equity injected change?

Answer 28

Depending on the LTV achieved

Question 29

• Why would lenders request sales to be paid down?

Answer 29

Security on repayment, this is a type of amortisation structure – also ensures that it’s not spent elsewhere before they are reimbursed.

Question 30

• Why would the interest be rolled?

Answer 30

There is no operating income - usually development financing

Question 31

• How did you advise the development would still meet their return criteria in the senior living model?

Answer 31

It met their target IRR of greater than 30%

Question 32

• How could the lender utilize the sensitivity analysis in the senior living model?

Answer 32

They could change terms but also inputs into the cash flow – they may remove growth assumptions, rental values etc

Question 33

• What sensitivities would lenders look at?

Answer 33

Lender are conservative so would stress test downside scenarios to ensure that they would be repaid. i.e. no growth, sales prices aren’t achieved etc.