Cashflow projection in Python

Sharing is caring!

Last Updated on July 14, 2022 by Jay

Folks work in the finance industry deal with cashflow projection everyday, but mostly in Excel. Indeed, Excel is easy to use and transparent. You can piece together a cashflow projection model in minutes – build a couple of formulas then drag down. Boom! There goes our projection model. In this tutorial, we are going to learn how to build a simple cashflow projection model in Python. The example here will eventually lead to a more complex model where we build a mortgage calculator in Python.

This tutorial is part of the “Integrate Python with Excel” series, you can find the table of content here for easier navigation.

Python tools for cashflow projection

We can use either lists or the pandas library to project cashflow. There are likely other tools or libraries out there, but I haven’t really explored them. So we’ll only look at lists and pandas here.

The example

Assuming we have an asset that will produce income for 30 years. The first income is $100, and grows at 6% annually for the next 29 years (after 30 year there’s no more income). Calculate the present value of the asset, discounted at 2% per year.

Excel modelling

Excel users probably already know how to model this problem (in Excel). Several input values, formulas, and drag down. Let’s take a look at the below. It’s only showing 10 years here, but the actual Excel file projects out 30 years.

Cashflow projection in Excel
Cashflow projection in Excel

Modelling with list

A Python list is an ordered data structure, which is exactly what we need to model a time series data (i.e. cashflow over time). Let’s create our assumptions below. Although we can use lists to model cashflow, it’s not a good idea to do so because we’ll have to do a lot of the low-level data manipulation ourselves. Here we are only demonstrating the idea, and in practice we should be using pandas (or numpy) to model a cashflow projection.

>>> income_first_yr = 100
>>> growth_rt = 0.06
>>> discocunt_rt = 0.02

Second, let’s set up the initial value in the list, cashflow = [income_first_yr]. Then we loop through 29 additional times to calculate the income at each subsequent year, and add it to the list. There we have a 30-year cashflow projection.

>>> cashflow = [income_first_yr]
>>> for i in range(29):
	cashflow.append(cashflow[i] * (1+ growth_rt))

Next, we’ll set up the discounting vector in another list.

discount_vector = [discount_rt]
for i in range(29):
    discount_vecotr.append(discount_vecotr[i] / (1 + discount_rt))

We can put these two vectors side by side if you prefer to see them visually. Here we’ll use the zip() function to do that. It basically combines two lists at each ith item, and return them as a tuple. Here’s what it looks like. Note this zip() function actually creates 30 tuples!

>>> for i in zip(cashflow, discount_vector):
	print(i)

	
(100, 1)
(106.0, 0.9803921568627451)
(112.36, 0.9611687812379853)
(119.1016, 0.9423223345470444)
(126.247696, 0.9238454260265141)
(133.82255776000002, 0.9057308098299157)
(141.85191122560002, 0.8879713821861919)
(150.36302589913603, 0.8705601786139135)
(159.3848074530842, 0.8534903711901113)
(168.94789590026926, 0.8367552658726581)
(179.08476965428542, 0.820348299875155)
(189.82985583354255, 0.8042630390932892)
(201.2196471835551, 0.7884931755816561)
(213.29282601456842, 0.773032525080055)
(226.09039557544253, 0.7578750245882893)
(239.6558193099691, 0.7430147299885189)
(254.03516846856726, 0.7284458137142342)
(269.27727857668134, 0.7141625624649355)
(285.43391529128223, 0.700159374965623)
(302.5599502087592, 0.6864307597702186)
(320.7135472212847, 0.6729713331080575)
(339.95636005456186, 0.6597758167726053)
(360.3537416578356, 0.6468390360515739)
(381.97496615730574, 0.6341559176976215)
(404.8934641267441, 0.6217214879388445)
(429.1870719743488, 0.6095308705282789)
(454.93829629280975, 0.597579284831646)
(482.23459407037836, 0.5858620439525941)
(511.1686697146011, 0.5743745528947001)
(541.8387898974772, 0.5631123067595099)

We know for each tuple created in the zip() function, the first element is the income, and the second element is the discount rate, so we can multiply them to get the discounted cashflow. Let’s loop through the tuples to calculate discounted cashflow, and put it into another list. Once we have this, the Present Value is just the sum of the resulting list.

>>> discounted_cashflow = []
>>> for item in zip(cashflow, discount_vector):
	discounted_cashflow.append(item[0] * item[1])

	
>>> discounted_cashflow
[100, 103.92156862745098, 107.99692425990003, 112.23209776028827, 116.63335649598585, 121.20721361347549, 125.96043767674904, 130.90006268368037, 136.03339847519723, 141.36804155265597, 146.9118863194268, 152.67313676332589, 158.6603185971818, 164.8822918755027, 171.34826410591455, 178.06780387477397, 185.05085500711806, 192.30775128190703, 199.84923172433474, 207.68645649783807, 215.83102341932192, 224.29498512204046, 233.09086689153227, 242.23168520100413, 251.7309669735925, 261.6027696000079, 271.86170174118473, 282.5229449467214, 293.6022761211026, 305.1160908709498]

>>> sum(discounted_cashflow)
5535.576408080165

Modelling with pandas

Creating a cashflow projection with pandas is easier than using just lists. Since we can use some of the built-in pandas methods. Let’s start from creating a pandas dataframe with 30 rows and 2 columns – one column for Income projection and another column for the Discount vector.

>>> df = pd.DataFrame({'Income':[100*1.06**i for i in range(30)],
		       'Discount_vector':[1.02**(-i) for i in range(30)]})
>>> df
        Income  Discount_vector
0   100.000000         1.000000
1   106.000000         0.980392
2   112.360000         0.961169
3   119.101600         0.942322
4   126.247696         0.923845
5   133.822558         0.905731
6   141.851911         0.887971
7   150.363026         0.870560
8   159.384807         0.853490
9   168.947896         0.836755
10  179.084770         0.820348
11  189.829856         0.804263
12  201.219647         0.788493
13  213.292826         0.773033
14  226.090396         0.757875
15  239.655819         0.743015
16  254.035168         0.728446
17  269.277279         0.714163
18  285.433915         0.700159
19  302.559950         0.686431
20  320.713547         0.672971
21  339.956360         0.659776
22  360.353742         0.646839
23  381.974966         0.634156
24  404.893464         0.621721
25  429.187072         0.609531
26  454.938296         0.597579
27  482.234594         0.585862
28  511.168670         0.574375
29  541.838790         0.563112

Once we have both vectors, we can multiply them to get discounted cashflow, then sum() to get the Present Value.

>>> df['Discounted_cashflow'] = df['Income'] * df['Discount_vector']
>>> df
        Income  Discount_vector  Discounted_cashflow
0   100.000000         1.000000           100.000000
1   106.000000         0.980392           103.921569
2   112.360000         0.961169           107.996924
3   119.101600         0.942322           112.232098
4   126.247696         0.923845           116.633356
5   133.822558         0.905731           121.207214
6   141.851911         0.887971           125.960438
7   150.363026         0.870560           130.900063
8   159.384807         0.853490           136.033398
9   168.947896         0.836755           141.368042
10  179.084770         0.820348           146.911886
11  189.829856         0.804263           152.673137
12  201.219647         0.788493           158.660319
13  213.292826         0.773033           164.882292
14  226.090396         0.757875           171.348264
15  239.655819         0.743015           178.067804
16  254.035168         0.728446           185.050855
17  269.277279         0.714163           192.307751
18  285.433915         0.700159           199.849232
19  302.559950         0.686431           207.686456
20  320.713547         0.672971           215.831023
21  339.956360         0.659776           224.294985
22  360.353742         0.646839           233.090867
23  381.974966         0.634156           242.231685
24  404.893464         0.621721           251.730967
25  429.187072         0.609531           261.602770
26  454.938296         0.597579           271.861702
27  482.234594         0.585862           282.522945
28  511.168670         0.574375           293.602276
29  541.838790         0.563112           305.116091
>>> df['Discounted_cashflow'].sum()
5535.5764080801655

Leave a Reply

Your email address will not be published. Required fields are marked *