Message Boards Message Boards

0
|
2606 Views
|
1 Reply
|
1 Total Likes
View groups...
Share
Share this post:

How to code irregular recurrence pattern of interest and principal payments

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

Output

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.

POSTED BY: Roger J Brown

I would say that this is a typical application of Fold[]:

Fold[#1 + #1 rate/360 QuantityMagnitude[(series[[#2 + 1, 1]] - series[[#2, 1]])] + series[[#2 + 1, 2]] &,
 start[[2]], Range[Length[series] - 1]]
POSTED BY: Henrik Schachner
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract