## What is an elegant way to add two or more cash flows?

5

1

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_*)


Hello, you don't have to sign inside the post, just edit your profile and we will see :) – Kuba – 2014-02-02T22:45:31.263

If there isn't anything built in, then after joining those lists this is a duplicate of this topic

– Kuba – 2014-02-02T22:49:09.030

Or just pad the quarterly list with zeroes, e.g. Flatten@Riffle[c2, {{0, 0, 0}}, {2, 2 Length[c2], 2}] – ciao – 2014-02-02T23:22:42.233

@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}] – PlaysDice – 2014-02-02T23:44:08.330

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.

– gwr – 2017-02-25T17:40:45.580

3

## 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 }
]
}
],
]

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


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}}]

@user3263870 I have now expanded my solution further as to be able to work with short forms also. Would this meet your needs? – gwr – 2017-02-26T21:35:14.927

1

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]