Using loops and equations within dplyr

dplyr
#1

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?

0 Likes

#2

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

1 Like

#3

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

0 Likes

#4

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

#5

Apologies. I have updated. Will this work?

0 Likes

#6

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.

0 Likes

#7

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.

0 Likes

closed #8

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.

0 Likes