PF AUTO CALCULATION

[baidya91]

�Please help me out to solve the problem as stated below:
1. I want to prepare an automated pf calculation excel sheet.
2. No. of deposit in any month can be maximum 2 as shown in col C & D
3. If any deposit made within first 15 days of the month, it will be placed in the respective row of the column I with respect to challan number and date in column J & K which will be filled automatically with respect to challan and date in column E & F
4.. If any deposit made after first 15 days of the month it will be placed in the respective row of column L with respct to challan number and date in column M & N which will be automatically filled with respect to challan and date in column G & H

Month Of Deposit

Opening Balance

Monthly Deposit

Deposited In Treasury within first 15 days of month

Deposited In Treasury after first 15 days of the month

Deposited Upto 15th Day

Deposited In Treasury

Deposited Between 16th and Last Day

Deposited In Treasury

Monthly lowest balance

Interest For The Month

Closing Balance

ROI

Challan no

Date

Challan no

Date

Employee's share

Challan no

Date

Employee's Share

Challan no

Date

Apr-19​

500123​

11000​

2202/14

05-04-19

11000​

2202/14

05-04-19

0​

511123​

3407.49​

511123​

8.0%

May-19​

511123​

11000​

2202/40

27-04-19​

0​

11000​

2202/40

27-04-19​

511123​

3407.49​

522123​

8.0%

Jun-19​

522123​

11000​

2202/29

28-05-19​

0​

11000​

2202/29

28-05-19​

522123​

3480.82​

533123​

8.0%

Jul-19​

533123​

11000​

2202/36

28-06-19​

0​

11000​

2202/36

28-06-19​

533123​

3509.73​

544123​

7.9%

Aug-19​

544123​

11000​

2202/6

05-08-19

11000​

2202/6

05-08-19

0​

555123​

3654.56​

555123​

7.9%

Sep-19​

555123​

11000​

2202/42

28-08-19​

0​

11000​

2202/42

28-08-19​

555123​

3654.56​

566123​

7.9%

Oct-19​

566123​

11000​

2202/57

29-09-19​

0​

11000​

2202/57

29-09-19​

566123​

3726.98​

577123​

7.9%

Nov-19​

577123​

11000​

2202/35

24-10-19​

0​

11000​

2202/35

24-10-19​

577123​

3799.39​

588123​

7.9%

Dec-19​

588123​

11000​

2202/44

28-11-19​

0​

11000​

2202/44

28-11-19​

588123​

3871.81​

599123​

7.9%

Jan-20​

599123​

11000​

2202/45

29-12-19​

0​

11000​

2202/45

29-12-19​

599123​

3944.23​

610123​

7.9%

Feb-20​

610123​

11000​

2202/42

31-01-20​

0​

11000​

2202/42

31-01-20​

610123​

4016.64​

621123​

7.9%

Mar-20​

621123​

11000​

12000​

2202/05

02-03-20

2202/05

26-03-20​

11000​

2202/05

02-03-20

12000​

2202/05

26-03-20​

632123​

4161.48​

644123​

7.9%

Total

132000​

12000​

33000​

0​

111000​

44635.16​

 

[baidya91]

Help please. Thanks in advance........