Financial Modelling | Assignment

Running head: FINANCIAL MODELLING
Financial Modelling
Name of the Student:
Name of the University:
Authors Note:

FINANCIAL MODELLING
1
Contents
Problem:...........................................................................................................................................2
Sensitivity analysis:.........................................................................................................................4
Simulation of a stochastic variable:...............................................................................................13
Pricing:...........................................................................................................................................16
Optimizing using excel:.................................................................................................................17
CAPM Model illustration:.............................................................................................................26
References:....................................................................................................................................28

FINANCIAL MODELLING
2
Problem:
In this document a detailed calculations shall be performed to assess the sensitivity to the net
present value of a property to various underlying variables. An assumption of a tenant property
has been made to perform sensitivity and simulation analysis in this document. The underlying
variables are provided in the table below. These are standard inputs on the basis of which
sensitivity and simulation analysis shall be carried out (Srithongrung, 2017).
Using investment appraisal technique to appraise the investment proposal of investing in a tenant
property situated at 104 Lake Popular Street at White City in Israel is the solution to the problem
of investment decision. Net present value (NPV) of the above project by assuming certain
underlying variables in accordance with the market conditions shall be helpful in assessing the
desirability of investing in the tenant property in above address. It is important to understand that
market conditions continuously changes resulting in changes of underlying variables on which
the outcome of an investment proposal is dependent significantly. Sensitivity analysis to
calculate outcome of an investment proposal by changing the underlying variables will be
extremely helpful in assessing the sensitivity of such proposal. It is desirable from the point of
view of investors to invest in a project which has low sensitivity to the underlying variables and
market conditions as the risk in such investment projects are significantly lower as compared to
highly sensitive investment proposals.
The standard underlying conditions in relation to the tenant property situated in the above
mentioned address are accumulated in the table below. Using these underlying variables an in-
depth analysis of the investment proposal shall be conducted in this document.
Inputs:

FINANCIAL MODELLING
3
Name of the property: 104 Lake Popular Street, White City
Address of the property: 104 Lake Popular Street, White City
Type of property: Tenant property
Total Building Area (Square Ft.): 15,000
Cost of the property (investment) $3,950,000
Date of rent period starting: 01.01.2019
Number of years for the analysis 5
Rate of general inflation 5%
Loss of credit / collection 0%
Expenses reimbursed
Area Maintenance $1.00 per sq.ft./yr.
Property Tax $1,750 per yr.
Property Insurance $0.50 per sq.ft./yr.
Utilities $1.50 per sq.ft./yr.
Expense incurred on Administrative
purpose
$0.10 per sq.ft./yr.

FINANCIAL MODELLING
4
Expenses not to be reimbursed
Management fee 1% of Annual gross rent
Assumptions for Tenancy agreement
Rent $35.00 per sq.ft./yr.
Tenant Improvement Allowance $1.20 per sq.ft.
Term Length 5 years
Resale Assumptions:
Terminal Cap Rate 12%
Resale Adjustment (Commission) 2%
Valuation Assumptions:
Appropriate rate of discount 10.00%
Sensitivity analysis:
Firstly, the NPV with standard variables is calculated below:
Prospective Present Value
Calculation of net present value (NPV)

FINANCIAL MODELLING
5
Year Annual net
cash inflow
Present value
factor @10% pa
Present Value of
Cash Flow @ 10% pa
1 $496,500 $0.909 451,
363.64
2 $521,325 $0.826 430,
847.11
3 $547,391 $0.751 411,
263.15
4 $574,761 $0.683 392,
569.37
5 $603,499 $0.621 374,
725.31
Total Cash Flow $2,743,476 2,060
,768.57
Property Resale @ Cap Rate $5,029,157.11
Less Adjustments $100,583
Net Resale Cash Flow $4,928,574 $0.621 3,060