5
$\begingroup$

When modeling a swap contract a common operation is to sum up two cash flows producing a resulting cashflow with netted amounts in the highest granularity of the two initial cash flows. What is a practical way to do this?

c1 = Cashflow[Annuity[-765.3, 4, 1/12]]; (*-monthly payments_*)

c2 = Cashflow[Annuity[2357.124, 4, 1/4]]; (*_-quarterly payments_*)

c3 = ? (*_-result is the netted cashflow in monthly granularity_*)

A fragment of result modeled with Numbers, with two cash flows in different granularity and the resulting with netted amounts.

$\endgroup$
  • $\begingroup$ Hello, you don't have to sign inside the post, just edit your profile and we will see :) $\endgroup$ – Kuba Feb 2 '14 at 22:45
  • $\begingroup$ If there isn't anything built in, then after joining those lists this is a duplicate of this topic $\endgroup$ – Kuba Feb 2 '14 at 22:49
  • $\begingroup$ Or just pad the quarterly list with zeroes, e.g. Flatten@Riffle[c2, {{0, 0, 0}}, {2, 2 Length[c2], 2}] $\endgroup$ – ciao Feb 2 '14 at 23:22
  • $\begingroup$ @rasher This tweak generates a list of monthly payments from the quarterly Cashflow using your approach Flatten@Riffle[c2[[1,All,2]],{{0,0,0}},{2,2 Length[c2[[1]],2}] $\endgroup$ – PlaysDice Feb 2 '14 at 23:44
  • $\begingroup$ I have changed aggregate to add in the title. Within the time series framework TimeSeriesAggregate does have a very concise meaning which is quite different. $\endgroup$ – gwr Feb 25 '17 at 17:40
3
$\begingroup$

Using the Temporal Data Framework

While Cashflow is a nice feature for doing financial calculations it lacks the powerful functionality to process time series that has been built into Mathematica as of Version 10 and later.

Using this framework we can convert the cashflows into event series (which essentialy is what they are) and can then use TimeSeriesResample to align the cashflows and add them up correctly.

Implementation

addCashflows[ cashflows : { __Cashflow} ] := Module[
    {
        minTime,
        maxTime,
        minTimeInc,
        timeSeries
    },

    (* convert the cashflows *)
    timeSeries = EventSeries[ #, MissingDataMethod -> {"Constant", 0} ]& @@@ cashflows;

    (* find the parameters to align the event series *)
    { minTime, maxTime, minTimeInc } = timeSeries // RightComposition[
        Map[ {#["FirstTime"],#["LastTime"], MinimumTimeIncrement @ # }& ],
        Transpose,
        Apply[ { Min @ #1, Max @ #2, Min @ #3 }& ]    
    ];

    (* align event series, add them and return the total cashflow *)
    timeSeries // RightComposition[
        Map[ TimeSeriesResample[ #, { minTime, maxTime, minTimeInc }] & ],
        Total,
        Normal,
        Cashflow
    ]

]

To make all this more comfortable and in the end "more elegant" we can overload the definition for Cashflow:

Unprotect[ Cashflow ];

Cashflow/: Times[ a_ , Cashflow[ c_?VectorQ ] ] := Cashflow[ a c ]
Cashflow/: Times[ a_ , Cashflow[ c_?VectorQ, q_ ] ] := Cashflow[ a c, q ]
Cashflow/: Times[ a_ , cashflow : Cashflow[{ { _ , _ }.. }] ] := Apply[
    Function[ { time, value }, {time, a value} ], 
    cashflow, 
    {2} 
]

Cashflow/: Plus[ cashflows__Cashflow ] := { cashflows } // RightComposition[
    ReplaceAll[
      {
        Cashflow[ c_?VectorQ ] :> Cashflow[ 
            Transpose @ { Range[ 0, Length @ c - 1], c } 
        ],
        Cashflow[ c_?VectorQ, q_?NumericQ ] :> Cashflow[ 
            Transpose @ { NestList[ # + q & , 0 , Length @ c - 1 ], c } 
        ]
      }
    ],
    addCashflows
]

Protect[ Cashflow ];

Testing the Functionality

Now we can test the functionality using the OP's data.

c1 = Cashflow[ Annuity[-765.3,   4, 1/12] ] (* monthly payments_  *)
c2 = Cashflow[ Annuity[2357.124, 4, 1/4 ] ] (* quarterly payments *)

totalCF = c1 + c2;
totalCF // First // Dataset

TotalCashflow

We can now also subtract cash flows:

c1 - c2

Cashflow[{{1/12, -765.3}, {1/6, -765.3}, {1/4, -3122.42}, {1/ 3, -765.3}, {5/12, -765.3}, {1/2, -3122.42}, {7/12, -765.3}, {2/ 3, -765.3}, {3/4, -3122.42}, {5/6, -765.3}, {11/ 12, -765.3}, {1, -3122.42}, {13/12, -765.3}, {7/6, -765.3}, {5/ 4, -3122.42}, {4/3, -765.3}, {17/12, -765.3}, {3/2, -3122.42}, {19/ 12, -765.3}, {5/3, -765.3}, {7/4, -3122.42}, {11/6, -765.3}, {23/ 12, -765.3}, {2, -3122.42}, {25/12, -765.3}, {13/6, -765.3}, {9/ 4, -3122.42}, {7/3, -765.3}, {29/12, -765.3}, {5/2, -3122.42}, {31/ 12, -765.3}, {8/3, -765.3}, {11/4, -3122.42}, {17/6, -765.3}, {35/ 12, -765.3}, {3, -3122.42}, {37/12, -765.3}, {19/6, -765.3}, {13/ 4, -3122.42}, {10/3, -765.3}, {41/12, -765.3}, {7/ 2, -3122.42}, {43/12, -765.3}, {11/3, -765.3}, {15/ 4, -3122.42}, {23/6, -765.3}, {47/12, -765.3}, {4, -3122.42}}]

We can now also work with short forms of Cashflow:

Cashflow[ {1,2,3} ] + Cashflow[ {1,2,3}, 2 ]

Cashflow[{{0, 2}, {1, 2}, {2, 5}, {3, 0}, {4, 3}}]

$\endgroup$
  • $\begingroup$ @user3263870 I have now expanded my solution further as to be able to work with short forms also. Would this meet your needs? $\endgroup$ – gwr Feb 26 '17 at 21:35
1
$\begingroup$

Kuba (comments) gave a useful hint. I did it using this approach and found it acceptable. Still the code doesn't look very intuitive. I am still tempted to do the aggregation step rather in SQL therefore. The riffle approach (by @rasher) does work, would however require a test of all cash flows to determine highest granularity. This I think requires more overhead. Here is the working example using GatherBy:

c1 = Cashflow[Annuity[-720, 10, 1/12]];  
c2 = Cashflow[Annuity[{3632.81, {3632.81*-1, 0}}, 10, 1]];  

gathered = GatherBy[Union[c1[[1]], c2[[1]], Cashflow[{{1/12, l1}}][[1]]], First];  
aggregated = Map[{#[[1, 1]], Total[#[[;; , 2]]]} &, gathered];  

Grid[c2[[1]][[1 ;; 3]], Frame -> All]  
Grid[c1[[1]][[1 ;; 24]], Frame -> All]  
Grid[gathered[[1 ;; 24]], Frame -> All]  
Grid[aggregated[[1 ;; 24]], Frame -> All] 
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.