Cellwise Start free

Excel guide · 7 min read

How to calculate a project budget burn rate in Excel

Quick answerBurn rate = actual spend per week. Take the average of recent weeks, =AVERAGE(TAKE(FILTER(D2:D17,D2:D17<>""),-4)) in Excel 365, then weeks of budget left = (budget - spent) / burn rate, and the run-out date = status date + weeks left x 7. On the sample project, $28,000 left at $10,875 a week lasts 2.6 weeks: the budget runs out on November 17, 24 days before the planned finish.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHI
1WeekWeek endingPlannedActualCum. plannedCum. actualBudget120000
21Aug 28, 20264000380040003800Status dateOct 30, 2026
32Sep 4, 2026600064001000010200Planned finishDec 11, 2026
43Sep 11, 2026800089001800019100% complete0.62
54Sep 18, 2026800092002600028300
65Sep 25, 2026900098003500038100Burn (last 4 wks)10875
76Oct 2, 20269000104004400048500Weeks left2.5747126437
87Oct 9, 2026900096005300058100Runs outNov 17, 2026
98Oct 16, 20269000112006200069300
109Oct 23, 20269000109007100080200
1110Oct 30, 20269000118008000092000
1211Nov 6, 2026800088000
1312Nov 13, 2026800096000
1413Nov 20, 20267000103000
1514Nov 27, 20266000109000
1615Dec 4, 20266000115000
1716Dec 11, 20265000120000

Download this sheet as an Excel practice file (.xlsx)

Set up the weekly log and cumulative spend

The sample is a 16-week CRM rollout with a $120,000 budget (I1). Each row is one week, ending on a Friday: the planned spend from the baseline in C and the actual spend booked that week in D. Actuals are filled for the first 10 weeks, up to the status date in I2 (October 30). Keep the budget, status date and planned finish in input cells, so next week's report is one date change.

If your costs come from a ledger or timesheet export instead of a weekly total, build column D with SUMIFS on the dates: =SUMIFS(Costs!C:C,Costs!A:A,">"&B2-7,Costs!A:A,"<="&B2) adds everything dated in the seven days up to that Friday. Include committed costs (a signed purchase order) only if you report them the same way every week.

Cumulative spend is a running total: in F2, =IF(D2="","",SUM($D$2:D2)), filled down. The IF matters. A plain =SUM($D$2:D2) keeps repeating 92,000 in every future week, so a chart of cumulative actual vs cumulative planned shows a flat line to week 16 instead of stopping at today. Column E does the same for the plan, with no IF because every week has a planned figure.

=SUM(D2:D17)

Result: 92000

92,000 spent in the first 10 weeks, out of the $120,000 budget.

Burn rate: average the recent weeks, not all of them

The burn rate is how much the project spends per week. The simple version is the average of every actual week, =AVERAGE(D2:D17), which gives 9,200 (AVERAGE skips the blank future weeks). But spending on this project has climbed from about $4,000 a week while the team was small to over $11,000 now, and the early cheap weeks drag the average down.

Use the last 4 weeks instead. In Excel 365, FILTER keeps only the weeks with an actual and TAKE with -4 returns the last four of them, so the formula moves on by itself when you add week 11. Don't skip the FILTER: TAKE(D2:D17,-4) on its own takes the last four rows of the range, which are the empty weeks 13 to 16.

In Excel 2019 and older, =AVERAGE(OFFSET(D2,COUNT(D2:D17)-4,0,4,1)) does the same job: COUNT finds 10 filled weeks, so OFFSET points at D8:D11, the same four weeks. OFFSET is volatile (it recalculates on every change), which is fine on a sheet this size. Both versions assume the actuals are filled from the top with no gaps; enter 0 for a week with no spend rather than leaving it blank. For other windows, see sum the last N values and moving averages.

=AVERAGE(TAKE(FILTER(D2:D17,D2:D17<>""),-4))

Result: 10875

10,875 a week: the average of weeks 7 to 10 (9,600, 11,200, 10,900 and 11,800).

Weeks of budget left and the run-out date

Budget remaining divided by the burn rate is how many weeks the money lasts: (120,000 - 92,000) / 10,875 = 2.57 weeks. I7 holds that, using the recent burn in I6. With the all-weeks average it would say 3.04 weeks, which is why the choice of window matters more than the formula.

Turn weeks into a date by adding weeks x 7 days to the status date. ROUNDDOWN keeps it on a whole day and errs on the early side: I8 is =I2+ROUNDDOWN(I7*7,0). Format the cell as a date if it shows a number like 46343. Then compare it with the planned finish in I3: =I3-I8 is 24 days. At the current burn, the budget is gone three and a half weeks before the work is due to finish.

Guard the division for a project that has spent nothing yet: =IF(I6<=0,"No spend yet",(I1-SUM(D2:D17))/I6). And once the remaining budget goes negative, the weeks-left figure turns negative too; show "Over budget" instead with another IF.

=I2+ROUNDDOWN(I7*7,0)

Result: Nov 17, 2026 shows as Nov 17, 2026 with the custom mmm d, yyyy format

The budget runs out on November 17, 2026, against a planned finish of December 11.

Planned vs actual burn, side by side

A burn rate means more next to the plan. Look up both cumulative columns on the status date with XLOOKUP: =XLOOKUP(I2,B2:B17,E2:E17) returns 80,000 planned by October 30, and the same with F2:F17 returns 92,000 actual. The project has spent $12,000 (15%) more than the plan expected by now. Chart columns E and F as two lines (Insert > Line chart) and the gap is the first thing anyone sees.

Then ask what the rest of the project needs. The plan still has 40,000 of work in weeks 11 to 16, =SUMIFS(C2:C17,B2:B17,">"&I2), but only 28,000 of budget is left. Dividing what is left by the weeks left gives the burn rate that would land exactly on budget: 28,000 / 6 = 4,666.67 a week, less than half the current 10,875. That is the number to put in front of the sponsor, together with a choice: more budget, less scope or a cheaper way to do the remaining work.

For the week-by-week view, a variance column G with =D2-C2 shows which weeks overspent. Every week since week 1 has run over plan, and weeks 8 to 10 by $1,900 to $2,800 each. The budget vs actual variance guide covers variance % and favourable/unfavourable flags.

=(I1-SUM(D2:D17))/COUNTIFS(B2:B17,">"&I2)

Result: 4666.6666667

About 4,666.67 a week: the burn rate that spends exactly the remaining $28,000 over the 6 weeks left.

Why burn rate alone says nothing about progress

A project can burn exactly to plan and still be in trouble, because the burn rate counts money, not finished work. Here the team reports the work as 62% complete (I4) while 76.7% of the budget is spent. Spending is running well ahead of progress.

That is what earned value analysis measures. Earned value is budget x % complete, 120,000 x 62% = 74,400, and the cost performance index is earned value / actual cost: 74,400 / 92,000 = 0.81. Each dollar spent has bought 81 cents of planned work. If that continues, the estimate at completion is budget / CPI = $148,387, about $28,000 over.

Use the two together. The burn rate and run-out date are quick, need only the cost data, and tell you when the money stops. Earned value needs an honest % complete, and tells you whether the money is buying the progress it should. For a company's cash rather than one project's budget, the same idea is cash runway: cash in the bank divided by monthly net burn.

=HSTACK(SUM(D2:D17)/I1,I4,I1*I4/SUM(D2:D17))

Result: spills 1 row × 3 columns

0.766666666670.620.80869565217

0.767, 0.62 and 0.809: 76.7% of the budget spent, 62% of the work done, a CPI of 0.81.

Common mistakes

Averaging every week since kickoff

Early weeks are usually cheap, so the all-weeks average (9,200 here) understates today's burn (10,875) and makes the budget look like it lasts longer. Average the last 4 weeks, and say which window you used.

Letting the cumulative column repeat into future weeks

=SUM($D$2:D2) filled to week 16 shows the last total in every empty week and flattens the chart. Wrap it in IF(D2="","",...).

Taking the last 4 rows instead of the last 4 actuals

TAKE(D2:D17,-4) returns the empty future weeks. FILTER out the blanks first, or use OFFSET with COUNT in older Excel.

Reporting burn rate as progress

Spending to plan says nothing about work done. Show % of budget spent next to % complete, or run earned value.

FAQ

How do you calculate burn rate on a project?

Divide the actual spend by the number of weeks (or months) it covers. For a forecast, use recent periods: =AVERAGE(TAKE(FILTER(D2:D17,D2:D17<>""),-4)) averages the last four weeks with an actual in Excel 365.

How do I calculate how many weeks of budget are left?

Budget remaining / burn rate: =(budget - SUM(actuals)) / burn. Multiply by 7 and add it to the status date to get the date the budget runs out, then compare it with the planned finish date.

What is the difference between burn rate and earned value?

Burn rate is how fast the money is spent. Earned value compares that spending with the work actually finished (budget x % complete), so it shows whether the project is getting value for the money. You can burn exactly to plan and still be behind.

Should burn rate be weekly or monthly?

Weekly for projects shorter than about six months, because a month is too coarse to react in time. Monthly works for long programs and for company cash runway. Keep the budget, the actuals and the plan in the same period.

How do I make a burn chart in Excel?

Select the week-ending dates and the cumulative planned and cumulative actual columns, then Insert > Line chart. Use IF(actual="","",running total) for the actual column so its line stops at the status date. A horizontal line at the budget amount shows where the money runs out.

Functions used

Related guides

Updated