Update values in a timetable with values and matching dates from a different timetable.

Hello everyone, I've this problem and I just can't seem to figure it out yet.

Lets say I have a timetable like this:

cashflowdates1 = datetime({'2022-02-18';'2022-07-09';'2023-01-09'});
cashflows1=[-0.542;0.25;10];

TT=timetable(cashflowdates1,cashflows1)

TT = 3×1 timetable
    cashflowdates1    cashflows1
    ______________    __________

    18-Feb-2022         -0.542  
    09-Jul-2022           0.25  
    09-Jan-2023             10  

This is just like output of the cashflows function for a fixedbond instrument in the financial toolbox. That first negative number represents accrued interest.

Since I want to compute the rate of return for an investment, I need to replace that number with the full price I pay for the bond.

Simple enough, right?

initialinvestment=-3.3;

TT{1,1}=initialinvestment

TT = 3×1 timetable
    cashflowdates1    cashflows1
    ______________    __________

    18-Feb-2022          -3.3   
    09-Jul-2022          0.25   
    09-Jan-2023            10   

And now I can compute the return I wanted.

xirr(TT.cashflows1,TT.cashflowdates1,0.1,5,1)

ans = 2.6536

Now, here's my problem. I would like to do this for multiple cashflows and multiple prices and dates.

So my data actually now looks like this:

cashflowdates1 = datetime({'2022-02-18';'2022-02-19';'2022-02-20';'2022-07-09';'2023-01-09'});
cashflows1=[-0.542;0;0;0.25;10];
cashflows2=[0;-0.562;0;0.25;10];
cashflows3=[0;0;-0.58;0.25;10];

TT=timetable(cashflowdates1,cashflows1,cashflows2,cashflows3)

TT = 5×3 timetable
    cashflowdates1    cashflows1    cashflows2    cashflows3
    ______________    __________    __________    __________

    18-Feb-2022         -0.542             0            0   
    19-Feb-2022              0        -0.562            0   
    20-Feb-2022              0             0        -0.58   
    09-Jul-2022           0.25          0.25         0.25   
    09-Jan-2023             10            10           10   

initialinvestment=[-3.3;-3.4;-3.2];
initialinvestmentdates=datetime({'2022-02-18';'2022-02-19';'2022-02-20'});
initialinvestmentTT=timetable(initialinvestmentdates,initialinvestment)

initialinvestmentTT = 3×1 timetable
    initialinvestmentdates    initialinvestment
    ______________________    _________________

    18-Feb-2022                     -3.3       
    19-Feb-2022                     -3.4       
    20-Feb-2022                     -3.2       

Is there a way to update the cashflow timetable with the data of the initialinvestment timetable?

[Ive J]

Why do you have several cashflows variables in table TT? Wouldn't it be easier to merge all?

dts = datetime({'2022-02-18';'2022-02-19';'2022-02-20';'2022-07-09';'2023-01-09'});
cashflow = [-0.542; -0.562; -0.58; 0.25; 10];
ttab = timetable(dts, cashflow, 'DimensionNames', {'date', 'Variables'})

ttab = 5×1 timetable
       date        cashflow
    ___________    ________

    18-Feb-2022     -0.542 
    19-Feb-2022     -0.562 
    20-Feb-2022      -0.58 
    09-Jul-2022       0.25 
    09-Jan-2023         10 

initinvest = [-3.3; -3.4; -3.2];
initdts = datetime({'2022-02-18';'2022-02-19';'2022-02-20'});
itab =timetable(initdts, initinvest, 'DimensionNames', {'date', 'Variables'})

itab = 3×1 timetable
       date        initinvest
    ___________    __________

    18-Feb-2022       -3.3   
    19-Feb-2022       -3.4   
    20-Feb-2022       -3.2   

[idx1, idx2] = ismember(ttab.date, itab.date);
ttab.cashflow(idx1) = itab.initinvest(idx2(idx1));
disp(ttab)

       date        cashflow
    ___________    ________

    18-Feb-2022      -3.3  
    19-Feb-2022      -3.4  
    20-Feb-2022      -3.2  
    09-Jul-2022      0.25  
    09-Jan-2023        10  

[Max Simonelli]

Thanks for the reply. There's a reason for that. I need each cashflow as a separte vector for imput into the xirr function.

Merging all cashflows into one, gives me only one result. But I need to come up with a vector of returns, one for each cashflow.

Answer 1

[Sufiyan]

Hello Max,

You can refer to the code below.

cashflowdates1 = datetime({'2022-02-18';'2022-02-19';'2022-02-20';'2022-07-09';'2023-01-09'});
cashflows1 = [-0.542; 0; 0; 0.25; 10];
cashflows2 = [0; -0.562; 0; 0.25; 10];
cashflows3 = [0; 0; -0.58; 0.25; 10];

TT = timetable(cashflowdates1, cashflows1, cashflows2, cashflows3)

TT = 5×3 timetable
    cashflowdates1    cashflows1    cashflows2    cashflows3
    ______________    __________    __________    __________

    18-Feb-2022         -0.542             0            0   
    19-Feb-2022              0        -0.562            0   
    20-Feb-2022              0             0        -0.58   
    09-Jul-2022           0.25          0.25         0.25   
    09-Jan-2023             10            10           10   

initialinvestment = [-3.3; -3.4; -3.2];
initialinvestmentdates = datetime({'2022-02-18'; '2022-02-19'; '2022-02-20'});
initialinvestmentTT = timetable(initialinvestmentdates, initialinvestment)

initialinvestmentTT = 3×1 timetable
    initialinvestmentdates    initialinvestment
    ______________________    _________________

    18-Feb-2022                     -3.3       
    19-Feb-2022                     -3.4       
    20-Feb-2022                     -3.2       

for i = 1:length(initialinvestment)
    % Find the index of the minimum value in each column of cash flows
    [~, minIndex] = min(TT{1:end, i});
    
    TT{minIndex, i} = initialinvestment(i);
end
TT

TT = 5×3 timetable
    cashflowdates1    cashflows1    cashflows2    cashflows3
    ______________    __________    __________    __________

    18-Feb-2022          -3.3             0             0   
    19-Feb-2022             0          -3.4             0   
    20-Feb-2022             0             0          -3.2   
    09-Jul-2022          0.25          0.25          0.25   
    09-Jan-2023            10            10            10   

xirr_result = zeros(length(initialinvestment), 1);

for i = 1:length(initialinvestment)
    xirr_result(i) = xirr(TT{:, i}, TT.Properties.RowTimes, 0.1, 5, 1);
end
xirr_result

xirr_result = 3×1

2.6536 2.5445 2.8179

Hope it helps!