I have the following irregular payments (in and out) occurring on irregular dates
rawData = {{DateObject[{2022, 12, 1, 0, 0, 0.`}, "Instant",
"Gregorian", -8.`], "Balance", 854084.44, 14091.44, 208783.37,
222874.82}, {DateObject[{2022, 12, 16, 0, 0, 0.`}, "Instant",
"Gregorian", -8.`], "Lot 4 Holdback", -30000., -30000., 650.05,
193524.87}, {DateObject[{2022, 12, 20, 0, 0, 0.`}, "Instant",
"Gregorian", -8.`], "Dec 15 2021 Draw", 129967., 129967., 150.52,
323642.39}, {DateObject[{2023, 1, 10, 0, 0, 0.`}, "Instant",
"Gregorian", -8.`], "Jan 15 2023 Draw", 128416.53, 128416.53,
1321.54,
453380.46}, {DateObject[{2023, 2, 14, 0, 0, 0.`}, "Instant",
"Gregorian", -8.`], "Jan 15 2023 Draw2", 47038.23, 47038.23,
3085.51,
503504.19}, {DateObject[{2023, 2, 23, 0, 0, 0.`}, "Instant",
"Gregorian", -8.`], "Mar 15 2023 Draw", 50000., 50000., 881.13,
554385.32}}
Interest at 7% is computed on a daily basis (360 day year). The series for the first few transactions looks like this
rate = .07;
dates = Drop[rawData, 1][[All, 1]];
amts = Drop[rawData, 1][[All, 4]];
start = rawData[[1, {1, 6}]];
series = Join[{start}, Transpose[{dates, amts}]]
and a brute strength method like you see below gets the right ending balance of $554,385.33
start[[2]] +
start[[2]] rate/
360 QuantityMagnitude[(series[[2, 1]] - series[[1, 1]])] +
series[[2, 2]];
% + % rate/360 QuantityMagnitude[(series[[3, 1]] - series[[2, 1]])] +
series[[3, 2]];
% + % rate/360 QuantityMagnitude[(series[[4, 1]] - series[[3, 1]])] +
series[[4, 2]];
% + % rate/360 QuantityMagnitude[(series[[5, 1]] - series[[4, 1]])] +
series[[5, 2]];
% + % rate/360 QuantityMagnitude[(series[[6, 1]] - series[[5, 1]])] +
series[[6, 2]]
I am sure there is a more elegant way of doing this, probably several, and would appreciate suggestions.