Free resource · Excel + ledger formulas
A leave tracker that keeps EL, CL and SL as a real ledger.
Opening + Accrual − Taken + Adjustment = Closing, per leave type, per employee, per month. The same fold-the-ledger formula LekhaHR's own leave system runs on. Download the .xlsx, no email required.
- No base fee
- Per active employee
- GST extra
EL, CL, SL and a Custom column each carry an independent Opening/Accrual/Taken/Adjustment/Closing formula chain.
Real released UI screenshots remain required before public indexing.What's inside
The Ledger tab, EL block shown.
Three months of one employee's EL block shown here. The real file carries the same five columns for CL, SL, a Custom type and an optional Comp-off column, plus a Year View tab.
- ELEarned leave
- CLCasual leave
- SLSick leave
- CustomRename to your policy
Scroll the table sideways to see every column.
| Emp ID | Employee Name | Month | EL Opening | EL Accrual | EL Taken | EL Adjustment | Adjustment reason | EL Closing |
|---|---|---|---|---|---|---|---|---|
| E001 | Rahul Nair | Jan | 5.0 | 1.0 | 0.0 | 0.0 | — | 6.0 |
| E001 | Rahul Nair | Feb | 6.0 | 1.0 | 2.0 | 0.0 | — | 5.0 |
| E001 | Rahul Nair | Mar | 5.0 | 1.0 | 0.0 | -1.5 | Carry-forward trimmed to policy cap | 4.5 |
Sample data. March's −1.5 Adjustment visibly moves the closing balance: without it the month would fold to 6.0, and it lands at 4.5 instead, with the why kept in the Adjustment reason column so the balance never changes silently.
What each column does
- EL Opening
- The prior month's EL Closing rolls forward automatically. Only January's opening balance is a manual starting value.
- EL Accrual
- This period's credit: a manual entry per month, driven by your accrual policy, not a statutory rate this file asserts.
- EL Taken
- Approved leave consumed this month.
- EL Adjustment
- Manual correction, positive or negative. It flows straight into Closing, so a −1.5 entry moves the balance down by 1.5 — never a silent change, because the why sits in the Adjustment reason column.
- Adjustment reason
- The reason behind any Adjustment entry, kept in the shared reason column so a balance movement always carries its explanation.
- EL Closing
- = Opening + Accrual − Taken + Adjustment. The same fold-the-ledger language LekhaHR's product uses for balance = fold(ledger).
Download
Get the .xlsx, no email, no account.
Setup, Ledger and Year View tabs, five blank employee blocks ready to extend by copying the last one.
Column by column
What's inside each tab.
- Setup tab
- Leave-type labels (EL, CL, SL prefilled, a fourth "Custom, rename to your policy" column), the leave-year start month, your carry-forward cap in days, a Comp-off on/off toggle, and the employee roster.
- Ledger tab
- One 12-row block per employee, one row per month, with five columns per leave type: Opening, Accrual, Taken, Adjustment, Closing. Closing folds all four into one balance, the same balance = fold(ledger) language the shipped leave system uses.
- Year View tab
- One row per employee per leave type: the twelve months' closing balances side by side, an annual accrual total, an annual taken total, the balance carried into next year, and a flag if that carry-forward exceeds your cap.
- Comp-off column
- Off by default on the Setup tab. When turned on, it carries its own Opening/Accrual/Taken/Adjustment/Closing block, kept separate from EL/CL/SL since comp-off credits come from worked days rather than a periodic accrual.
How to use it
From setup to a closing balance.
- Set up leave types and roster. Rename EL/CL/SL/Custom on the Setup tab to your policy, set the carry-forward cap and list employees.
- Enter opening balances. Only January needs a manual Opening value per employee, per type; every later month rolls forward from the prior Closing.
- Log accrual, taken and adjustments each month. Adjustments carry a reason in the shared reason column instead of silently editing a balance.
- Read Closing and the Year View. Closing recalculates live; the Year View totals the year and flags anything over your cap.
Honest limits
When the spreadsheet breaks.
Three things tend to break a flat ledger first: mid-year joiner proration (a partial first year needs a manual accrual adjustment, not a formula this sheet derives on its own), the sandwich rule around weekends (whether a Friday-and-Monday request also counts the intervening Saturday and Sunday is a policy decision the ledger can't apply automatically), and an approval trail per request (this sheet records the numbers, not who approved which row or why).
Once the Adjustment column becomes the main way a balance changes rather than the exception, that's the signal to move to a system where every ledger movement (accrual, consumption, carry-forward, comp-off credit, manual adjustment) is an entry with its own reason attached, which is what LekhaHR's leave management system keeps as the balance's audit trail.
FAQ
Questions before you download.
How do I create a leave record in Excel?
Download the template, list your roster on the Setup tab, then use the Ledger tab: one 12-row block per employee, one row per month. Each row's Closing balance formula is Opening + Accrual − Taken + Adjustment, and January's opening is the only manual starting value; every later month rolls forward automatically.
How do I make a leave balance sheet in Excel?
The formula is the same one LekhaHR's own leave ledger uses: Opening + Accrual (this period) − Taken + Adjustment = Closing. Each leave type gets its own five-column block, so EL, CL, SL and your custom type each carry an independent balance.
What is the leave record summary or year view?
The Year View tab shows each employee's month-by-month closing balance per leave type, an annual accrual total, an annual taken total, and the balance carried forward into the next leave year, with a flag cell if that carry-forward exceeds your configured cap.
What's the format for leave types in this template?
EL, CL and SL ship as prefilled labels plus a fourth "Custom, rename to your policy" column. These are configurable labels, not a statutory entitlement count; rename any of them to match your company's actual policy.
Does this track statutory leave entitlements?
No. It's a ledger you configure to your own policy. Accrual rates, leave-type labels and the carry-forward cap are all inputs you set on the Setup tab, not numbers this file asserts on its own.
What about leave encashment at year end?
Not computed here. The Year View tab flags a carry-forward-cap line only, for example "2.0 days over cap, check your policy for encashment/lapse treatment," with no payout figure. Encashment valuation is a payroll calculation this sheet deliberately leaves out.
When does a spreadsheet stop working for leave tracking?
Mid-year joiner proration, the sandwich rule around weekends, and an approval trail per request are the three things a flat ledger can't hold without manual re-entry. Once those come up often enough that the Adjustment column becomes the main way balances change, that's the signal to move to a system that keeps the request and its reason attached to the ledger row.