Using loops and equations within dplyr

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?

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

1 Like

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)
 
  )
  

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:

1 Like

Apologies. I have updated. Will this work?

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.

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.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.