How to Calculate Cash Flow and Visualize it in Python

Share:
Indexes
  1. What is cash flow?
  2. Why do we want POSITIVE cash flow?
  3. How to calculate cash flow?
  4. Prerequisite
  5. Supporting Video
  6. Google Colaboratory
  7. Sankey
  8. Real Estate Cash Flow - 3 Visuals Showing How Rental Profits Actually Work
Source

How do you calculate whether a property will be successful or not? One of the most import metrics real estate investors use to decide on whether an investment is sound is cash flow. This article will discuss how to calculate cash flow and how to visualize it in Python.

What is cash flow?

Cash flow is a description of the way money flows through a rental property, similar to the way water flows over a waterfall. — Roofstock

Put simply cash flow is monthly income — monthly expenses. The remainder amount is the number you profit on a monthly basis.

Why do we want POSITIVE cash flow?

For your investment to be beneficial it needs to generate a return for you. Monthly cash flow does this by creating a passive income stream.

Here are the benefits of cash flow:

  • Cash flow creates more opportunity. Reinvesting profits from an investment property into another investment is a great way to exponentially grow your financial well-being.
  • Cash flow creates safety. The extra monthly income you bring in can help create a larger savings reserve to protect you against unexpected life expenses (like medical bills, car maintenance, etc.).

How to calculate cash flow?

Let’s start with the equation:

Cash flow = gross rental income — all expenses and cash reserves

Here’s a real-life example of how to calculate the monthly cash flow from a property I own in Riverview, FL.

Monthly income:

  • Rent: $2,300
  • Pet fee: $50
  • TOTAL MONTHLY INCOME: $2,350

Month expenses:

  • Mortgage: $1120 (principal & interest)
  • Property taxes: $430
  • Insurance: $75
  • PMI: $100
  • HOA: $100
  • Property management: $0
  • Vacancy reserves: $115 (5% of my rental income, adjustable depending on risk tolerance)
  • Repair reserves: $115 (5% of my rental income, adjustable depending on risk tolerance)
  • TOTAL MONTHLY EXPENSES: $2,055

Monthly Cash Flow = $2,350 — $2,055 = $295

I aim for $300 in cash flow a month. There is not magic number for target monthly cash flow. However, I’d say to aim for at the minimum $100 cash flow a month.

Ok, let’s review how to visualize cash flow using Python!

Prerequisite

Your own Python environment or a Google account.

Supporting Video

Follow along in my Python Tutorial to for calculating and visualizing cash flow.

YouTube AnalyticsAriel

Python Tutorial

If you do not have an existing Python environment, then I highly suggest to first clone the notebook (at the bottom of the article).

This will allow you to run the Python code in Google Colab (free!). It is a cloud-based environment that lets you run code without having to install Python locally.

I. Install Packages

The first step is installing the necessary packages:

!pip install plotly -q
  • plotly allows us interactive charts — see basic charts here.

II. Import Packages

Next, import the necessary packages.

import plotly.graph_objects as go
import pandas as pd

III. Data Load

Let’s read in data for a sample property. The data is in a CSV format and published publicly on Github.

We can read in the CSV file using pandas. This will convert our CSV file into a dataframe (table with rows and columns).

df = pd.read_csv(
'https://raw.githubusercontent.com/analyticsariel/public-data/main/cash_flow_data.csv')
df.head()

The dataset contains information on monthly income and expenses.

Github

Next, we assign each income and expense value to a variable. We select the column in the dataframe and locate the first value (first row).

# assign variables to values in table
property_address = df['property_address'].iloc[0]
rent = df['rent'].iloc[0]
additional_income = df['additional_income'].iloc[0]
principal_and_interest = df['principal_and_interest'].iloc[0]
taxes = df['taxes'].iloc[0]
insurance = df['insurance'].iloc[0]
property_management = df['property_management'].iloc[0]
cap_ex = df['cap_ex'].iloc[0]
vacancy = df['vacancy'].iloc[0]
utilities = df['utilities'].iloc[0]
additional_expenses = df['additional_expenses'].iloc[0]

We can create total income and expense variables. For example, total income is rent + additional_income.

These new variables will allow us to view the flow of expenses and income.

# summary variables
total_income = rent + additional_income
fixed_expense = principal_and_interest + taxes + insurance
variable_expense = property_management + cap_ex + vacancy + utilities + additional_expenses
total_expense = fixed_expense + variable_expense

IV. Transformations

We want to create a Sankey diagram to visualize our income and expenses, similar to the Plotly example below:

Plotly

Sankey diagrams are a higher degree of complexity to chart relative to other plotly express visualizations.

Sankey diagrams visualize the contributions to a flow by defining source to represent the source node, target for the target node, value to set the flow volume, and label that shows the node name. — Plotly Express Sankey Description

We need to have three components to create our Sankey Diagram -

  1. Node — starting point (i.e. Income)
  2. Target — ending point, sub component of the node (i.e. Rent)
  3. Value — value between flow (i.e. $2,600)

Let’s start by creating a list of all of our labels. We will then assign each label to an integer, which we will refer to in the flow.

# create list of labels for the chart
label_list = [ "Income",
"Rent",
"Additional Income",
"Expense",
"Fixed",
"Variable",
"Principal & Interest",
"Taxes",
"Insurance",
"Property Management",
"CapEx",
"Vacancy",
"Utilities",
"Additional Expenses",
]
# append an index number to each element in the list
label_dict = {}
for i in range(len(label_list)):
label_dict[label_list[i]] = i
label_dict

Output:

Next, we create a nested list to define our node -> target -> value flow.

# create sub list for each node -> target -> value
sankey_list = [
# income
["Income", "Rent", rent],
["Income", "Additional Income", additional_income],
# expenses [fixed]
["Expense", "Fixed", fixed_expense],
["Fixed", "Principal & Interest", principal_and_interest],
["Fixed", "Taxes", taxes],
["Fixed", "Insurance", insurance],
# expenses [variable]
["Expense", "Variable", variable_expense],
["Variable", "Property Management", property_management],
["Variable", "CapEx", cap_ex],
["Variable", "Vacancy", vacancy],
["Variable", "Utilities", utilities],
["Variable", "Additional Expenses", additional_expenses],
]

In order to plot our data, we need to pass three lists.

For the source and target lists we need to pass the integer values assigned to each label. The value list retrieves the last element in our nested list.

# create sublists from the sankey list
source_list = [label_dict[x[0]] for x in sankey_list]
target_list = [label_dict[x[1]] for x in sankey_list]
value_list = [x[2] for x in sankey_list]

print('Source list:', source_list)
print('Target list:', target_list)
print('Value list:', value_list)

Output:

V. Visualization

We can use Plotly Indicator to visualize cash flow

fig = go.Figure(go.Indicator(
mode = "number+delta",
value = total_income - total_expense,
number = {'prefix': "Cash Flow - $"},
domain = {'x': [0, 1], 'y': [0, 1]})
)

fig.update_layout(height=300)

fig.show()

Output:

Next, we create our Sankey diagram by plotting our source, target, and value lists.

# create figure
fig = go.Figure(data=[go.Sankey(
# nodes
node = dict(
pad = 15,
thickness = 20,
line = dict(color = "black", width = 0.5),
label = label_list, # cash flow parameters
hovertemplate='%{label} has total value $%{value}',
),
# links
link = dict(
source = source_list, # start node
target = target_list, # end node
value = value_list # value of end node
))])

fig.update_layout(
hovermode = 'x',
title="Income & Expenses<br>Property Address: {}".format(property_address),
# optional for black background
font=dict(size = 10, color = 'white'),
plot_bgcolor='black',
paper_bgcolor='black'
)

fig.show()

Output:

Conclusion

Sankey diagrams are a great way to visualize multi-flows such as cash flow. This can help individuals quickly identify opportunities to increase income or decrease expenses for cash flow.

Check out my YouTube channel — AnalyticsAriel to get more insight on real estate data sources and data analytics!

Clone notebook

Sources