Cellwise Start free

Excel guide · 7 min read

Earned value analysis in Excel

Quick answerEarned value compares three numbers at a status date: planned value (PV, the budget of the work that should be done by then), earned value (EV, budget x % complete) and actual cost (AC). In Excel, EV is =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.

RowABCDEFGHIJ
1TaskBudgetStartFinish% completeActual costPlanned %Status date
2Discovery8000Sep 1, 2026Sep 11, 2026186001Oct 30, 2026
3Wireframes12000Sep 14, 2026Oct 2, 20261112001
4Visual design15000Sep 28, 2026Oct 23, 20260.9165001BAC90000
5Front-end build30000Oct 19, 2026Nov 27, 20260.35138000.33333333333PV47500
6Content migration10000Oct 26, 2026Nov 20, 20260.215000.25EV46000
7Testing9000Nov 16, 2026Dec 4, 2026000AC51600
8Launch and training6000Dec 7, 2026Dec 11, 2026000

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.

=SUMPRODUCT(B2:B8,E2:E8)

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.

=SUMPRODUCT(B2:B8,G2:G8)

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.
=J6/J7

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.

=LET(cpi,J6/J7,spi,J6/J5,eac,J4/cpi,VSTACK(HSTACK("CPI",ROUND(cpi,2)),HSTACK("SPI",ROUND(spi,2)),HSTACK("EAC",ROUND(eac,0)),HSTACK("ETC",ROUND(eac-J7,0)),HSTACK("VAC",ROUND(J4-eac,0)),HSTACK("TCPI",ROUND((J4-J6)/(J4-J7),2))))

Result: spills 6 rows × 2 columns

CPI0.89
SPI0.97
EAC100957
ETC49357
VAC-10957
TCPI1.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.

=HSTACK(A2:A8,IF(F2:F8=0,"not started",ROUND(B2:B8*E2:E8/F2:F8,2)))

Result: spills 7 rows × 2 columns

Discovery0.93
Wireframes1.07
Visual design0.82
Front-end build0.76
Content migration1.33
Testingnot started
Launch and trainingnot 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.

=SUMPRODUCT(B2:B8,--(E2:E8=1))

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

Related guides

Updated