I built a "Payroll Projection Engine" a Live Web App - using AI what took me hours in Google Sheets

By CA Pushkar Agrawal | The Finance Forge

Most payroll projections live in a spreadsheet that only one person fully understands. You have the formula hidden in column AQ, a tab called "DO NOT TOUCH," and a monthly ritual of copying last month's file and praying nothing breaks.

I have built those spreadsheets. I have maintained those spreadsheets. And I decided there had to be a better way.

So I built a live, interactive payroll projection engine using Python, Streamlit, and AI assistance. It runs in a browser, takes an Excel upload, and produces a 3–36 month workforce cost forecast in seconds. No macros. No locked cells. No "DO NOT TOUCH" tabs.

Try it yourself here: Link

(Please fill your name, email and then click on Payroll Projection Engine)

Why I Built This

I spent years building financial models for the Office of the CFO — budgeting, forecasting, headcount planning. The payroll projection was always where the most assumptions lived:

• Base salaries compound differently depending on each employee's hire anniversary

• New hires get prorated in their first month

• Bonuses might be quarterly, semi-annual, or annual depending on company policy

• Some employees have different raise percentages than the rest of the team

• Part-time staff costs need to scale by FTE — most models forget this

• Inactive employees need to be excluded, but termination timing matters

• Benefits load and payroll taxes add 25–40% on top of gross — and most models miss this

• The CFO wants to see three scenarios before the board meeting on Friday

  
  

Every time I built this in Google Sheets, I was writing deeply nested LAMBDA and MAP formulas that worked, but that nobody else could maintain or extend. The model was brittle. The insights were locked inside a file.

I wanted something a finance team could actually hand to a new analyst and say: here, run this month's projection.

What the App Does

Upload Your Workforce Data

The app accepts any Excel or CSV file with your employee roster. Required columns are Employee ID, Employee Name, Department, Base Salary, Annual Bonus, Hire Date, and Status.

Template.csv

Download CSV • 1KB

Three optional columns unlock additional precision:

• Bonus Type — set to Fixed $ (default) or % of Base. If percentage, the bonus scales automatically when the salary grows due to raises — which is how most compensation frameworks actually work.

• FTE — enter 0.5 for a part-time employee, 1.0 for full-time. All costs scale proportionally.

• Annual Raise % — a per-employee raise that overrides the global sidebar percentage. Useful for high performers or employees on a different pay band.

  
  

If your salary column has dollar signs and commas — the way most finance exports look — the app handles that automatically. Future hires can also be included directly in the file with their planned start date; the model shows $0 until they join and prorates their first month automatically.

The projection engine supports up to 36 months forward from any start date you choose.

Three Increment Models

Most payroll tools apply raises one generic way. This app gives you three:

Anniversary Date — each employee's raise applies in the month of their hire anniversary. Alice joined March 15 — her raise shows up in March's numbers, not April's.

Fiscal Year Start, Full Raise — everyone gets the full raise on the company's fiscal year start date (configurable to January, April, July, or October). New joiners who missed that year's cycle wait for the next one.

Fiscal Year Start, Prorated First Year — same as above, but an employee's very first raise is scaled by the months they worked before the fiscal year start. Joined in July with a January raise cycle? You get 6/12 of the raise in year one, full raise every year after. This is how many mid-market companies actually operate — and most models do not support it at all.

Accurate Salary Projection Logic

The raise logic treats your current base salary as today's salary, then counts only the raise events that fall between today and each projected month, applying compounded raises at each one.

This means if your anniversary is in May and you are projecting April 2026, no raise is applied yet. If you are projecting May 2026, one raise has been applied. If you are projecting May 2027, two raises have compounded.

Proration is also handled correctly. If an employee joins on the 15th of a month, they receive exactly the proportional salary for the days worked, not a full month's pay.

Cost to Company, Not Just Gross Pay

The projection breaks every employee's cost into three components: gross pay (base plus bonus), benefits load (configurable — includes health, dental, vision, retirement/401k match, and other employer-paid benefits), and payroll taxes. The total Cost to Company is what the business actually spends — typically 27–40% higher than the salary line alone.

Industry benchmarks are built into the sidebar: average annual increment of 3.5%, benefits load of 29.8%, payroll taxes of 10.0%. You can adjust all of these for your organization.

Bonus Frequency Control

Not every company pays bonuses quarterly. The app lets you choose between quarterly payouts, semi-annual (June and December), or annual (December only). The math adjusts automatically. And if an employee's bonus is defined as a percentage of base, it recalculates against their projected post-raise salary — so your bonus pool grows correctly when raises are applied.

Planned Headcount Changes

The app handles both planned new hires and planned terminations within the projection window. For new hires, you enter the name, department, salary, bonus, and start date — proration applies automatically. For terminations, you select the employee and effective date — the model stops counting their cost from that month forward.

Alternatively, if your analyst has already included future hires in the Excel file with their planned start dates, the app handles this automatically without needing the in-app form. Either workflow works.

Department View and Budget Variance

The Department View tab shows total Cost to Company broken down by department, with a donut chart, per-employee average cost, and budget variance against entered annual budgets.

The Projection Results tab also shows company-level totals — Total Company Budget, Budget Variance (red if over, green if under), and Budget Utilization percentage. A monthly headcount chart tracks how your active employee count changes across the projection window.

What-If Scenario Analysis

The Simulation tab lets you define three raise scenarios simultaneously — conservative, base case, and aggressive — and run them in parallel. You see the diverging cost lines on a single chart and a table showing the dollar difference between scenarios. This is the exact output needed before a compensation review or board presentation.

Attrition Analysis

You set an annual attrition rate and the model projects headcount reduction and cost savings month by month, assuming natural turnover without backfill.

The model uses a survival probability approach — each month, the workforce cost is multiplied by (1 - monthly rate)^month, giving a smooth, actuarially correct cost curve that shows divergence from day one regardless of team size.

How I Built It

The core logic started as a Google Sheets formula — a deeply nested MAP, LAMBDA, and LET construction that handled proration, anniversary raises, and bonus timing across a dynamic date range. I had built this for a real forecasting model at work.

The first step was translating that logic into Python, function by function. I used Claude as a coding partner throughout — not to write the finance logic, which I already understood, but to help translate it into Python syntax, structure the Streamlit layout, and debug issues as they came up.

Stack: Python, Streamlit, Pandas, Plotly, OpenPyXL — deployed on Streamlit Community Cloud. Entirely free.

What I Learned

Domain knowledge is the hard part. The code is learnable. Understanding how anniversary raises interact with proration, how attrition compounds over time, how CTC differs from gross, how part-time FTE scales costs — that is the knowledge that makes this model useful rather than just functional. Any developer can build a form that takes salary inputs. Very few can build one that gets the payroll math right.

AI makes you faster, not different. I used AI to accelerate writing code I already understood conceptually. The domain expertise is still irreplaceable — without it, AI produces something that looks right but is wrong in subtle ways.

Interactivity changes the conversation. When a CFO can move a slider and see the cost impact of a 5% versus 8% raise in real time, the conversation shifts from "here is what I calculated" to "let us figure this out together." That shift is worth more than any individual chart.

Try It

The app is live and free to use. Upload your own workforce file or use the built-in sample data to explore all features.

Try the Payroll Projection Engine

GitHub: https://github.com/capushkar/Payroll-projection-engine

If you work in finance and want to talk about building tools like this for your team, reach out on LinkedIn or at pushkarkansal12@gmail.com.

Follow The Finance Forge for practical tools and tutorials at thefinanceforge.com.