CODEWITHSUNDEEP
r
Gloria Dalla Costa
Gloria Dalla Costa

Reputation: 397

R: calculating interests and balance at each step

I have a stupid question but I can't solve it easily with lag/lead or other things

Let's say I have this table, I have an initial balance of 100, Position is if I bid or not, and percentage is what I get if I bid, how can i calculate the balance to get something like this?

Position  Percentage_change    Balance
   0             0.01            100
   0           - 0.01            100
   1             0.02            102
   1             0.05            107.1
   0           - 0.02            107.1
   1             0.03            110.3

Upvotes: 0

Views: 30

Answers (1)

Jonny Phelps
Jonny Phelps

Reputation: 2727

cumprod is the function you are looking for eg

df <- data.frame(Position = c(0,0,1,1,0,1),
                 Percentage_change = c(0.01, -0.01, 0.02, 0.05, -0.02, 0.03))

# convert in to multiplier form eg 100 * 1.01
df$Multiplier <- df$Percentage_change + 1
# when position is 0, reset this to 1 so there is no change to the balance
df[df$Position == 0, ]$Multiplier <- 1 
# take starting balance of 100 and times by cumulative product of the multipliers
df$Balance <- 100 * cumprod(df$Multiplier)

df
  Position Percentage_change Multiplier Balance
1        0              0.01       1.00 100.000
2        0             -0.01       1.00 100.000
3        1              0.02       1.02 102.000
4        1              0.05       1.05 107.100
5        0             -0.02       1.00 107.100
6        1              0.03       1.03 110.313

Upvotes: 1

Related Questions