Build an earned leave ledger with accrual and carry-forward cap

Why a ledger and not a formula

Leave queries reach HR as "why is my balance 12 and not 13.5?" A single formula such as months × 1.5 − leave taken gives a number but not an answer. A month-by-month ledger shows exactly when leave was credited, when it was used and where the difference arose. It also makes mid-year joiners, policy changes and corrections straightforward, because each is an entry in a particular month.

Write the policy down first

The rules below are a synthetic example, not a statutory rule. Leave entitlements differ by company and, for some establishments, are governed by state shops and establishments laws or factory rules, so confirm yours with HR before building anything.

The ledger

Three synthetic employees cover the usual cases: a long-serving employee who starts FY 2025-26 with 28.5 days, one who joined on 20 September 2025, after the cut-off, and one who joined on 3 November 2025 and took leave early.

import pandas as pd

# Synthetic company policy (not a statutory rule):
ACCRUAL_PER_MONTH = 1.5        # earned leave credited at each month-end of service
CARRY_CAP = 30.0               # maximum carried into the new financial year on 1 April
JOIN_RULE_DAY = 15             # joined on or before the 15th: that month accrues

staff = pd.DataFrame({
    "emp": ["E101", "E102", "E103"],
    "joined": pd.to_datetime(["2019-06-10", "2025-09-20", "2025-11-03"]),
    "opening_1apr": [28.5, 0.0, 0.0],          # carried in from FY 2024-25
})
taken = pd.DataFrame({                         # approved leave, in days (half days allowed)
    "emp": ["E101", "E101", "E101", "E102", "E103", "E103"],
    "month": ["2025-05", "2025-10", "2026-02", "2025-12", "2025-11", "2026-01"],
    "days": [3.0, 5.0, 2.5, 1.0, 2.0, 3.0],
})

months = pd.period_range("2025-04", "2026-03", freq="M")      # FY 2025-26
rows = []
for s in staff.itertuples():
    first = s.joined.to_period("M") + (0 if s.joined.day <= JOIN_RULE_DAY else 1)
    bal = s.opening_1apr
    for m in months:
        credit = ACCRUAL_PER_MONTH if m >= first else 0.0
        used = taken.loc[(taken["emp"] == s.emp) & (taken["month"] == str(m)), "days"].sum()
        bal = bal + credit - used
        rows.append((s.emp, str(m), credit, used, bal))
ledger = pd.DataFrame(rows, columns=["emp", "month", "credit", "taken", "closing"])

# Negative balances: leave approved before it was earned. Flag, do not hide.
print(ledger[ledger["closing"] < 0].to_string(index=False))

# Year-end: carry forward up to the cap; the rest lapses (or is encashed, per policy).
ye = ledger[ledger["month"] == "2026-03"].set_index("emp")["closing"]
year_end = pd.DataFrame({"closing_31mar": ye,
                         "carried_1apr": ye.clip(lower=None, upper=CARRY_CAP),
                         "lapsed": (ye - CARRY_CAP).clip(lower=0)})
print(year_end.to_string())

# Control: opening + credits - taken = closing, for every employee.
chk = ledger.groupby("emp").agg(credit=("credit", "sum"), taken=("taken", "sum"))
chk["opening"] = staff.set_index("emp")["opening_1apr"]
assert ((chk["opening"] + chk["credit"] - chk["taken"]) == ye).all()
assert year_end.loc["E101", "lapsed"] == 36.0 - 30.0

What the output shows

E102 joined on the 20th, after the cut-off, so accrual starts in October rather than September. Six months of credit is 9 days, less 1 day taken, leaving 8.

E103 took 2 days in November with only 1.5 credited, and 3 days in January against a balance of 2.5, so the ledger goes to −0.5 twice. The script lists both months. Whether that is an error depends on the policy: some companies permit advance leave for new joiners, others would record the extra half day as loss of pay. Either way, a person should see it before payroll runs.

E101 reaches 36 days at 31 March. Thirty carry into FY 2026-27 and 6 are recorded as lapsed. Keep that lapsed column. If the policy is encashment instead, the same figure is the input to that calculation, and if an employee disputes the lapse later, you can show exactly how it arose.

The final assert is a control total for the whole year: for every employee, opening balance plus all credits minus all leave taken must equal the closing balance at 31 March. It catches a missed month, a duplicated leave entry or a join that doubled rows.

The month-by-month ledger in a workbook

The same ledger works in a flat table with one row per employee per month: columns for employee, month, credit, taken and closing. With rows sorted by employee and then month, and the opening balance in the first row of each employee:

Credit   =IF(EOMONTH(B2,0) >= EOMONTH(Staff_Join, IF(DAY(Staff_Join)>15, 1, 0)), 1.5, 0)
Closing  =IF(A2<>A1, Opening, F1) + D2 - E2
Flag     =IF(F2<0, "NEGATIVE", "")
Carry    =MIN(F_March, 30)
Lapsed   =MAX(F_March - 30, 0)

In the Credit formula, Staff_Join is the joining date looked up for that employee. The second argument of EOMONTH moves the first credited month forward by one when the join is after the 15th; test it on joins on the 15th, the 16th and the last day of a month before relying on it. Leave taken comes from the approved leave register with SUMIFS on employee and month, never typed into the ledger by hand.

Details that cause most disputes

Half days. Keep leave in decimal days throughout and display with one decimal place. A balance shown as a whole number hides a half day.

Leave spanning months. A leave from 29 September to 3 October is two separate months in the ledger. Split it by date before summing; the working-day calendar in count working days with 2nd and 4th Saturdays off tells you which of those dates are actually leave days.

Exits. For an employee who leaves mid-year, stop credits at the last working month under the policy and take the closing balance as the input to the final settlement.

Policy changes. If the accrual rate changes from 1 October, store the rate by month in a table rather than editing the constant, so earlier months keep their original credit.

Once the ledger is correct, loss-of-pay days feed from it into payroll, as in calculate loss-of-pay days from an attendance sheet. HR data work of this kind, where every number must be explainable to the employee it belongs to, is part of the practical projects in our Data Analyst course.