Earned value management in a spreadsheet: SPI, CPI and EAC
Calculate planned value, earned value and actual cost in a spreadsheet, then SPI, CPI and EAC, with a five-package example and notes on reading the results.
Earned value management reduces a project's status to a few numbers and two ratios. The schedule performance index (SPI) is earned value divided by planned value. The cost performance index (CPI) is earned value divided by actual cost. A result of 1.00 means the project is on plan. Below 1.00 means behind schedule or over budget, and above 1.00 means ahead or under. Divide the total budget by CPI and you get a forecast of the final cost, called the estimate at completion (EAC). A spreadsheet can produce all of these from one table with a row per work package.
The sections below define each term, give the formulas, work through a five-package example with reproducible figures, and end with the limits, which matter as much as the arithmetic.
What are the four base values in earned value management?
Every calculation starts from four amounts at one status date. The terms follow the Project Management Body of Knowledge (PMBOK Guide), published by the Project Management Institute.
- BAC (budget at completion): the approved budget for all the work in scope.
- PV (planned value): the budgeted cost of the work scheduled to be finished by the status date.
- EV (earned value): the budgeted cost of the work actually finished by the status date. For each work package, EV = BAC × percent complete.
- AC (actual cost): what has been spent on the work performed up to the status date.
Earned value is measured in budget dollars, not in actual dollars. A work package budgeted at $40,000 that is 100% complete has an EV of $40,000 whether it cost $38,000 or $42,500. The gap between EV and AC is the cost story, and the gap between EV and PV is the schedule story.
How do you calculate variances and indices in earned value management?
| Measure | Formula | Reading |
|---|---|---|
| Schedule variance (SV) | =EV-PV |
Negative means behind plan, in currency |
| Cost variance (CV) | =EV-AC |
Negative means over budget, in currency |
| SPI | =EV/PV |
Below 1.00 means behind plan |
| CPI | =EV/AC |
Below 1.00 means each dollar spent has earned less than a dollar of budgeted work |
The variances are in currency and show the size of the problem. The indices are ratios and show the rate, which is easier to compare across projects of different sizes.
How do you forecast the final cost (EAC) in earned value management?
EAC is a forecast of the total cost at completion. Three common forms differ in what they assume about the remaining work:
| Form | Formula | Assumes |
|---|---|---|
| Current cost efficiency continues | =BAC/CPI |
The remaining work will cost what the completed work has cost per budget dollar |
| Remaining work at budget | =AC+(BAC-EV) |
Past variances were one-off, and the rest of the work will be done at budget |
| Cost and schedule both matter | =AC+(BAC-EV)/(CPI*SPI) |
Remaining work is affected by both the cost and the schedule performance |
Two companion measures follow from EAC:
- ETC (estimate to complete) = EAC − AC, the money still needed.
- VAC (variance at completion) = BAC − EAC. A negative VAC is an expected overrun.
The to-complete performance index (TCPI) shows the efficiency the remaining work must reach to finish on budget: =(BAC-EV)/(BAC-AC). If TCPI is well above the current CPI, the budget target is unlikely without a change in scope or resources. A version based on the forecast, =(BAC-EV)/(EAC-AC), answers the question of what efficiency the forecast implies.
How do you spread planned value over time in earned value?
PV needs a schedule. The simplest method spreads each work package's BAC evenly across the days between its start and finish. The planned fraction on the status date is the elapsed share of the duration. Clamp it between 0 and 1, so that work not yet started has zero planned value and work past its finish has its full budget:
=BAC*MAX(0,MIN(1,(StatusDate-Start)/(Finish-Start)))
Put the status date in B2. Start the table in row 5 with columns for the work package (A), BAC (B), start (C), finish (D), percent complete (E), actual cost (F), PV (G), EV (H), SV (I), CV (J), SPI (K) and CPI (L). The row 5 formulas are:
G5:=B5*MAX(0,MIN(1,($B$2-C5)/(D5-C5)))H5:=B5*E5I5:=H5-G5J5:=H5-F5K5:=IF(G5=0,"n/a",H5/G5)L5:=IF(F5=0,"n/a",H5/F5)
Fill the formulas down, then total the money columns. The project SPI is the total EV divided by the total PV, not an average of the package SPIs, because an average ignores the size of each package.
What does a worked earned value example with five packages show?
The status date is 30 June 2026. The project has five work packages, and all amounts are in dollars.
Only package 3 is in progress on that date. It runs from 1 May to 31 August 2026, a span of 122 days. By 30 June, 60 days have passed, so its planned fraction is 60 / 122 = 0.4918. Its PV is $150,000 × 0.4918 = $73,770.49. The team reports it 45% complete, which gives an EV of $150,000 × 0.45 = $67,500. Its actual cost is $80,000.
Packages 1 and 2 finished before the status date, so their planned fraction is 100%. Packages 4 and 5 have not started, so their PV and EV are zero.
| Work package | BAC | Start | Finish | Planned % | PV | Complete | EV | AC |
|---|---|---|---|---|---|---|---|---|
| 1. Requirements | 40,000 | 1 Mar 2026 | 30 Apr 2026 | 100.0% | 40,000.00 | 100% | 40,000.00 | 42,500.00 |
| 2. Design | 90,000 | 1 Apr 2026 | 15 Jun 2026 | 100.0% | 90,000.00 | 100% | 90,000.00 | 88,000.00 |
| 3. Build | 150,000 | 1 May 2026 | 31 Aug 2026 | 49.2% | 73,770.49 | 45% | 67,500.00 | 80,000.00 |
| 4. Test | 60,000 | 1 Jul 2026 | 15 Aug 2026 | 0.0% | 0.00 | 0% | 0.00 | 0.00 |
| 5. Training | 30,000 | 1 Aug 2026 | 30 Sep 2026 | 0.0% | 0.00 | 0% | 0.00 | 0.00 |
| Total | 370,000 | 203,770.49 | 197,500.00 | 210,500.00 |
The variances and indices for the same packages:
| Work package | SV | CV | SPI | CPI |
|---|---|---|---|---|
| 1. Requirements | 0.00 | -2,500.00 | 1.000 | 0.941 |
| 2. Design | 0.00 | 2,000.00 | 1.000 | 1.023 |
| 3. Build | -6,270.49 | -12,500.00 | 0.915 | 0.844 |
| 4. Test | 0.00 | 0.00 | n/a | n/a |
| 5. Training | 0.00 | 0.00 | n/a | n/a |
| Total | -6,270.49 | -13,000.00 | 0.9692 | 0.9382 |
The project SPI of 0.9692 means it has earned about 97 cents of budgeted work for each dollar planned by this date. The CPI of 0.9382 means each dollar spent has earned about 94 cents of budgeted work. Package 2 is the only one under cost. Package 3 is behind on both measures.
The three EAC forms give different answers:
| Forecast | Calculation | EAC | VAC |
|---|---|---|---|
| Cost efficiency continues | 370,000.00 / 0.9382 | 394,354.43 | -24,354.43 |
| Remaining work at budget | 210,500.00 + (370,000.00 − 197,500.00) | 383,000.00 | -13,000.00 |
| Cost and schedule both matter | 210,500.00 + 172,500.00 / (0.9382 × 0.9692) | 400,191.68 | -30,191.68 |
The forecasts span $383,000 to $400,192. That spread is the uncertainty in the forecast itself, and it tells you which assumption the team has to defend. The TCPI for the budget is 172,500 / 159,500 = 1.0815, so the remaining work must earn about 1.08 dollars of budget per dollar spent to finish at $370,000. The current CPI is 0.94.
Keep the ratios unrounded in the formulas. The EAC values above come from the unrounded CPI and SPI. Typing the four-decimal indices by hand moves the first EAC by $18 and the third by $14.
How should you read SPI and CPI together?
The two indices describe different problems, so read them as a pair.
- SPI below 1.00 and CPI below 1.00: behind schedule and over budget. The EAC shows how far the cost is likely to move.
- SPI below 1.00 and CPI at or above 1.00: behind but spending efficiently. Catching up may require spending more, which the EAC will not show until it happens.
- SPI at or above 1.00 and CPI below 1.00: ahead of plan but over budget. Check whether the work was pulled forward with extra resources, or whether the percent complete is overstated.
- SPI and CPI both at or above 1.00: on or ahead of plan and within budget. Still verify the percent complete figures, because the same measurement errors apply.
What are the limits of earned value management?
SPI reaches 1.00 near the end, even for late projects. SPI is measured in money. As the project nears completion, PV approaches BAC, and if EV keeps pace, SPI approaches 1.00 whether or not the finish date slips. Track the critical path and milestone dates separately to see end-of-project delay.
Percent complete must be measured. EV is only as good as the percent complete it multiplies. A package that is really 60% complete but reported at 80% adds 20% of its BAC to EV and hides the cost overrun behind it. Use measured quantities, completed milestones or inspected deliverables, not a feeling about how far along the work is.
Small packages need a rule. For packages shorter than a reporting period, pick one of two conventions and apply it throughout:
- 0/100 rule: the package earns no EV until it is finished, then 100%. This avoids guessing partial progress.
- 50/50 rule: the package earns 50% when it starts and the other 50% when it finishes. This gives some credit for early work without an estimate of progress.
Indices are unstable early on. Early in a project, the denominators are small, so a few dollars of overrun can move CPI a lot. Treat the first few reporting periods as a trend to watch, not a forecast.
Linear phasing is a simplification. If the approved plan spends most of a package's budget near its end, the linear PV is too high in the middle of the package and too low near the finish, which distorts SPI at every status date. Replace the formula with the baseline phasing wherever it exists.
How should risks that EAC does not capture be tracked?
The EAC forecasts from past performance. Risks that could push the cost beyond it, such as a supplier delay or an unapproved change in scope, belong in a risk register with a probability and an impact for each. The risk register template is one place to keep them.
For the formulas on their own, with a shorter worked example, see how to calculate SPI and CPI. The table described above can be built in any spreadsheet, and the earned value management tracker is a ready-made workbook for the same calculations. The same inputs-calculations-outputs layout applies to most project models, as described in spreadsheet modeling: inputs, calculations, outputs.