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.
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.576408080165Modelling 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.563112Once 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