gibbz00 gibbz00 - 25 days ago 10
R Question

Calculating reinvestment of dividends with a cumulative approach

This is what my data.table looks like. The A:E columns are just to draw comparison to excel. Column

NewShares
is my desired column. I DO NOT have that column in my data.

A B C D E F
dt<-fread('
InitialShares Level Price Amount CashPerShare NewShares
1573.333 0 9.5339 13973.71 0 1573.333
0 1 10.2595 0 .06689 1584.73
0 1 10.1575 0 .06689 1596.33
0 1 9.6855 0 .06689 1608.58')


I am trying to calculate
NewShares
with the assumption that new shares are added to
InitialShares
by reinvesting dividends(
NewShares*CashPershare
) at 90% of the price(
Price*.9
). In excel land the formula will be
=F2+((F2*E3*B3)/(C3*0.9))
as of the second row. The first row is just equal to
InitialShares
.

In R land, I am trying(which is not quite right):

dt[,NewShares:= cumsum(InitialShares[1]*Level * CashPerShare/(Price*.9)+InitialShares[1])]


Please pay attention to the Decimal points of
NewShares
once you generate the field in order to validate your approach.

Answer

If you expand your formula, you'll realize that this works:

dt[, NewShares := cumprod(1+Level*CashPerShare/Price/0.9)*InitialShares[1]]
Comments