Driving a Financial Valuation from a Design

As seen in Loading a Design, a design’s object model (a knowledge graph including all its spatial and semantic relationships) can be loaded via Speckle from any of Speckle’s supported connectors, so long as those objects (Entitys and Relationships) have been assigned.

In this notebook, we will demonstrate how to integrate financial modelling into the object model (graph) in order to derive the financial valuation of a real estate property from its spatial design. This is done by calculating cash flows per Entity, and then using the Relationships to aggregate those Flows into appropriate Streams.

Load the Design

We will use the same design as in the previous notebook.

import json
import os

import locale
import urllib.parse
import json
import pandas as pd
import networkx as nx
from IPython.display import IFrame
import plotly.subplots
import plotly.offline as py

import rangekeeper as rk

speckle = rk.api.Speckle(
    host="speckle.xyz",
    token=os.getenv('SPECKLE_TOKEN'))
stream_id = "c0f66c35e3"
commit_id = speckle.get_latest_commit_id(stream_id)
IFrame("https://speckle.xyz/embed?stream={0}&commit={1}".format(stream_id, commit_id), width='100%', height=800)

 SpeckleClient( server: https://speckle.xyz, authenticated: True )

Inspect the Model Graph

model = speckle.get_commit(stream_id=stream_id)
parsed = rk.api.Speckle.parse(base=model['@property'])
property = rk.api.Speckle.to_rk(
    bases=list(parsed.values()),
    name='property',
    type='archetype')

Warning: Duplicate Entity 6496e279-88ca-49f4-b48c-6c0f156e0a58 [utilities] found.
Existing Entity is an Assembly while new Entity is not. Keeping Assembly.
Warning: Duplicate Entity 6496e279-88ca-49f4-b48c-6c0f156e0a58 [utilities] found.
Existing Entity is an Assembly while new Entity is not. Keeping Assembly.
Warning: Duplicate Entity 6496e279-88ca-49f4-b48c-6c0f156e0a58 [utilities] found.
Existing Entity is an Assembly while new Entity is not. Keeping Assembly.
Warning: Duplicate Entity 2b51847e-ec54-415d-afa0-ce32d74c6144 [plinthparking] found.
Existing Entity is an Assembly while new Entity is not. Keeping Assembly.

property.plot(name=f'assets/{property.name}')
IFrame(src=f'./{property.name}.html', width='100%', height=800)

assets/property.html

Assigning and Aggregating Flows per Entity

Now we can organise the two main categories of cash flows (costs and revenues) according to the Relationship types in the design.

In this example, revenue-based cash flows are generated by Entitys that have a measureable floor area – in this case, anything that has a ‘gfa’ property.

We can query the graph to find them:

Revenue-producing Entities

Revenue-producing Entitys will be aggregated by their spatial containment. So we can first slice our graph to a tree of nodes that have “contains” relationships:

spatial_containment = property.filter_by_type(
    relationship_type='spatiallyContains',
    name='spatial_containment')

Note: we anticipate that the spatial decomposition is completely hierarchical (i.e., no spaces overlap, which means no spaces have multiple parents). We can check this by testing whether the containment graph is a “tree” (or “arborescence”: see NetworkX Tree).

nx.is_arborescence(spatial_containment.graph)

True

# Plot the spatial containment graph
spatial_containment.plot(name=f'assets/{spatial_containment.name}')
IFrame(src=f'./{spatial_containment.name}.html', width='100%', height=800)

assets/property by spatiallyContains and None.html

Simple Aggregation

Now that we have verified this, we can continue with a simple aggregation. We wish to aggregate up through our hierarchy all Entitys that have a Gross Floor Area (‘gfa’) property. Rangekeeper provides defaults for the Entity.aggregate() method for this, whereby a named property of any Entity is summed (numerical addition) into its parent Entity against a specified label:

property.aggregate(
    property='gfa',
    label='subtotal_gfa',
    relationship_type='spatiallyContains')

We can tabulate this by converting the graph to a pandas DataFrame. Note that the graph nodes (as witnessed in the DataFrame’s index and ‘parent’ column) are GUID strings. Each of these have corresponding Entity objects, organised via NetworkX’s Node Attributes. See NetworkX Tutorial

df = spatial_containment.to_DataFrame()
df = df[['name', 'type', 'gfa', 'subtotal_gfa', 'parent', 'use', 'ffl', 'number']]
df

	name	type	gfa	subtotal_gfa	parent	use	ffl	number
afe8e730-ad0f-4c2a-a15c-e249e4518fa8	property	property	NaN	42786.259115	None	NaN	NaN	NaN
0a0a97ef-431d-471d-bc33-ace7e7df5fcb	plinth	building	NaN	12773.744211	afe8e730-ad0f-4c2a-a15c-e249e4518fa8	NaN	NaN	NaN
5ebb9736-5f18-4103-a5a4-040cd2e9b019	buildingA	building	NaN	16575.630074	afe8e730-ad0f-4c2a-a15c-e249e4518fa8	NaN	NaN	NaN
a1e00481-e51e-47c5-b629-8b214c0900bd	buildingB	building	NaN	13436.884829	afe8e730-ad0f-4c2a-a15c-e249e4518fa8	NaN	NaN	NaN
6496e279-88ca-49f4-b48c-6c0f156e0a58	utilities	utilities	NaN	0.000000	afe8e730-ad0f-4c2a-a15c-e249e4518fa8	NaN	NaN	NaN
2b51847e-ec54-415d-afa0-ce32d74c6144	plinthparking	space	NaN	12773.744211	0a0a97ef-431d-471d-bc33-ace7e7df5fcb	parking	NaN	NaN
938f4cf1-d307-4160-9538-d4bfa9583ad8	plinthplant	utilities	NaN	0.000000	0a0a97ef-431d-471d-bc33-ace7e7df5fcb	plant	NaN	NaN
e84b8c35-fd4a-49fc-b4c9-e318a958685b	plinthparkingFloor-2	floor	6386.872106	6386.872106	2b51847e-ec54-415d-afa0-ce32d74c6144	NaN	-5.9	-2.0
c3ebe151-1e0c-45c6-8852-7ea21f72290e	plinthparkingFloor-1	floor	6386.872106	6386.872106	2b51847e-ec54-415d-afa0-ce32d74c6144	NaN	-2.8	-1.0
c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	buildingAhotel	space	NaN	11716.690054	5ebb9736-5f18-4103-a5a4-040cd2e9b019	hotel	NaN	NaN
1d0841bf-aa28-41d7-ba75-0ad0f3d57d21	buildingAretail	space	NaN	789.114895	5ebb9736-5f18-4103-a5a4-040cd2e9b019	retail	NaN	NaN
7716632b-f43c-4ed2-9f84-26709361bbfe	buildingAresidential	space	NaN	2308.322369	5ebb9736-5f18-4103-a5a4-040cd2e9b019	residential	NaN	NaN
01aec121-ef19-4e6d-9f09-b2ce007b717d	buildingAretail	space	NaN	722.715820	5ebb9736-5f18-4103-a5a4-040cd2e9b019	retail	NaN	NaN
6f6b227b-b28b-4412-89bd-0a9bbc47164e	buildingAparking	space	NaN	1038.786936	5ebb9736-5f18-4103-a5a4-040cd2e9b019	parking	NaN	NaN
7eec5208-fc0e-4dfd-bd0b-b1d226b2e97e	buildingAcores	utilities	NaN	0.000000	5ebb9736-5f18-4103-a5a4-040cd2e9b019	cores	NaN	NaN
bc265fcf-d0f6-4d22-8e92-d8f86f4d707a	buildingAhotelFloor0	floor	111.690000	111.690000	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	0.3	0.0
22851887-f9ee-4aa8-8755-bc021c3a3b30	buildingAhotelFloor1	floor	1697.451572	1697.451572	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	5.3	1.0
d67cc046-9c03-4e43-b1b5-876417dbcdda	buildingAhotelFloor2	floor	1270.128420	1270.128420	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	8.4	2.0
5f90565d-44c0-4de2-b510-d263599953f5	buildingAhotelFloor3	floor	1068.208325	1068.208325	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	11.5	3.0
f7b76e8b-93f0-47c6-8666-05c541c19e25	buildingAhotelFloor4	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	14.6	4.0
9bdec185-a51b-4845-b746-ddcd3e3fa433	buildingAhotelFloor5	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	17.7	5.0
250edc28-32de-4633-add0-a384305b5e87	buildingAhotelFloor6	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	20.8	6.0
e4f25e01-0e9d-4fd2-b705-96d118802468	buildingAhotelFloor7	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	23.9	7.0
9582fd3c-ce66-42c7-8675-39b04b7a4689	buildingAhotelFloor8	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	27.0	8.0
9151983a-1344-4c88-81f3-76cd4ca8ec6d	buildingAhotelFloor9	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	30.1	9.0
56eec388-d1bb-4de7-92a8-be8202d5be2d	buildingAhotelFloor10	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	33.2	10.0
49d884ea-f590-46a3-a662-db5e73190848	buildingAhotelFloor11	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	36.3	11.0
192fb1b2-fc6c-4b42-8135-45cbcbead777	buildingAhotelFloor12	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	39.4	12.0
85305abb-0e6f-4c8f-bf27-553292e317e9	buildingAhotelFloor13	floor	756.921174	756.921174	c0348c0f-a31f-421b-b6bc-0fe0d5ea947f	NaN	42.5	13.0
75c85658-55c8-4bd0-9d0c-c03a9b2c6169	buildingAretailFloor0	floor	789.114895	789.114895	1d0841bf-aa28-41d7-ba75-0ad0f3d57d21	NaN	0.3	0.0
bf5a4444-6abd-4b89-b7a8-d758df5134fe	buildingAresidentialFloor0	floor	57.900000	57.900000	7716632b-f43c-4ed2-9f84-26709361bbfe	NaN	0.3	0.0
dbf25b07-6f85-45bb-905c-de5b8562f5e7	buildingAresidentialFloor1	floor	750.140790	750.140790	7716632b-f43c-4ed2-9f84-26709361bbfe	NaN	5.3	1.0
507e4ef2-fe13-44d7-af5f-bcf717b3de53	buildingAresidentialFloor2	floor	750.140790	750.140790	7716632b-f43c-4ed2-9f84-26709361bbfe	NaN	8.4	2.0
bb815059-7516-45df-8fac-16f0e62242f3	buildingAresidentialFloor3	floor	750.140790	750.140790	7716632b-f43c-4ed2-9f84-26709361bbfe	NaN	11.5	3.0
d45316af-5763-4b39-8e39-dc7645c39ec2	buildingAretailFloor0	floor	722.715820	722.715820	01aec121-ef19-4e6d-9f09-b2ce007b717d	NaN	0.3	0.0
99dae6a3-d102-4525-9f3a-b8ee50a9c45f	buildingAparkingFloor0	floor	1038.786936	1038.786936	6f6b227b-b28b-4412-89bd-0a9bbc47164e	NaN	0.3	0.0
e9acc93a-6f25-4179-97b3-594d1927565a	buildingBresidential	space	NaN	11442.736624	a1e00481-e51e-47c5-b629-8b214c0900bd	residential	NaN	NaN
eb1a8e6c-abf7-4fde-be66-b79a53eb7d52	buildingBparking	space	NaN	1178.164752	a1e00481-e51e-47c5-b629-8b214c0900bd	parking	NaN	NaN
34e63754-df1f-4c4d-b480-afa4109b3a5a	buildingBretail	space	NaN	815.983454	a1e00481-e51e-47c5-b629-8b214c0900bd	retail	NaN	NaN
cb5a02f7-b1b9-4a32-95b8-bbb70a946230	buildingBcores	utilities	NaN	0.000000	a1e00481-e51e-47c5-b629-8b214c0900bd	cores	NaN	NaN
0c018f4e-85d1-45ca-a83a-e3047e63ea6e	buildingBresidentialFloor0	floor	765.941631	765.941631	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	0.3	0.0
5ffdc26b-ed78-45e7-95a7-93114f38437b	buildingBresidentialFloor1	floor	2361.056035	2361.056035	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	5.3	1.0
15c0f8fb-5a62-4f60-94fc-5f1ea1a79f76	buildingBresidentialFloor2	floor	2361.056035	2361.056035	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	8.4	2.0
aece3428-b220-4ced-a819-15cdb72166aa	buildingBresidentialFloor3	floor	2015.956035	2015.956035	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	11.5	3.0
1b966f89-f491-425e-9da1-ad0ad124cae4	buildingBresidentialFloor4	floor	2015.956035	2015.956035	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	14.6	4.0
f3ff41c9-c6e6-4bcb-8d36-c7acfbecf839	buildingBresidentialFloor5	floor	875.014900	875.014900	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	17.7	5.0
1df6c7b8-7e0a-4347-b94c-82324a5c4765	buildingBresidentialFloor6	floor	875.014900	875.014900	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	20.8	6.0
7f876f6b-5eef-44d0-b9be-39b08176e2e4	buildingBresidentialFloor0	floor	172.741054	172.741054	e9acc93a-6f25-4179-97b3-594d1927565a	NaN	0.3	0.0
3623f3aa-9226-46d1-9e2f-b3e82e0f187f	buildingBparkingFloor0	floor	1178.164752	1178.164752	eb1a8e6c-abf7-4fde-be66-b79a53eb7d52	NaN	0.3	0.0
bb756739-0f7c-43ec-874a-ec0def2ca9e4	buildingBretailFloor0	floor	815.983454	815.983454	34e63754-df1f-4c4d-b480-afa4109b3a5a	NaN	0.3	0.0

We can also plot this as a hierarchical pie chart (a.k.a. ‘sunburst’ chart), or a treemap:

sunburst = spatial_containment.sunburst(property='subtotal_gfa')
treemap = spatial_containment.treemap(property='subtotal_gfa')
fig = plotly.subplots.make_subplots(
    rows=2, cols=1,
    specs=[[{"type": "sunburst"}], [{"type": "treemap"}]],
    subplot_titles=('Gross Floor Area - Sunburst Plot', 'Gross Floor Area - Treemap Plot'))
fig.append_trace(sunburst, row=1, col=1)
fig.append_trace(treemap, row=2, col=1)
filename='spatial_containment_chart.html'

py.plot(fig, filename=f'assets/{filename}', auto_open=False)
IFrame(src=f'./{filename}', width='100%', height=800 * len(fig.data))

Aggregating a (Financial) Calculation

A more complex aggregation involves collecting specific cash Flows into Streams per Entity/Assembly; for instance, we may want to aggregate all revenues generated by lettable floorspace into their respective parent Entitys, in order to analyse or compare the performance of the design at different resolutions.

We start by identifying which Entitys are ‘floor’ types and have an area measurement:

floors = property.filter_by_type(entity_type='floor')
floors = [floor for floor in floors.get_entities().values() if hasattr(floor, 'gfa')]

For each floor we can project a set of simple cash flows, in a similar manner to A Basic DCF Valuation.

First initialize our locale, and a set of parameters, including some around area efficiency ratios, initial incomes per area, and growth rates:

locale.setlocale(locale.LC_ALL, 'en_au')
units = rk.measure.Index.registry
currency = rk.measure.register_currency(registry=units)
frequency = rk.duration.Type.YEAR

efficiency_ratios = dict(
    hotel=0.8,
    retail=0.675,
    residential=0.75,
    parking=0.9
    )
initial_income_per_area_pa = dict(
    hotel=750,
    retail=1000,
    residential=600,
    parking=0
    )
pgi_growth_rates = dict(
    hotel=.035,
    retail=.075,
    residential=.055,
    parking=0
    )
vacancy_rates = dict(
    hotel=.025,
    retail=.075,
    residential=.015,
    parking=0,
    )

revenue_inputs = pd.DataFrame(
    data=dict(
        efficiency_ratio=efficiency_ratios,
        initial_income_per_area_pa=initial_income_per_area_pa,
        pgi_growth_rate=pgi_growth_rates,
        vacancy_rate=vacancy_rates,
        ))
revenue_inputs

	efficiency_ratio	initial_income_per_area_pa	pgi_growth_rate	vacancy_rate
hotel	0.800	750	0.035	0.025
retail	0.675	1000	0.075	0.075
residential	0.750	600	0.055	0.015
parking	0.900	0	0.000	0.000

params = dict(
    start_date=pd.Timestamp('2001-01-01'),
    num_periods=10,
    frequency=rk.duration.Type.YEAR
    )

Streams of Revenue Flows per Entity:

We can now construct a set of cash flows per Entity in our design for our asset. We will use a similar structure to the previous notebooks.

First we will define a general ‘Spans’ class that defines the time periods. This can be used by any subsequent calculations:

class Spans:
    def __init__(self, params: dict):
        self.params = params
        self.calc_span = rk.span.Span.from_duration(
            name='Span to Calculate Reversion',
            date=self.params['start_date'],
            duration=self.params['frequency'],
            amount=self.params['num_periods'] + 1)
        self.acq_span = rk.span.Span.from_duration(
            name='Acquisition Span',
            date=rk.duration.offset(
                self.params['start_date'],
                amount=-1,
                duration=self.params['frequency']),
            duration=self.params['frequency'])
        self.span = self.calc_span.shift(
            name='Span',
            amount=-1,
            duration=self.params['frequency'])

Then, we will define a Revenues class that holds the parameters:

class Revenues:
    def __init__(
            self,
            params: dict,
            spans: Spans):
        self.params = params
        self.spans = spans

And then we add Revenue Flows:

@rk.update_class(Revenues)
class Revenues:
    def generate(self):
        self.pgi = rk.flux.Flow.from_projection(
            name='Potential Gross Income',
            value=self.params['initial_income'],
            proj=rk.projection.Extrapolation(
            form=rk.extrapolation.Compounding(
                rate=self.params['pgi_growth_rate']),
            sequence=self.spans.calc_span.to_sequence(frequency=self.params['frequency'])),
            units=currency.units)
        self.vacancy = rk.flux.Flow(
            name='Vacancy Allowance',
            movements=self.pgi.movements * -self.params['vacancy_rate'],
            units=currency.units)
        self.egi = rk.flux.Stream(
            name='Effective Gross Income',
            flows=[self.pgi, self.vacancy],
            frequency=self.params['frequency'])

Now we need to add Entity-specific parameters to the parameters dictionary, and run the init_spans() and init_flows() methods per floor Entity:

(Note we place the resultant Stream in the events attribute of the Entity)

spans = Spans(params)

for floor in floors:
    parent = floor.get_relatives(
        relationship_type='spatiallyContains',
        outgoing=False,
        assembly=spatial_containment)[0]
    floor['use'] = parent['use']

    floor.params = params.copy()
    floor.params['initial_income'] = initial_income_per_area_pa[floor['use']] * floor['gfa'] * efficiency_ratios[floor['use']]
    floor.params['pgi_growth_rate'] = pgi_growth_rates[floor['use']]
    floor.params['vacancy_rate'] = vacancy_rates[floor['use']]

    revenues = Revenues(floor.params, spans)
    revenues.generate()
    floor['pgi'] = revenues.pgi
    floor['vacancy'] = revenues.vacancy
    floor['egi'] = revenues.egi

Aggregating by Parent Container

Since we have the “containment” hierarchy, we can aggregate the Revenues per parent Entity:

Rangekeeper provides some helper methods to assist in the aggregation of Streams and Flows; specifically, since the Entity.aggregate() method updates a specified Entity’s property with a dictionary of subentity properties, keyed by their respective entityIds, we can use the aggregate_flows() function to support the collation of child Entity Flows into a single Stream in a new property label in the Entity

def aggregate_egis(**kwargs):
    return rk.graph.Entity.aggregate_flows(
        **kwargs,
        name='Effective Gross Income',
        frequency=frequency)

This function is passed into the Entity.aggregate() method as the function parameter:

property.aggregate(
    function=aggregate_egis,
    property='egi',
    label='subtotal_egi',
    relationship_type='spatiallyContains')

Now we can look at what the aggregate method has produced for a single floor:

floors[1]['egi'].name

'Effective Gross Income'

We can then inspect the aggregate_egi property of an Entity, which holds a Stream of Flows (each coming from a child of that Entity). Let’s use the hotel compartment in Building A:

buildingAhotel = [e for (id, e) in spatial_containment.get_entities().items() if e.name == 'buildingAhotel'][0]
print(buildingAhotel.name)
buildingAhotel['subtotal_egi']

buildingAhotel

date	Effective Gross Income for bc265fcf-d0f6-4d22-8e92-d8f86f4d707a [buildingAhotelFloor0]	Effective Gross Income for 22851887-f9ee-4aa8-8755-bc021c3a3b30 [buildingAhotelFloor1]	Effective Gross Income for d67cc046-9c03-4e43-b1b5-876417dbcdda [buildingAhotelFloor2]	Effective Gross Income for 5f90565d-44c0-4de2-b510-d263599953f5 [buildingAhotelFloor3]	Effective Gross Income for f7b76e8b-93f0-47c6-8666-05c541c19e25 [buildingAhotelFloor4]	Effective Gross Income for 9bdec185-a51b-4845-b746-ddcd3e3fa433 [buildingAhotelFloor5]	Effective Gross Income for 250edc28-32de-4633-add0-a384305b5e87 [buildingAhotelFloor6]	Effective Gross Income for e4f25e01-0e9d-4fd2-b705-96d118802468 [buildingAhotelFloor7]	Effective Gross Income for 9582fd3c-ce66-42c7-8675-39b04b7a4689 [buildingAhotelFloor8]	Effective Gross Income for 9151983a-1344-4c88-81f3-76cd4ca8ec6d [buildingAhotelFloor9]	Effective Gross Income for 56eec388-d1bb-4de7-92a8-be8202d5be2d [buildingAhotelFloor10]	Effective Gross Income for 49d884ea-f590-46a3-a662-db5e73190848 [buildingAhotelFloor11]	Effective Gross Income for 192fb1b2-fc6c-4b42-8135-45cbcbead777 [buildingAhotelFloor12]	Effective Gross Income for 85305abb-0e6f-4c8f-bf27-553292e317e9 [buildingAhotelFloor13]
2001	$65,338.65	$993,009.17	$743,025.13	$624,901.87	$442,798.89	$442,798.89	$442,798.89	$442,798.89	$442,798.89	$442,798.89	$442,798.89	$442,798.89	$442,798.89	$442,798.89
2002	$67,625.50	$1,027,764.49	$769,031.00	$646,773.44	$458,296.85	$458,296.85	$458,296.85	$458,296.85	$458,296.85	$458,296.85	$458,296.85	$458,296.85	$458,296.85	$458,296.85
2003	$69,992.40	$1,063,736.25	$795,947.09	$669,410.51	$474,337.24	$474,337.24	$474,337.24	$474,337.24	$474,337.24	$474,337.24	$474,337.24	$474,337.24	$474,337.24	$474,337.24
2004	$72,442.13	$1,100,967.02	$823,805.24	$692,839.87	$490,939.04	$490,939.04	$490,939.04	$490,939.04	$490,939.04	$490,939.04	$490,939.04	$490,939.04	$490,939.04	$490,939.04
2005	$74,977.60	$1,139,500.86	$852,638.42	$717,089.27	$508,121.91	$508,121.91	$508,121.91	$508,121.91	$508,121.91	$508,121.91	$508,121.91	$508,121.91	$508,121.91	$508,121.91
2006	$77,601.82	$1,179,383.39	$882,480.77	$742,187.39	$525,906.17	$525,906.17	$525,906.17	$525,906.17	$525,906.17	$525,906.17	$525,906.17	$525,906.17	$525,906.17	$525,906.17
2007	$80,317.88	$1,220,661.81	$913,367.59	$768,163.95	$544,312.89	$544,312.89	$544,312.89	$544,312.89	$544,312.89	$544,312.89	$544,312.89	$544,312.89	$544,312.89	$544,312.89
2008	$83,129.01	$1,263,384.97	$945,335.46	$795,049.69	$563,363.84	$563,363.84	$563,363.84	$563,363.84	$563,363.84	$563,363.84	$563,363.84	$563,363.84	$563,363.84	$563,363.84
2009	$86,038.52	$1,307,603.45	$978,422.20	$822,876.43	$583,081.58	$583,081.58	$583,081.58	$583,081.58	$583,081.58	$583,081.58	$583,081.58	$583,081.58	$583,081.58	$583,081.58
2010	$89,049.87	$1,353,369.57	$1,012,666.98	$851,677.11	$603,489.43	$603,489.43	$603,489.43	$603,489.43	$603,489.43	$603,489.43	$603,489.43	$603,489.43	$603,489.43	$603,489.43
2011	$92,166.62	$1,400,737.50	$1,048,110.32	$881,485.80	$624,611.56	$624,611.56	$624,611.56	$624,611.56	$624,611.56	$624,611.56	$624,611.56	$624,611.56	$624,611.56	$624,611.56

We can also easily sum and collapse that aggregation, as it is a Stream`:

buildingAhotel['subtotal_egi'].sum()

date	Effective Gross Income for c0348c0f-a31f-421b-b6bc-0fe0d5ea947f [buildingAhotel] Aggregation (sum)
2001-12-31 00:00:00	$6,854,263.68
2002-12-31 00:00:00	$7,094,162.91
2003-12-31 00:00:00	$7,342,458.61
2004-12-31 00:00:00	$7,599,444.66
2005-12-31 00:00:00	$7,865,425.23
2006-12-31 00:00:00	$8,140,715.11
2007-12-31 00:00:00	$8,425,640.14
2008-12-31 00:00:00	$8,720,537.54
2009-12-31 00:00:00	$9,025,756.36
2010-12-31 00:00:00	$9,341,657.83
2011-12-31 00:00:00	$9,668,615.85

buildingAhotel['subtotal_egi'].sum().collapse()

date	Effective Gross Income for c0348c0f-a31f-421b-b6bc-0fe0d5ea947f [buildingAhotel] Aggregation (sum)
2011-12-31 00:00:00	$90,078,677.93

The above subtotals the Effective Gross Income (EGI) for the hotel space of Building A over the hold period of the asset.

We can then do this for all Entitys in the graph, and plot the results:

sunburst = spatial_containment.sunburst(property='subtotal_egi')
treemap = spatial_containment.treemap(property='subtotal_egi')
fig = plotly.subplots.make_subplots(
    rows=2, cols=1,
    specs=[[{"type": "sunburst"}], [{"type": "treemap"}]],
    subplot_titles=('Effective Gross Income - Sunburst Plot', 'Effective Gross Income - Treemap Plot'))
fig.append_trace(sunburst, row=1, col=1)
fig.append_trace(treemap, row=2, col=1)
filename='aggregate_egis_chart.html'

py.plot(fig, filename=f'assets/{filename}', auto_open=False)
IFrame(src=f'./{filename}', width='100%', height=800 * len(fig.data))

Cost-generating Entities

Let’s now do the same for the cost-generating Entitys. While it is routine to register revenue-generating Flows with Entitys that have lettable or sellable floor space, the convention to also register all cost-generating Flows with only those same Entitys is possibly only done out of convenience rather than accuracy.

For example, both operating and capital expenses may be attributed to building systems (e.g. maintenance, upgrading, & replacement of MEP or facade componentry) that are not coincident with the floor space they are servicing. I.e., the convention to average (sub)total costs across (sub)total floor space may be misleading, especially when attempting to compare different scenarios at different resolutions (See [dNG18], 5.4 Flaw of Averages).

By virtue of Rangekeeper’s multi-faceted approach to its object model, costs can be attributed and aggregated to Entitys that are not spatially coincident with the revenue-generating Entitys; in the example design, we will use the ‘services’ relationship-type to register and aggregate some of these, while space-specific costs will be registered and aggregated via spatial containment:

Operational and Capital Expenses of Building Utilities & Systems

Of course, in a simplified model and without knowing design and engineering details for the property, costs for operational and capital expenses will be calculated from some proportional rate of spatial characteristics of the building. In this example, we will use the following as simplifications of how these types of costs may be calculated. Note that this is more a demonstration of methodology, and in practice would adjust to real-world data about the specific building systems and their costs.

• ‘Floorplate-derived’ Operational and Capital Expenses will be calculated as a function of the subtotal floorplate area of ‘building’ Entitys. One could think of this as representing the cost of operating the floor’s MEP, or maintaining, repairing and upgrading elements on the floors themselves (e.g. FF&E)

• ‘Facade-derived’ Operational Expenses will be calculated as a function of the surface area of ‘building’ Entity. This would represent the cost of facade maintenance, repair or replacement.

• ‘Utility-derived’ Capital Expenses will be calculated as a function of the volume of ‘utility’ Entitys. This represents the cost of repairing and upgrading the building’s MEP and transportation systems.

Cost Flows per Entity

There are some properties of Entitys that we need to produce before we can calculate Flows off them. In this case we need to produce the facade area for any Entitys that have ‘perimeter’ and ‘ftf’ (Floor-to-Floor height) properties:

# Aggregate Facade Areas:
for (entityId, entity) in property.get_entities().items():
    if hasattr(entity, 'perimeter') & hasattr(entity, 'ftf'):
        entity['facade_area'] = entity['perimeter'] * entity['ftf']
spatial_containment.aggregate(
    property='facade_area',
    label='subtotal_facade_area')

We now set up operational and capital costing parameters that are based off either areas or volume, as well as growth rates:

floor_opex_per_area_pa = dict(
    hotel=-175,
    retail=-225,
    residential=-125,
    parking=-65,
    )
facade_opex_per_area_pa = dict(
    hotel=-50,
    retail=-100,
    residential=-50,
    parking=-25,
    )
opex_growth_rate = 0.035
floor_capex_per_area_pa = dict(
    hotel=-100,
    retail=-150,
    residential=-75,
    parking=-50,
    )
cores_capex_per_vol_pa = -100
plant_capex_per_vol_pa = -1000
capex_growth_rate = 0.035

Now we can set up our Costs class to produce cost Flows and Streams:

class Costs:
    def __init__(
            self,
            params: dict,
            spans: Spans):
        self.params = params
        self.spans = spans

@rk.update_class(Costs)
class Costs:
    def generate_space_costs(self):
        floor_opex = rk.flux.Flow.from_projection(
            name='Floor-derived Operating Expenses',
            value=self.params['initial_floor_opex'],
            proj=rk.projection.Extrapolation(
            form=rk.extrapolation.Compounding(
                rate=self.params['opex_growth_rate']),
            sequence=self.spans.calc_span.to_sequence(frequency=self.params['frequency'])),
            units=currency.units)
        facade_opex = rk.flux.Flow.from_projection(
            name='Facade-derived Operating Expenses',
            value=self.params['initial_facade_opex'],
            proj=rk.projection.Extrapolation(
            form=rk.extrapolation.Compounding(
                rate=self.params['opex_growth_rate']),
            sequence=self.spans.calc_span.to_sequence(frequency=self.params['frequency'])),
            units=currency.units)
        self.opex = rk.flux.Stream(
            name='Space Operating Expenses',
            flows=[floor_opex, facade_opex],
            frequency=self.params['frequency'])

        floor_capex = rk.flux.Flow.from_projection(
            name='Floor-derived Capital Expenses',
            value=self.params['initial_floor_capex'],
            proj=rk.projection.Extrapolation(
            form=rk.extrapolation.Compounding(
                rate=self.params['capex_growth_rate']),
            sequence=self.spans.calc_span.to_sequence(frequency=rk.duration.Type.SEMIDECADE)),
            units=currency.units)
        self.floor_capex = floor_capex.trim_to_span(self.spans.calc_span)  # This is to avoid issues with using >yearly periodicity in projection

We will split the generation of space-based costs from the utility-based costs, as they will be applied to different Entity types:

@rk.update_class(Costs)
class Costs:
    def generate_utils_costs(self):
        utils_capex = rk.flux.Flow.from_projection(
            name='Utilities-derived Capital Expenses',
            value=self.params['initial_utility_capex'],
            proj=rk.projection.Extrapolation(
                form=rk.extrapolation.Compounding(
                    rate=self.params['capex_growth_rate']),
                sequence=self.spans.calc_span.to_sequence(frequency=rk.duration.Type.SEMIDECADE)),
            units=currency.units)
        self.utils_capex = utils_capex.trim_to_span(self.spans.calc_span)  # This is to avoid issues with using >yearly periodicity in projection

Now we can run the Costs class against ‘floor’ and ‘utilities’ Entitys:

for floor in floors:
    floor_params = params.copy()

    floor_params['initial_floor_opex'] = floor_opex_per_area_pa[floor['use']] * floor['subtotal_gfa']
    floor_params['initial_facade_opex'] = facade_opex_per_area_pa[floor['use']] * floor['subtotal_facade_area']
    floor_params['initial_floor_capex'] = floor_capex_per_area_pa[floor['use']] * floor['subtotal_gfa']
    floor_params['opex_growth_rate'] = opex_growth_rate
    floor_params['capex_growth_rate'] = capex_growth_rate
    floor['params'] = floor_params

    floor['events'] = {} if not hasattr(floor, 'events') else floor['events']

    costs = Costs(
        params=floor_params,
        spans=spans)
    costs.generate_space_costs()

    floor['opex'] = costs.opex
    floor['capex'] = costs.floor_capex

The utilities are a bit different, as (for this example), their capex is a factor of their volume. We can see that the utilities are the cores and plant of the property:

utilities = property.filter_by_type(entity_type='utilities', is_assembly=False)

key = 'type'
isolated_ids = [entity['id'] for entity in utilities.get_entities().values()]

url = 'https://speckle.xyz/streams/{0}/commits/{1}?filter={2}'.format(
    stream_id,
    commit_id,
    urllib.parse.quote(json.dumps(
        dict(
            propertyInfoKey = key,
            isolatedIds = isolated_ids))))
IFrame(url, width='100%', height=800)

for utility in utilities.get_entities().values():
    utility_params = params.copy()

    if 'plant' in utility['name']:
        utility_params['initial_utility_capex'] = plant_capex_per_vol_pa * utility['volume']
    elif 'cores' in utility['name']:
        utility_params['initial_utility_capex'] = cores_capex_per_vol_pa * utility['volume']
    utility_params['capex_growth_rate'] = capex_growth_rate
    utility['params'] = utility_params

    utility['events'] = {} if not hasattr(utility, 'events') else utility['events']

    costs = Costs(
        params=utility_params,
        spans=spans)
    costs.generate_utils_costs()

    utility['capex'] = costs.utils_capex

list(utilities.get_entities().values())[0]['capex']

date	Utilities-derived Capital Expenses
2005-12-31 00:00:00	-$643,105.79
2010-12-31 00:00:00	-$665,614.49

And finally aggregate them:

def aggregate_opex(**kwargs):
    return rk.graph.Entity.aggregate_flows(
        **kwargs,
        name='Operational Expenses',
        frequency=frequency)
property.aggregate(
    function=aggregate_opex,
    property='opex',
    label='subtotal_opex',
    relationship_type='spatiallyContains')

def aggregate_capex(**kwargs):
    return rk.graph.Entity.aggregate_flows(
        **kwargs,
        name='Capital Expenses',
        frequency=frequency)
property.aggregate(
    function=aggregate_capex,
    property='capex',
    label='subtotal_capex',
    relationship_type='spatiallyContains')

If we wish to see the ultimate aggregation of those costs, we can do so by totalling them at their root Entity:

property_opex = property.get_roots()['spatiallyContains'][0]['subtotal_opex'].sum()
property_capex = property.get_roots()['spatiallyContains'][0]['subtotal_capex'].sum()

print('Property Average Periodic OPEX: ${:,.2f}'.format(property_opex.movements.mean()))
print('Property Average Periodic CAPEX: ${:,.2f}'.format(property_capex.movements.mean()))

Property Average Periodic OPEX: $-7,488,734.31
Property Average Periodic CAPEX: $-2,007,558.91

To get a sense check if this is reasonable, we can compare the opex and capex to the PGI. First let’s aggregate the PGI for all Entitys:

def aggregate_pgis(**kwargs):
    return rk.graph.Entity.aggregate_flows(
        **kwargs,
        name='Potential Gross Income',
        frequency=frequency)

spatial_containment.aggregate(
    property='pgi',
    function=aggregate_pgis,
    label='subtotal_pgi')

And total it to the root Entity:

property_pgi = property.get_roots()['spatiallyContains'][0]['subtotal_pgi'].sum()
print('Property Average Periodic PGI: ${:,.2f}'.format(property_pgi.movements.mean()))

Property Average Periodic PGI: $18,918,012.54

Now we can calculate the Expenses ratios:

print('Property OpEx as proportion of PGI: {:.2%}'.format(-property_opex.movements.mean() / property_pgi.movements.mean()))

Property OpEx as proportion of PGI: 39.59%

print('Property CapEx as proportion of PGI: {:.2%}'.format(-property_capex.movements.mean() / property_pgi.movements.mean()))

Property CapEx as proportion of PGI: 10.61%

Looking at just the cost-producing Entitys, we can see that in the OpEx and CapEx breakdowns, there is a significant amount of cost generated by non-floor (i.e., non-revenue-generating) Entitys, like cores and plant. This type of analysis is important in understanding the cost drivers of a property, and where the most effective cost-saving measures could be applied.

opex = spatial_containment.sunburst(property='subtotal_opex')
capex = spatial_containment.sunburst(property='subtotal_capex')
fig = plotly.subplots.make_subplots(
    rows=2, cols=1,
    specs=[[{"type": "sunburst"}], [{"type": "sunburst"}]],
    subplot_titles=('Total Operational Expenses', 'Total Capital Expenses'))
fig.append_trace(opex, row=1, col=1)
fig.append_trace(capex, row=2, col=1)
filename='aggregate_exp_chart.html'

py.plot(fig, filename=f'assets/{filename}', auto_open=False)
IFrame(src=f'./{filename}', width='100%', height=800 * len(fig.data))

Calculating Overall Valuation

Now that we have our revenues and cost line items (Flows) generated per Entity, we can accumulate them into Net Operating Income (NOI) and Net Annual Cashflow (NACF) subtotals, as well as the reversion (disposition, or sale) of the whole asset, in order to determine the overall valuation of the property.

We can also calculate the net position of each Entity as its ‘Net Annual Cashflow (NACF)’:

for entity in property.get_entities().values():
    noi = [getattr(entity, 'egi', None), getattr(entity, 'opex', None)]
    noi = [flow.sum() if isinstance(flow, rk.flux.Stream) else flow for flow in list(filter(None, noi))]
    if len(noi) > 0:
        entity['noi'] = rk.flux.Stream(
            name='Net Operating Income',
            flows=noi,
            frequency=frequency)
        capex = getattr(entity, 'capex', None)
        if capex is not None:
            entity['nacf'] = rk.flux.Stream(
                name='Net Annual Cashflow',
                flows=entity['noi'].flows + [capex],
                frequency=frequency)

def aggregate_noi(**kwargs):
    return rk.graph.Entity.aggregate_flows(
        **kwargs,
        name='Net Operating Income',
        frequency=frequency)
property.aggregate(
    function=aggregate_noi,
    property='noi',
    label='subtotal_noi',
    relationship_type='spatiallyContains')

def aggregate_nacf(**kwargs):
    return rk.graph.Entity.aggregate_flows(
        **kwargs,
        name='Net Annual Cashflow',
        frequency=frequency)
property.aggregate(
    function=aggregate_nacf,
    property='nacf',
    label='subtotal_nacf',
    relationship_type='spatiallyContains')

Reversion

To calculate the reversion cashflow, we set up a period that spans the 10th year of the project. This only applies to the asset as a whole, so it is recorded at the root Entity:

root = property.get_roots()['spatiallyContains'][0]

reversion_span = rk.span.Span.from_duration(
    name='Reversion',
    date=params['start_date'] + pd.DateOffset(years=9),
    duration=params['frequency'])

exit_caprate = 0.05
reversion_flow = rk.flux.Flow.from_projection(
    name='Reversion',
    value=root['subtotal_nacf'].sum().movements.values[-1] / exit_caprate,
    proj=rk.projection.Distribution(
        form=rk.distribution.Uniform(),
        sequence=reversion_span.to_sequence(frequency=params['frequency'])),
    units=currency.units)
root['reversion'] = reversion_flow
reversion_flow

date	Reversion
2010-12-31 00:00:00	$284,674,637.71

Now we can aggregate the net and reversion cashflows in the root Entity in order to derive the project’s complete cashflows:

net_cashflows_with_reversion = (rk.flux.Stream(
    name='Net Cashflow with Reversion',
    flows=[root['subtotal_nacf'].sum(), root['reversion']],
    frequency=rk.duration.Type.YEAR)
        .trim_to_span(
            rk.span.Span(
                start_date=params['start_date'],
                end_date=reversion_span.end_date)
    )
)
root['nacf_reversion'] = net_cashflows_with_reversion
net_cashflows_with_reversion

date	Net Annual Cashflow for afe8e730-ad0f-4c2a-a15c-e249e4518fa8 [property] Aggregation (sum)	Reversion
2001	$8,134,691.89	0
2002	$8,599,446.40	0
2003	$9,091,532.28	0
2004	$9,612,601.91	0
2005	$6,862,704.94	0
2006	$10,748,821.65	0
2007	$11,367,816.34	0
2008	$12,023,498.34	0
2009	$12,718,103.14	0
2010	$10,036,741.15	$284,674,637.71

table = rk.flux.Stream(
    name='Table 1.1',
    flows=[
        root['subtotal_egi'].sum(),
        root['subtotal_opex'].sum(),
        root['subtotal_noi'].sum(),
        root['subtotal_capex'].sum(),
        root['subtotal_nacf'].sum(),
        root['reversion'],
        root['nacf_reversion'].sum()
        ],
    frequency=rk.duration.Type.YEAR
    )
table

date	Effective Gross Income for afe8e730-ad0f-4c2a-a15c-e249e4518fa8 [property] Aggregation (sum)	Operational Expenses for afe8e730-ad0f-4c2a-a15c-e249e4518fa8 [property] Aggregation (sum)	Net Operating Income for afe8e730-ad0f-4c2a-a15c-e249e4518fa8 [property] Aggregation (sum)	Capital Expenses for afe8e730-ad0f-4c2a-a15c-e249e4518fa8 [property] Aggregation (sum)	Net Annual Cashflow for afe8e730-ad0f-4c2a-a15c-e249e4518fa8 [property] Aggregation (sum)	Reversion	Net Cashflow with Reversion (sum)
2001	$14,402,849.55	-$6,268,157.66	$8,134,691.89	0	$8,134,691.89	0	$8,134,691.89
2002	$15,086,989.58	-$6,487,543.18	$8,599,446.40	0	$8,599,446.40	0	$8,599,446.40
2003	$15,806,139.47	-$6,714,607.19	$9,091,532.28	0	$9,091,532.28	0	$9,091,532.28
2004	$16,562,220.35	-$6,949,618.44	$9,612,601.91	0	$9,612,601.91	0	$9,612,601.91
2005	$17,357,265.38	-$7,192,855.09	$10,164,410.29	-$5,919,092.61	$6,862,704.94	0	$6,862,704.94
2006	$18,193,426.66	-$7,444,605.02	$10,748,821.65	$0.00	$10,748,821.65	0	$10,748,821.65
2007	$19,072,982.53	-$7,705,166.19	$11,367,816.34	$0.00	$11,367,816.34	0	$11,367,816.34
2008	$19,998,345.35	-$7,974,847.01	$12,023,498.34	$0.00	$12,023,498.34	0	$12,023,498.34
2009	$20,972,069.80	-$8,253,966.66	$12,718,103.14	$0.00	$12,718,103.14	0	$12,718,103.14
2010	$21,996,861.68	-$8,542,855.49	$13,454,006.19	-$6,126,260.85	$10,036,741.15	$284,674,637.71	$294,711,378.86
2011	$23,075,587.32	-$8,841,855.43	$14,233,731.89	0	$14,233,731.89	0	0

Valuation

With our net cashflows including reversion, if we specify a discount rate, we can calculate the present value of the property:

discount_rate = 0.07
pvs = root['nacf_reversion'].sum().pv(
    name='Present Value',
    frequency=rk.duration.Type.YEAR,
    rate=discount_rate)
pvs

date	Present Value
2001-12-31 00:00:00	$7,602,515.78
2002-12-31 00:00:00	$7,511,089.53
2003-12-31 00:00:00	$7,421,398.50
2004-12-31 00:00:00	$7,333,407.97
2005-12-31 00:00:00	$4,893,013.78
2006-12-31 00:00:00	$7,162,393.72
2007-12-31 00:00:00	$7,079,304.69
2008-12-31 00:00:00	$6,997,785.50
2009-12-31 00:00:00	$6,917,805.44
2010-12-31 00:00:00	$149,816,320.83

property_pv = pvs.collapse().movements.item()
print('Property PV: ${:,.0f}'.format(property_pv))

Property PV: $212,735,036