Excel guide · 7 min read
Earned value analysis in Excel
=SUMPRODUCT(Budget,PctComplete), then CPI = EV / AC and SPI = EV / PV. Below 1 means over budget or behind schedule.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Task | Budget | Start | Finish | % complete | Actual cost | Planned % | Status date | ||
| 2 | Discovery | 8000 | Sep 1, 2026 | Sep 11, 2026 | 1 | 8600 | 1 | Oct 30, 2026 | ||
| 3 | Wireframes | 12000 | Sep 14, 2026 | Oct 2, 2026 | 1 | 11200 | 1 | |||
| 4 | Visual design | 15000 | Sep 28, 2026 | Oct 23, 2026 | 0.9 | 16500 | 1 | BAC | 90000 | |
| 5 | Front-end build | 30000 | Oct 19, 2026 | Nov 27, 2026 | 0.35 | 13800 | 0.33333333333 | PV | 47500 | |
| 6 | Content migration | 10000 | Oct 26, 2026 | Nov 20, 2026 | 0.2 | 1500 | 0.25 | EV | 46000 | |
| 7 | Testing | 9000 | Nov 16, 2026 | Dec 4, 2026 | 0 | 0 | 0 | AC | 51600 | |
| 8 | Launch and training | 6000 | Dec 7, 2026 | Dec 11, 2026 | 0 | 0 | 0 |
Download this sheet as an Excel practice file (.xlsx)
The three numbers: PV, EV and AC
The sample is a $90,000 website redesign with seven tasks. Each row has the task's budget, its baseline start and finish, the % complete the team reports, and the actual cost booked so far. The status date in I2 (Friday, October 30) is the date every number is measured at. Keep it in one input cell, so moving to next month's report is a single change.
Budget at completion (BAC) is the whole budget, =SUM(B2:B8) = $90,000. Earned value is what the finished work was budgeted to cost: each task's budget times its % complete, added up. SUMPRODUCT does that in one cell. Visual design is 90% done on a $15,000 budget, so it has earned $13,500, whatever was actually spent on it.
Actual cost is simply =SUM(F2:F8) = $51,600. Planned value needs one more column, which the next section builds: how much of each task should be finished by the status date. The sample keeps the four totals in J4:J7 so the ratios below stay short.
Result: 46000
46000: the budgeted value of the work actually done by October 30, out of $90,000.
Planned value from start and finish dates
If your plan has a time-phased budget (dollars per task per month from the baseline), PV is that budget summed up to the status date. Most spreadsheets only have start and finish dates, so the usual approach is to assume each task spends its budget evenly across its working days. Column G does that: =IF($I$2<C2,0,MIN(1,NETWORKDAYS(C2,$I$2)/NETWORKDAYS(C2,D2))).
Front-end build runs from October 19 to November 27, 30 working days. By the status date it has had 10 of them, so it is planned to be one third done, and its PV is $10,000. The IF returns 0 for tasks that haven't started yet (Testing, Launch), and MIN caps finished tasks at 100%. Add a holiday range as the third argument of both NETWORKDAYS calls if your plan skips public holidays.
Why not plain date subtraction? (I2-C5)/(D5-C5) gives 28.2% for the same task, because it counts weekends and doesn't count the start day. NETWORKDAYS counts both ends, which matches how a task scheduled "Monday to Friday" is five days of work. And keep it as a helper column: MIN and MAX return a single number, so =SUMPRODUCT(B2:B8,MIN(1,(I2-C2:C8)/(D2:D8-C2:C8))) doesn't cap each row, it collapses the whole list.
Result: 47500
47500: by October 30 the plan expected $47,500 of the budget's work to be done.
Variances and indexes: CV, SV, CPI, SPI
With the totals in J5 (PV), J6 (EV) and J7 (AC), the four standard measures are one subtraction or division each:
- Cost variance CV = EV - AC:
=J6-J7= -$5,600. Negative means the work done cost more than it was budgeted at. - Schedule variance SV = EV - PV:
=J6-J5= -$1,500. Negative means less work is done than planned. It is in dollars, not days. - Cost performance index CPI = EV / AC:
=J6/J7= 0.89. Every dollar spent has bought 89 cents of planned work. - Schedule performance index SPI = EV / PV:
=J6/J5= 0.97. The project has done 97% of the work it planned to have done by now.
Result: 0.89147286822 shows as 0.89 with Number format
0.89147: CPI below 1, so the project is getting less work per dollar than budgeted.
Forecast the finish: EAC, ETC, VAC and TCPI
The estimate at completion (EAC) answers "what will this project cost in the end?". The common formula assumes the cost performance so far continues: EAC = BAC / CPI, =J4/(J6/J7) = $100,957. The estimate to complete is what's still to spend, ETC = EAC - AC = $49,357, and the variance at completion is VAC = BAC - EAC = -$10,957, the expected overrun.
Pick the EAC formula that fits why you're over. If the overrun came from a one-off problem that won't repeat (a contractor's rate on Discovery, say), use EAC = AC + (BAC - EV) = $95,600: the remaining work at its budget. If both cost and schedule pressure will continue, EAC = AC + (BAC - EV)/(CPI x SPI) gives $102,566. Show the range rather than one number when you report it.
TCPI (to-complete performance index) turns it around: how efficient the remaining work must be to finish on the original budget. =(J4-J6)/(J4-J7) is $44,000 of work left for $38,400 of budget left, 1.15. A project at CPI 0.89 that needs 1.15 for the rest is very unlikely to land on budget, so it's time to rebaseline or cut scope. Between full EV reports, a weekly project budget burn rate needs only the cost data and shows the date the remaining budget runs out at the current spend.
Result: spills 6 rows × 2 columns
| CPI | 0.89 |
| SPI | 0.97 |
| EAC | 100957 |
| ETC | 49357 |
| VAC | -10957 |
| TCPI | 1.15 |
CPI 0.89, SPI 0.97, EAC 100957, ETC 49357, VAC -10957, TCPI 1.15: a one-cell status summary that updates when the status date or % complete changes.
Find the tasks behind the numbers
A project CPI hides which tasks are the problem. Per task, CPI is budget x % complete / actual cost, but tasks that haven't started have zero actual cost, and dividing by it returns #DIV/0!. Return a label instead with IF, or wrap the division in IFERROR(...,"") when you only need the cell blank.
In the sample, Front-end build is the one to look at: $10,500 earned for $13,800 spent, CPI 0.76, on the biggest task in the plan. Visual design is at 0.82. Content migration shows 1.33, which looks great but usually means costs haven't been booked yet (an unpaid contractor invoice, timesheets not approved). Check high CPIs as hard as low ones.
To list the tasks over budget for the work done, =FILTER(A2:A8,(F2:F8>0)*(B2:B8*E2:E8<F2:F8),"None") returns Discovery, Visual design and Front-end build.
Result: spills 7 rows × 2 columns
| Discovery | 0.93 |
| Wireframes | 1.07 |
| Visual design | 0.82 |
| Front-end build | 0.76 |
| Content migration | 1.33 |
| Testing | not started |
| Launch and training | not started |
Discovery 0.93, Wireframes 1.07, Visual design 0.82, Front-end build 0.76, Content migration 1.33, Testing and Launch "not started".
How % complete is measured changes everything
EV is only as good as the % complete column. People tend to report tasks as 90% done for weeks. Two common rules take the guessing out for short tasks. Under 0/100, a task earns nothing until it's finished: =SUMPRODUCT(B2:B8,--(E2:E8=1)) gives EV of $20,000. Under 50/50, a task earns half its budget when it starts and the rest when it finishes: =SUMPRODUCT(B2:B8,(E2:E8>0)*0.5+(E2:E8=1)*0.5) gives $47,500. Same project, same day, EV anywhere from $20,000 to $47,500, so agree on the rule before the first report.
Also, never average the percentages. =AVERAGE(E2:E8) says the project is 49.3% complete and implies EV of $44,357, because it treats the $6,000 launch task like the $30,000 build. Weighted by budget, =J6/J4, the project is 51.1% complete.
Two more things make the numbers lie. Actual costs must run to the same status date as the % complete: if timesheets are booked to the 30th but invoices only to the 15th, CPI looks better than it is. And SV always ends at zero: at the finish EV equals PV equals BAC, even for a project that finished three months late. Late in a project, use SPI with the Gantt dates, not SV, to judge the schedule.
Result: 20000
20000: under the 0/100 rule only Discovery and Wireframes have earned anything.
Common mistakes
Averaging % complete across tasks
Weight by budget: EV = SUMPRODUCT(budget, % complete), and project % complete = EV / BAC.
Using TODAY() as the status date
PV then changes every day while actual costs are booked monthly. Put a fixed status date in one input cell and point every formula at it.
#DIV/0! in task-level CPI
Tasks with no actual cost yet divide by zero. Use IF(F2=0,"not started",...) or IFERROR(...,"").
Capping planned % with MIN inside SUMPRODUCT
MIN and MAX return one number for the whole range. Work out planned % per row in a helper column, then SUMPRODUCT it with the budget.
FAQ
How do you calculate earned value in Excel?
Multiply each task's budget by its % complete and add them up: =SUMPRODUCT(B2:B8,E2:E8). Keep the % complete as a real percentage (0.35 for 35%), not text.
What is the difference between planned value and earned value?
Planned value is the budget of the work that should be finished by the status date. Earned value is the budget of the work that actually is finished. SV = EV - PV and SPI = EV / PV compare them.
What does a CPI of 0.89 mean?
For every dollar spent, the project has completed 89 cents of budgeted work. If that continues, the final cost is about BAC / 0.89, roughly 12% over budget.
How do I calculate EAC in Excel?
The usual formula is =BAC/CPI. If the overrun so far was a one-off, use =AC+(BAC-EV) instead; if cost and schedule problems will both continue, use =AC+(BAC-EV)/(CPI*SPI).
Functions used
SUMPRODUCTMultiply then addSUMAdds numbersNETWORKDAYSWeekdays between datesMINSmallest numberIFPicks a result from a testIFERRORFallback when a formula errorsAVERAGEMean of numbersFILTERKeeps matching rows, spillsLETNames values inside a formulaHSTACKJoins arrays side by sideVSTACKStacks arrays one under anotherROUNDRounds to digitsDATEBuilds a date
Related guides
- How to calculate budget vs actual variance in Excel
- How to calculate a project budget burn rate in Excel
- How to calculate the percentage of tasks completed (% complete) in Excel
- How to create a Gantt chart in Excel
- How to count workdays between two dates in Excel
- How to calculate schedule adherence in Excel
- How to calculate simple interest in Excel
Updated