Using loops and equations within dplyr

[eemrun]

Newbie question. I have a dataset that I am using to calculate some accounting values. The dataset looks like this

ID   Period   Amount Interest Opening_Bal         Closing_Bal
1     1            1234         0.07     1234             (1234+(1234*0.07)) = 1321
1     2            1244        0.07      1321             (1321-1234+(1321*0.07))
1     3           .....
2     1            ......
...

Please ignore the actual values in the table. What I have at the moment is the first row for each ID. I need to create a loop for each ID group so that from period 2 onwards, it calculates the closing balance as per the formula in the second row. Any ideas?

[martin.R]

You can use lag() to use the previous row's value:

Compute lagged or leading values — lead-lag

[eemrun]

Thanks for that. I have tried the following:

fin_calc$C_Bal = fin_calc$O_Bal + (fin_calc$O_Bal*0.07)

if (fin_calc$Period==1) {
  fin_calc$Cl_Bal = fin_calc$C_Bal
}

fin_c <- fin_calc %>%
  group_by(ID) %>%
  mutate(Cl_Bal = lag(Cl_Bal) - Amount_PA + (lag(Cl_Bal) - Amount_PA)*0.07)

What this is doing is its able to continue from period 1 to period 2 but then subsequent periods are calculated incorrectly

ID	Amount	Interest	Period	O_Bal	C_Bal	Cl_Bal
1	2946737	0.03	1	30979981.5	33148580.2	NA
1	3035139	0.03	2	0	0	32221382.2
1	3126193	0.03	3	0	0	-3345026.6

  

  Data <- data.frame(
    ID = c(1,1,1,2,2,2,3,3,3),
    Period = c(1,2,3,1,2,3,1,2,3),
    Amount_PA = c(0,2946737,3035139,0,5191191,5346926,0,791430,765900),
    O_Bal = c(30979981.5,0,0,47648869.4,0,0,3055302.0,0,0),
    C_Bal = c(33148580.2,0,0,50984290.3,0,0,3269173.1,0,0),
    Cl_Bal = c(33148580.2,32221382.2,-3345026.6,32221382.,48998616.5,-5721211.3,3269173.,2651185.2,-819513.0)
 
  )
  

[andresrcs]

It would be easier to help you if you provide long enough sample data on a copy/paste friendly format, could you ask this with a minimal REPRoducible EXample (reprex)? A reprex makes it much easier for others to understand your issue and figure out how to help.

If you've never heard of a reprex before, you might want to start by reading this FAQ:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

[eemrun]

Apologies. I have updated. Will this work?

[andresrcs]

It's better, but I think the sample data is not long enough since only has one ID and you are grouping by ID so it's not possible to test the code with it.

[eemrun]

Ok. I have added more data. The last column Cl_Bal is the one I am calculating. The first 2 periods are correct values but then the last period is not correct.