MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE installment (
acc_name VARCHAR(25),
installment_amt INT,
installment_no INT,
paid_amt INT);

INSERT INTO installment VALUES
('A',100,1,100),
('A',100,2,90),
('A',100,3,80),
('A',100,4,0),
('A',100,5,0),
('B',100,1,100),
('B',100,2,90),
('B',100,3,5),
('B',100,4,0),
('B',100,5,0);
SELECT acc_name,
       installment_amt,
       installment_no,
       paid_amt,
       CASE WHEN paid_amt > 0
            AND paid_amt+nxp >= installment_amt
            AND diff > 0
        THEN 0
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND nxp > 0
        THEN nxp
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND LAG(diff) OVER (PARTITION BY acc_name ORDER BY installment_no) > 0
        THEN LAG(installment_amt-diff) OVER (PARTITION BY acc_name ORDER BY installment_no)
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND diff <0
        THEN ABS(diff)+paid_amt
        ELSE 0
       END balance
  FROM
(SELECT *,
       CASE WHEN paid_amt > 0
             AND paid_amt+nxp >= installment_amt
            THEN paid_amt+nxp-installment_amt 
            WHEN paid_amt > 0
             AND paid_amt+nxp < installment_amt
            THEN (paid_amt+nxp)-installment_amt
          END AS diff
FROM
(SELECT *,
      CASE WHEN paid_amt < installment_amt THEN
                LEAD(paid_amt) OVER (PARTITION BY acc_name ORDER BY installment_no)
           ELSE 0 END nxp
FROM installment) V
) R
     


acc_name
installment_amt
installment_no
paid_amt
balance


A
100
1
100
0

A
100
2
90
0

A
100
3
80
30

A
100
4
0
0

A
100
5
0
0

B
100
1
100
0

B
100
2
90
5

B
100
3
5
100

B
100
4
0
0

B
100
5
0
0


SELECT *,
       CASE WHEN paid_amt > 0
            AND paid_amt+nxp >= installment_amt
            AND diff > 0
        THEN 0
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND nxp > 0
        THEN nxp
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND LAG(diff) OVER (PARTITION BY acc_name ORDER BY installment_no) > 0
        THEN LAG(installment_amt-diff) OVER (PARTITION BY acc_name ORDER BY installment_no)
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND diff <0
        THEN ABS(diff)+paid_amt
        ELSE 0
       END balance
  FROM
(SELECT *,
       CASE WHEN paid_amt > 0
             AND paid_amt+nxp >= installment_amt
            THEN paid_amt+nxp-installment_amt 
            WHEN paid_amt > 0
             AND paid_amt+nxp < installment_amt
            THEN (paid_amt+nxp)-installment_amt
          END AS diff
FROM
(SELECT *,
      CASE WHEN paid_amt < installment_amt THEN
                LEAD(paid_amt) OVER (PARTITION BY acc_name ORDER BY installment_no)
           ELSE 0 END nxp
FROM installment) V
) R
     


acc_name
installment_amt
installment_no
paid_amt
nxp
diff
balance


A
100
1
100
0
0
0

A
100
2
90
80
70
0

A
100
3
80
0
-20
30

A
100
4
0
0
null
0

A
100
5
0
null
null
0

B
100
1
100
0
0
0

B
100
2
90
5
-5
5

B
100
3
5
0
-95
100

B
100
4
0
0
null
0

B
100
5
0
null
null
0

acc_name	installment_amt	installment_no	paid_amt	balance
A	100	1	100	0
A	100	2	90	0
A	100	3	80	30
A	100	4	0	0
A	100	5	0	0
B	100	1	100	0
B	100	2	90	5
B	100	3	5	100
B	100	4	0	0
B	100	5	0	0

acc_name	installment_amt	installment_no	paid_amt	nxp	diff	balance
A	100	1	100	0	0	0
A	100	2	90	80	70	0
A	100	3	80	0	-20	30
A	100	4	0	0	null	0
A	100	5	0	null	null	0
B	100	1	100	0	0	0
B	100	2	90	5	-5	5
B	100	3	5	0	-95	100
B	100	4	0	0	null	0
B	100	5	0	null	null	0

acc_name	installment_amt	installment_no	paid_amt	balance
A	100	1	100	0
A	100	2	90	0
A	100	3	80	30
A	100	4	0	0
A	100	5	0	0
B	100	1	100	0
B	100	2	90	5
B	100	3	5	100
B	100	4	0	0
B	100	5	0	0

acc_name	installment_amt	installment_no	paid_amt	balance
A	100	1	100	0
A	100	2	90	0
A	100	3	80	30
A	100	4	0	0
A	100	5	0	0
B	100	1	100	0
B	100	2	90	5
B	100	3	5	100
B	100	4	0	0
B	100	5	0	0