Calculate Beginning and Ending Balances Using R

Question

I am trying to follow a process where I make a contribution to an investment every period, that investment grows or shrinks over time and then I have an ending balance. The next period I take that ending balance, add the next period’s contribution and invest again. The process repeats from there.
In table form this would be shown as

So the 105 is the 100 * 1.05, the 205 is the 100+105 which is then multiplied by 0.95 to get 194.75. The 304.75 is the 194.75+110, etc.
This is easy to do in a spreadsheet, but pretty tricky in R. I tried

df <- df %>% 
    mutate(Return = ifelse(is.na(lag(Contribution,  
                            Contribution*Grow,
                            (lag(Contribution)*lag(Grow)*Grow)+(Contribution*Grow)
                            ))))

But I got the error message

Error in mutate(): ℹ In argument: Return = ifelse(...). Caused by error in lag(): ! n must be a whole number, not a double vector. Run rlang::last_trace() to see where the error occurred.

Below is the data.

df <- data.frame(Contribution=c(100,100,110,110),Grow=c(1.05,0.95,1.1,0.925))

I tried the code shown and the expectation is in the table.

Dave2e · Accepted Answer · 2024-06-02 23:46:28Z

2

Sometimes it is just easier with a loop:

df$begin <- 0
df$end<- 0

for(i in 1:nrow(df)) {
   df$begin[i] <- df$Contribution[i]+ifelse(i==1, 0, df$end[i-1])
   df$end[i] <- df$begin[i] *df$Grow[i]
}
df[, c("Contribution", "begin", "Grow", "end")]

answered Jun 2, 2024 at 23:46

Dave2e

24.3k18 gold badges46 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thelatemail Over a year ago

I was scratching my head wondering if there was a cumsum/cumprod solution to this, but I don't think there is. Nothing wrong with a loop if that is indeed the case!

langtang · Accepted Answer · 2024-06-03 00:48:27Z

Agree with @Dave2e re the simplicity of a loop, but you can also do this with Reduce()

df %>% mutate(
  EndBalance = Reduce(
    f = \(z,i) (z+Contribution[i])*Grow[i],
    x = 2:n(),
    init = Contribution[1]*Grow[1],
    accumulate = T
  )
)

Output:

  Contribution  Grow EndBalance
1          100 1.050   105.0000
2          100 0.950   194.7500
3          110 1.100   335.2250
4          110 0.925   411.8331

Above, I set the initial value to be the first row's product of contribution and grow (and then x from 2 to the n()).. However, an alternative is to set the initial value to 0, and iterate x from 1 to n(), but then accumualate will return a vector of length 5 (with the initial value, 0, being the first one), so you have to remove that first value in the vector to make the mutate work:

mutate(
  df,
  EndBalance = Reduce(
    f = \(z,i) (z+Contribution[i])*Grow[i],
    x = 1:n(),
    init=0, 
    accumulate = T)[-1]
)

Output: same.

Collectives™ on Stack Overflow

Calculate Beginning and Ending Balances Using R

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related