Dateflow

Dateflow is an array type that associates dates with payments. Constructing a dateflow and then applying present value calculations to that dateflow make financial analysis easier.

I got the idea of a dateflow from Niels Henrik Bruun. You can read his blog at http://www.bruunisejs.dk/PythonHacks/.

I created some case studies to demonstrate how to use a dateflow.

Stock portfolios

The following exercise were performed on the top companies from theDrucker Institute Company Ranking https://www.drucker.institute/2019-drucker-institute-company-ranking/. Thereare two examples. One on growth of a stock's price and the other on portfolio return.

For stock price growth I created a dateflow for Apple Inc for the five years ended 12/31/2019. I choose this period, before the 2020 bear market to avoid the complexities that bear market is making for analysts. The dateflow looked like this:

        fin∆df∆show df_aapl
2015/01/01   (107,390.00)
2015/02/05        470.00 
2015/05/07        520.00 
2015/08/06        520.00 
2015/11/05        520.00 
2016/02/04        520.00 
2016/05/05        570.00 
2016/08/04        570.00 
2016/11/03        570.00 
2017/02/09        570.00 
2017/05/11        630.00 
2017/08/10        630.00 
2017/11/10        630.00 
2018/02/09        630.00 
2018/05/11        730.00 
2018/08/10        730.00 
2018/11/08        730.00 
2019/02/08        730.00 
2019/05/10        770.00 
2019/08/09        770.00 
2019/11/07        770.00 
2019/12/31    292,950.00

That is the purchase of the stock 1/1/2015, the receipt of dividends for five years and the sale of the stock on 12/31/2019.

This allowed me to calculate the return on an investment.

      fin∆df∆irr df_aapl .2
0.2294155457

df_aapl is the variable where I stored the dateflow. .2 was my first guess of the rate of return.

For the portfolio return I picked Apple, Inc. (aapl); Amazon.com, Inc. (amzn); Cisco Systems, Inc. (csco); and Alphabet Inc. (goog). I built dateflows for the period 12/1/2018 through 12/1/2019.

I assumed an investment in each of approximately $5,000, and no fractional shares. The means 30 shares of Apple (variable df_aapl), 3 shares of Amazon.com, 110 share of Cisco Systems, Inc., and 5 shares of Alphabet Inc.

The dateflows I generated looked like this:

      fin∆df∆show df_aapl2
2019/01/01      (4,907.64)
2019/02/08          21.90 
2019/05/10          23.10 
2019/08/09          23.10 
2019/11/07          23.10 
2019/12/01       8,788.64

      fin∆df∆show df_amzn
2019/01/01      (5,156.19)
2019/12/01       5,543.52

      fin∆df∆show df_csco
2019/01/01      (4,974.88)
2019/01/03          36.30 
2019/04/04          38.50 
2019/07/03          38.50 
2019/10/03          38.50 
2019/12/01       5,187.91

      fin∆df∆show df_goog
2019/01/01      (5,581.85)
2019/12/01       6,685.10

I added these four dateflows together for a portfolio dateflow:

      portfolio←fin∆prtf∆compile df_aapl2 df_amzn df_csco df_goog

      fin∆df∆show portfolio
2019/01/01     (20,620.56)
2019/01/03          36.30 
2019/02/08          21.90 
2019/04/04          38.50 
2019/05/10          23.10 
2019/07/03          38.50 
2019/08/09          23.10 
2019/10/03          38.50 
2019/11/07          23.10 
2019/12/01      26,205.17

This gave me the dateflow I need to calculate the return

      Portfolio_return←fin∆df∆irr portfolio .15
0.2735876542

Proof

      fin∆df∆amortizationTable portfolio 0.2735876542
2019/01/01    0         20620.56271 20620.56271 
2019/01/03   30.927295    ¯36.3     20615.19    
2019/02/08  563.64        ¯21.9     21156.93    
2019/04/04  890.09        ¯38.5     22008.52    
2019/05/10  601.73        ¯23.1     22587.15    
2019/07/03  932.63        ¯38.5     23481.28    
2019/08/09  660.08        ¯23.1     24118.26    
2019/10/03 1014.68        ¯38.5     25094.44    
2019/11/07  666.8         ¯23.1     25738.14    
2019/12/01  467.023978 ¯26205.17398    ¯0.01

With this we can estimate the portfolio's beta:

        S_P_Return←.26153              ⍝ https://dqydj.com/sp-500-return-calculator/
        USTreasury_return←.017760      ⍝ Yahoo.com for ^TNX
        Beta←(Portfolio_return - USTreasury_return)÷(S_P_Return - USTreasury_return)
        Beta
1.049463206

Capital Budgeting

Capital budgeting uses quantitative techniques to evaluate competing long-term projects using their projected costs, and benefits. This means estimating the cash flow from the project, applying one of several calculations to that cash flow and comparing the results. Most calculations involve discounting the cash flow. Dateflow structures are easily used for these calculations.

To demonstrate such calculations I've created Project01:

      fin∆df∆show Project01
2020/01/01     (10,000.00)
2021/01/01       2,500.00 
2022/01/01       1,750.00 
2023/01/01       2,000.00 
2024/01/01       2,500.00 
2025/01/01       2,000.00 
2026/01/01       1,200.00 
2027/01/01       1,000.00 
2028/01/01         750.00

That is a project with an initial buy-in of $10,000 that will throw off cash (either cost savings or profit) as shown above.

Payback Period

Payback period is the amount of time to recoup one's investment.

      1++/1>+\Project01[2;]
6

Net present value

Net present value represents cash flow from the project discounted at an appropriate interest rate less the initial investment.

Use fin∆df∆net_pv:

      fin∆df∆net_pv Project01 .055
1179.284772

Internal Rate of Return

Internal rate of return is the interest rate that will yield a net present value of zero. This function uses a converging iterative algorithm and that requires a guess from the user of the interest rate.

       fin∆df∆irr Project01 .1
0.08670014667

Proof:
      fin∆df∆amortizationTable Project01 0.08670014667
2020/01/01   0    10000 10000    
2021/01/01 908.17 ¯2500  8408.17 
2022/01/01 761.43 ¯1750  7419.6  
2023/01/01 671.91 ¯2000  6091.51 
2024/01/01 551.64 ¯2500  4143.15 
2025/01/01 376.27 ¯2000  2519.42 
2026/01/01 228.15 ¯1200  1547.57 
2027/01/01 140.15 ¯1000   687.72 
2028/01/01  62.28  ¯750     0

Equivalent Annual Cost

This calculation estimates a fixed annual income over the live of the project. It is helpful when comparing projects with varying lives.

      (fin∆df∆net_pv t1 .05)÷fin∆presentValueAnnuity 1 .055 8
218.1481875