Financial Modeling Project: Investment Appraisal of a Tenant Property

This document presents a comprehensive financial modeling project focused on the investment appraisal of a tenant property located at 104 Lake Popular Street, White City, Israel. The project includes detailed calculations to assess the sensitivity of the Net Present Value (NPV) to various underlying variables. The analysis considers factors such as rent, expenses, and the discount rate. Sensitivity analysis is performed by changing variables like the discount rate and rent per square foot. Simulation of a stochastic variable is used to analyze the impact of uncertain annual cash inflows. The project also covers pricing strategies, optimization using Excel, and the application of the Capital Asset Pricing Model (CAPM) to determine the cost of capital. The document highlights the advantages of financial modeling in making informed investment decisions by assessing potential outcomes under different market conditions. It uses a variety of financial modeling techniques to provide a detailed analysis of the investment's potential.

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

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

2
FINANCIAL MODELLING
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:

3
FINANCIAL MODELLING
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.

4
FINANCIAL MODELLING
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)

5
FINANCIAL MODELLING
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

6
FINANCIAL MODELLING
,256.67
Total Property Present Value 5,1
21,025.24
Rounded to Thousands (use a
formula)
5,1
21,030.48
Less: Property purchase value in
2011 (last time the property sold)
3,9
50,000.00
Net present value (NPV) 1,17
1,030.48
Sensitivity in case the rate of return, i.e. appropriate rate of discount is 12% instead of
10%.
Prospective Present Value
Calculation of net present value (NPV)
Year Annual net Present value Present Value of

7
FINANCIAL MODELLING
cash inflow factor @12% pa Cash Flow @ 10% pa
1 $496,500 $0.893 443,
303.57
2 $521,325 $0.80 415,
597.10
3 $547,391 $0.71 389,
622.28
4 $574,761 $0.64 365,
270.89
5 $603,499 $0.57 342,
441.46
Total Cash Flow $2,743,476 1,956
,235.29
Property Resale @ Cap Rate $5,029,157.11
Less Adjustments $100,583
Net Resale Cash Flow $4,928,574 $0.567 2,796
,605.23

8
FINANCIAL MODELLING
Total Property Present Value 4,752
,840.52
Rounded to Thousands (use a
formula)
4,752
,845.76
Less: Property purchase value in
2011 (last time the property sold)
3,950
,000.00
Net present value (NPV) 802,8
45.76
Sensitivity:
NPV At 12% 802,845.76
NPV at 10% 1,171,030.48
Difference (368,184.71)
Sensitivity (%) (31.44)
Sensitivity in case the rate of return, i.e. appropriate rate of discount is 15% instead of
10%.
Year Annual net Present value Present Value of

9
FINANCIAL MODELLING
cash inflow factor @15%
pa
Cash Flow @
10% pa
1 $496,500 $0.870
431,739.13
2 $521,325 $0.756
394,196.60
3 $547,391 $0.658
359,918.63
4 $574,761 $0.572
328,621.36
5 $603,499 $0.497
300,045.59
Total Cash Flow $2,743,476 1
,814,521.31
Property Resale @ Cap Rate $5,029,157.11
Less Adjustments $100,583
Net Resale Cash Flow $4,928,574 $0.497 2
,450,372.31

10
FINANCIAL MODELLING
Total Property Present Value 4
,264,893.62
Rounded to Thousands (use a
formula)
4
,264,898.86
Less: Property purchase value in 2011
(last time the property sold)
3
,950,000.00
Net present value (NPV) 3
14,898.86
Particulars Amount ($)
NPV at 15% rate of
return
314,898.8
6
NPV at 10% rate of
return
1,171,030.
48
Difference (856,131.
61)
Sensitivity (%) (73.1

11
FINANCIAL MODELLING
1)
Sensitivity in case the rent is $30 per square feet per annum instead of $35 per square feet
per annum:
Prospective Present Value
Calculation of net present value (NPV)
Year Annual net
cash inflow
Present value
factor @10%
pa
Present Value of
Cash Flow @ 10%
pa
1 $423,000 $0.870 367,
826.09
2 $444,150 $0.79 351,
106.72
3 $466,358 $0.72 335,
147.32
4 $489,675 $0.65 319,