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