Sub-City Level Rent Yield Analysis

buy = pd.read_csv('data/Transactions.csv')

buy['year'] = buy['instance_date'].map(lambda x: x.split('-')[2]).astype(int)
buy = buy[buy['year'] >= 2000]

buy['date'] = pd.to_datetime(buy['instance_date'], format='%d-%m-%Y')
buy['count'] = 1
buy = buy.set_index('date').sort_index()

selected = [
    'trans_group_en', 'procedure_name_en', 'property_type_en',
    'property_sub_type_en', 'property_usage_en', 'reg_type_en',
    'area_name_en', 'building_name_en', 'project_name_en',
    'master_project_en', 'rooms_en', 'procedure_area',
    'actual_worth', 'meter_sale_price', 'count'
]
buy = buy[selected]
buy = buy.loc['2014-01-01':'2024-07-31']

buy = buy[buy['property_sub_type_en'] == 'Flat']
buy = buy[buy['reg_type_en'] == 'Existing Properties']
buy = buy[(buy['rooms_en'] == '2 B/R') | (buy['rooms_en'] == '3 B/R')]
buy = buy[(buy['procedure_area'] >= 70) & (buy['procedure_area'] <= 160)]

project_counts = buy['project_name_en'].value_counts()
top_projects = project_counts[project_counts > 10].index
buy = buy[buy['project_name_en'].isin(top_projects)]
buy = buy[buy['master_project_en'].notnull()]

buy_summary = pd.DataFrame([
    {
        'Metric': 'Transactions loaded',
        'Value': f'{len(buy):,}'
    },
    {
        'Metric': 'Date range',
        'Value': f'{buy.index.min().date()} to {buy.index.max().date()}'
    },
    {
        'Metric': 'Unique projects',
        'Value': f'{buy["project_name_en"].nunique():,}'
    },
    {
        'Metric': 'Master communities',
        'Value': f'{buy["master_project_en"].nunique():,}'
    }
])

top_projects_df = (
    buy['project_name_en']
    .value_counts()
    .head(15)
    .rename_axis('Project')
    .reset_index(name='Transactions')
)

top_communities_df = (
    buy['master_project_en']
    .value_counts()
    .head(15)
    .rename_axis('Master community')
    .reset_index(name='Transactions')
)

display(buy_summary.head(5))

print('Top projects by transaction count')
display(top_projects_df.head(5))

print('Top master communities by transaction count')
display(top_communities_df.head(5))

room_counts = buy['procedure_area']

plt.figure(figsize=(8, 4.5))
plt.hist(
    room_counts,
    bins=30,
    color='steelblue',
    edgecolor='white',
    linewidth=0.5
)
plt.xlabel('Procedure area (sqm)')
plt.ylabel('Number of transactions')
plt.title('Distribution of unit sizes for sales')
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

area_bins_df = pd.DataFrame({
    'Procedure area': room_counts.describe()
}).reset_index()
area_bins_df.columns = ['Metric', 'Value']

display(area_bins_df)

annual_volume = buy.resample('YE')['count'].sum()

plt.figure(figsize=(8, 4.5))
plt.bar(
    annual_volume.index.year,
    annual_volume.values,
    color='steelblue',
    width=0.6
)
plt.xlabel('Year')
plt.ylabel('Number of transactions')
plt.title('Annual sales volume for the filtered segment')
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{int(x):,}')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

annual_volume_df = pd.DataFrame({
    'Year': annual_volume.index.year,
    'Transactions': annual_volume.values
})

display(annual_volume_df.tail(5))

annual_price = buy.resample('YE')['meter_sale_price'].median()

plt.figure(figsize=(8, 4.5))
plt.plot(
    annual_price.index,
    annual_price.values,
    marker='o',
    color='steelblue',
    linewidth=2
)
plt.xlabel('Year')
plt.ylabel('Median price (AED/sqm)')
plt.title('Median sale price per sqm for all filtered projects')
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{int(x):,}')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

annual_price_df = pd.DataFrame({
    'Year': annual_price.index.year,
    'Median price (AED/sqm)': annual_price.values
})

display(annual_price_df.tail(5))

rent = pd.read_csv('data/Rent_Contracts.csv')

rent['date'] = pd.to_datetime(rent['contract_start_date'], format='%d-%m-%Y')
rent = rent.set_index('date').sort_index()
rent = rent.loc['2014-01-01':'2024-07-31']

selected_cols = [
    'contract_reg_type_en', 'contract_amount', 'annual_amount',
    'contract_end_date', 'line_number', 'is_free_hold',
    'ejari_bus_property_type_en', 'ejari_property_type_en',
    'ejari_property_sub_type_en', 'property_usage_en',
    'project_name_en', 'master_project_en', 'area_name_en',
    'tenant_type_en', 'actual_area'
]
rent = rent[selected_cols]
rent['count'] = 1

rent = rent[rent['contract_reg_type_en'] == 'New']
rent = rent[rent['ejari_bus_property_type_en'] == 'Unit']
rent = rent[rent['ejari_property_type_en'] == 'Flat']
rent = rent[
    (rent['ejari_property_sub_type_en'] == '2 bed rooms+hall') |
    (rent['ejari_property_sub_type_en'] == '3 bed rooms+hall')
]
rent = rent[(rent['actual_area'] >= 70) & (rent['actual_area'] <= 160)]
rent = rent[rent['property_usage_en'] == 'Residential']

project_counts_rent = rent['project_name_en'].value_counts()
top_projects_rent = project_counts_rent[project_counts_rent > 10].index
rent = rent[rent['project_name_en'].isin(top_projects_rent)]
rent = rent[rent['master_project_en'].notnull()]

rent['annual_rent_m2'] = rent['annual_amount'] / rent['actual_area']

rent_summary = pd.DataFrame([
    {
        'Metric': 'Rental contracts loaded',
        'Value': f'{len(rent):,}'
    },
    {
        'Metric': 'Date range',
        'Value': f'{rent.index.min().date()} to {rent.index.max().date()}'
    },
    {
        'Metric': 'Unique projects',
        'Value': f'{rent["project_name_en"].nunique():,}'
    },
    {
        'Metric': 'Master communities',
        'Value': f'{rent["master_project_en"].nunique():,}'
    }
])

top_projects_rent_df = (
    rent['project_name_en']
    .value_counts()
    .head(15)
    .rename_axis('Project')
    .reset_index(name='Contracts')
)

top_communities_rent_df = (
    rent['master_project_en']
    .value_counts()
    .head(15)
    .rename_axis('Master community')
    .reset_index(name='Contracts')
)

display(rent_summary.head(5))

print('Top projects by rent contract count')
display(top_projects_rent_df.head(5))

print('Top master communities by rent contract count')
display(top_communities_rent_df.head(5))

rent_area = rent['actual_area']

plt.figure(figsize=(8, 4.5))
plt.hist(
    rent_area,
    bins=30,
    color='#e07b39',
    edgecolor='white',
    linewidth=0.5
)
plt.xlabel('Actual area (sqm)')
plt.ylabel('Number of contracts')
plt.title('Distribution of unit sizes for rental contracts')
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

rent_area_df = pd.DataFrame({
    'Metric': rent_area.describe().index,
    'Value': rent_area.describe().values
})

display(rent_area_df)

annual_rent = rent.resample('YE')['annual_rent_m2'].median()

plt.figure(figsize=(8, 4.5))
plt.plot(
    annual_rent.index,
    annual_rent.values,
    marker='o',
    color='#e07b39',
    linewidth=2
)
plt.xlabel('Year')
plt.ylabel('Median annual rent (AED/sqm)')
plt.title('Median rental rate per sqm for all filtered projects')
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{int(x):,}')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

annual_rent_df = pd.DataFrame({
    'Year': annual_rent.index.year,
    'Median annual rent (AED/sqm)': annual_rent.values
})

display(annual_rent_df)

grouped_buy = buy.groupby('project_name_en').resample('YE')['meter_sale_price'].median().reset_index()
pivot_buy = grouped_buy.pivot_table(index='date', columns='project_name_en', values='meter_sale_price')

grouped_rent = rent.groupby('project_name_en').resample('YE')['annual_rent_m2'].median().reset_index()
pivot_rent = grouped_rent.pivot_table(index='date', columns='project_name_en', values='annual_rent_m2')

common_projects = pd.Series(pivot_buy.columns)[pd.Series(pivot_buy.columns).isin(pivot_rent.columns)]
pivot_buy  = pivot_buy[common_projects]
pivot_rent = pivot_rent[common_projects]

roi_projects = pivot_rent / pivot_buy
roi_projects = roi_projects.map(lambda v: np.nan if v is not None and (v > 0.20 or v < 0.02) else v)
roi_projects = roi_projects.ffill()

print(f'Projects with yield data: {roi_projects.shape[1]}')
print(f'Years covered:            {roi_projects.shape[0]}')

roi_summary = roi_projects.loc['2020-12-31':].describe().T
roi_summary['2024'] = roi_projects.iloc[-1]
roi_summary['2023'] = roi_projects.iloc[-2]
roi_summary = roi_summary.sort_values('50%', ascending=False)
roi_summary = roi_summary[roi_summary['count'] > 2]

display_cols = ['count', 'mean', 'std', '25%', '50%', '75%', '2023', '2024']
display(
    round(roi_summary[display_cols].iloc[:20] * 100, 2).rename(columns={
        'count': 'N years', 'mean': 'Mean %', 'std': 'Std %',
        '25%': 'P25 %', '50%': 'Median %', '75%': 'P75 %',
        '2023': '2023 %', '2024': '2024 %'
    }).head(10)
)

master_map = buy[['master_project_en', 'project_name_en']].drop_duplicates().set_index('project_name_en')
roi_ext = roi_summary.merge(master_map, left_index=True, right_index=True, how='left')
roi_ext = roi_ext.merge(roi_std_df.set_index('project_name_en'), left_index=True, right_index=True, how='left')

roi_ext['adj_roi_std'] = np.where(
    roi_ext['roi_std_err_propagation'] > 0.10,
    roi_ext['std'],
    roi_ext['roi_std_err_propagation']
)

top10 = roi_ext.iloc[:10].copy()

plt.figure(figsize=(8, 4.5))
plt.bar(
    range(len(top10)),
    top10['50%'] * 100,
    yerr=top10['adj_roi_std'] * 100,
    capsize=4,
    color='steelblue',
    alpha=0.85,
    error_kw={'elinewidth': 1.2, 'alpha': 0.6}
)
plt.xticks(range(len(top10)), top10.index, rotation=45, ha='right')
plt.ylabel('Median rental yield (%)')
plt.title('Top 10 projects by median rental yield (2020 to 2024)')
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{x:.1f}%')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

top10_df = top10.reset_index().rename(columns={'index': 'project_name_en'})
top10_df = top10_df[[
    'project_name_en',
    'master_project_en',
    '50%',
    'adj_roi_std'
]].copy()

top10_df.columns = [
    'Project',
    'Master community',
    'Median rental yield',
    'Yield std'
]

top10_df['Median rental yield'] = (top10_df['Median rental yield'] * 100).round(2)
top10_df['Yield std'] = (top10_df['Yield std'] * 100).round(2)

display(top10_df.head(5))

roi_regions = roi_ext.groupby('master_project_en')[['mean', 'std', '2024', '2023', 'adj_roi_std']].mean()
roi_regions = roi_regions.sort_values('mean', ascending=False)

display(
    round(roi_regions * 100, 2).rename(columns={
        'mean': 'Avg Yield %', 'std': 'Std %',
        '2024': '2024 %', '2023': '2023 %', 'adj_roi_std': 'Adj Std %'
    }).head(10)
)

top10_regions = roi_regions.head(10).copy()

plt.figure(figsize=(8, 4.5))
plt.bar(
    range(len(top10_regions)),
    top10_regions['mean'] * 100,
    yerr=top10_regions['adj_roi_std'] * 100,
    capsize=4,
    color='#5b8db8',
    alpha=0.85,
    error_kw={'elinewidth': 1.2, 'alpha': 0.6}
)
plt.xticks(range(len(top10_regions)), top10_regions.index, rotation=45, ha='right')
plt.ylabel('Average rental yield (%)')
plt.title('Top 10 master communities by rental yield')
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{x:.1f}%')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

top10_regions_df = top10_regions.reset_index()
top10_regions_df.columns = ['Master community', 'Mean yield', 'Median yield', 'Std', 'Yield std', 'Adjusted yield std']

for col in ['Mean yield', 'Median yield', 'Std', 'Yield std', 'Adjusted yield std']:
    top10_regions_df[col] = (top10_regions_df[col] * 100).round(2)

display(top10_regions_df.head(5))

grouped_region = buy.groupby('master_project_en').resample('YE')['meter_sale_price'].median().reset_index()
pivot_region = grouped_region.pivot_table(index='date', columns='master_project_en', values='meter_sale_price')

pivot_region = pivot_region.dropna(thresh=5, axis=1).ffill()
pct_change = pivot_region.pct_change()
pct_change = pct_change.where(pct_change <= 0.40, np.nan)

regional_means = pct_change.iloc[-4:].mean()
regional_stds = pct_change.std()

sorted_idx = regional_means.sort_values(ascending=False).index
sorted_means = regional_means[sorted_idx]
sorted_stds = regional_stds[sorted_idx]

top10_idx = sorted_idx[:10]
top10_means = sorted_means.loc[top10_idx]
top10_stds = sorted_stds.loc[top10_idx]

plt.figure(figsize=(8, 4.5))
plt.bar(
    range(len(top10_means)),
    top10_means * 100,
    yerr=top10_stds * 100,
    capsize=4,
    color='steelblue',
    alpha=0.75,
    error_kw={'elinewidth': 1.2, 'alpha': 0.5}
)
plt.xticks(range(len(top10_means)), top10_idx, rotation=45, ha='right')
plt.ylabel('Mean annual price change (%)')
plt.title('Top 10 regions by recent 4 year average price change')
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{x:.1f}%')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

regional_growth_df = pd.DataFrame({
    'Master community': top10_idx,
    'Mean annual price change (%)': (top10_means.values * 100).round(2),
    'Std (%)': (top10_stds.values * 100).round(2)
})

display(regional_growth_df.head(5))

long_run = 0.015 + 0.016

yearly_return = pd.DataFrame(
    np.zeros((len(regional_means), 11)),
    index=regional_means.index,
    columns=[f'year_{i}' for i in range(11)]
)

for idx in range(len(yearly_return)):
    start = regional_means.iloc[idx]
    path  = np.concatenate([np.linspace(start, long_run, 6), np.full(4, long_run)])
    yearly_return.iloc[idx, 1:] = path

yearly_return = yearly_return.T
cumulative = (yearly_return + 1).cumprod()
cumulative = cumulative.reset_index().rename(columns={'index': 'year'})
cumulative.head().T.head(10)

inf_start = 0.0381
inf_path = np.concatenate([
    [0],
    np.linspace(inf_start, long_run - 0.016, 6),
    np.full(4, long_run - 0.016)
])
re_path = inf_path + 0.016

years = cumulative['year'].values
inf_cum = (inf_path + 1).cumprod()
re_cum = (re_path + 1).cumprod()

region_cols = [c for c in cumulative.columns if c != 'year']

plt.figure(figsize=(8, 4.5))

for col in region_cols:
    plt.plot(
        years,
        cumulative[col].values,
        color='steelblue',
        alpha=0.25,
        linewidth=1.2
    )

plt.plot(
    years,
    inf_cum,
    color='black',
    linestyle='--',
    linewidth=2,
    label='Inflation baseline'
)
plt.plot(
    years,
    re_cum,
    color='#444444',
    linestyle=':',
    linewidth=2,
    label='RE market estimate'
)

plt.xlabel('Year')
plt.ylabel('Cumulative return (1.0 = no change)')
plt.title('10 year cumulative property value projection by region')
plt.legend(fontsize=10)
plt.gca().yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'{x:.2f}x')
)
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

final_projection_df = pd.DataFrame({
    'Region': region_cols,
    'Final cumulative return': [cumulative[col].values[-1] for col in region_cols]
}).sort_values('Final cumulative return', ascending=False)

final_projection_df['Final cumulative return'] = final_projection_df['Final cumulative return'].round(3)

benchmark_df = pd.DataFrame([
    {
        'Series': 'Inflation baseline',
        'Final cumulative return': round(inf_cum[-1], 3)
    },
    {
        'Series': 'RE market estimate',
        'Final cumulative return': round(re_cum[-1], 3)
    }
])

print('Final year projection by region')
display(final_projection_df.head(5))

print('Benchmark paths')
display(benchmark_df)

end_values = cumulative.iloc[-1][region_cols]

plt.figure(figsize=(8, 4.5))
plt.hist(
    end_values,
    bins=15,
    color='steelblue',
    edgecolor='white',
    linewidth=0.5
)
plt.axvline(
    end_values.median(),
    color='black',
    linestyle='--',
    linewidth=1.5,
    label=f'Median: {end_values.median():.2f}x'
)
plt.xlabel('Cumulative return at year 10')
plt.ylabel('Number of regions')
plt.title('Distribution of projected 10 year returns across regions')
plt.legend()
plt.tick_params(length=0)
plt.tight_layout()
plt.show()

end_values_summary = pd.DataFrame([
    {
        'Metric': 'Mean',
        'Value': f'{end_values.mean():.3f}x'
    },
    {
        'Metric': 'Median',
        'Value': f'{end_values.median():.3f}x'
    },
    {
        'Metric': 'Std',
        'Value': f'{end_values.std():.3f}'
    },
    {
        'Metric': 'Min',
        'Value': f'{end_values.min():.3f}x'
    },
    {
        'Metric': 'Max',
        'Value': f'{end_values.max():.3f}x'
    },
    {
        'Metric': 'P25',
        'Value': f'{end_values.quantile(0.25):.3f}x'
    },
    {
        'Metric': 'P75',
        'Value': f'{end_values.quantile(0.75):.3f}x'
    }
])

display(end_values_summary)

property_price = 1_800_000
fees_rate = 0.077
down_payment_rate = 0.20
loan_years = 5
annual_interest_rate = 0.045
yearly_running_cost = 30_000
holding_years = 10

down_payment = property_price * down_payment_rate
fees = property_price * fees_rate
total_initial_payment = down_payment + fees

loan_amount = property_price * (1 - down_payment_rate)
loan_months = loan_years * 12
monthly_rate = annual_interest_rate / 12
monthly_payment = (loan_amount * monthly_rate) / (1 - (1 + monthly_rate) ** -loan_months)
total_repaid = monthly_payment * loan_months
total_interest = total_repaid - loan_amount
total_running = yearly_running_cost * holding_years
total_cost = total_initial_payment + total_interest + total_running

ownership_cost_df = pd.DataFrame([
    {
        'Metric': 'Down payment + fees',
        'Value': f'AED {total_initial_payment:,.0f}'
    },
    {
        'Metric': 'Loan amount',
        'Value': f'AED {loan_amount:,.0f}'
    },
    {
        'Metric': 'Monthly repayment',
        'Value': f'AED {monthly_payment:,.0f}'
    },
    {
        'Metric': 'Total repaid (5 year loan)',
        'Value': f'AED {total_repaid:,.0f}'
    },
    {
        'Metric': 'Total interest paid',
        'Value': f'AED {total_interest:,.0f}'
    },
    {
        'Metric': 'Total running costs',
        'Value': f'AED {total_running:,.0f}'
    },
    {
        'Metric': 'Total cost of ownership',
        'Value': f'AED {total_cost:,.0f}'
    }
])

display(ownership_cost_df)