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

7 次查看（过去 30 天）
Max Simonelli
Max Simonelli 2022-2-19
回答： Sufiyan 2023-12-21
Ran in:
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?
  2 个评论
Ive J
Ive J 2022-2-19
Ran in:
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
Max Simonelli 2022-2-19
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Sufiyan
Sufiyan 2023-12-21
Ran in:
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!

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

产品


版本

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by