Im having following data
MonthInt Arrears FutPrin ArrearMonth InCome
23000 0 299000 0 23000
21000 24000 294000 1 21000
20000 48000 290000 2 20000
19000 72000 285000 3 19000
18000 96000 279000 4 18000
17000 120000 272000 5 17000
16000 96000 264000 4 16000
15000 72000 255000 3 15000
14000 120000 245000 5 14000
13000 144000 234000 6 -378000
12000 120000 222000 5 390000
11000 144000 209000 6 -353000
10000 168000 195000 7 0
9000 192000 180000 8 0
8000 120000 164000 5 380000
7000 96000 147000 4 7000
6000 144000 129000 6 -273000
5000 48000 110000 2 278000
4000 24000 90000 1 4000
0 0 0 0 0
I have to arrive income column based on ArrearMonth
Conditions:
1. If ArrearMonth is less than 5 then Income=MonthInt
2. If ArrearMonth changes from less than 5 to greater than 5 then -1*(Arrears + FutPrin)
3. If ArrearMonth changes from greater than 5 to less than 5 then previous Arrears + previous FutPrin + current MonthInt
4. If ArrearMonth retains greater than 5 then 0