Excel guide · 7 min read
How to calculate a project budget burn rate in Excel
=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.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Week | Week ending | Planned | Actual | Cum. planned | Cum. actual | Budget | 120000 | |
| 2 | 1 | Aug 28, 2026 | 4000 | 3800 | 4000 | 3800 | Status date | Oct 30, 2026 | |
| 3 | 2 | Sep 4, 2026 | 6000 | 6400 | 10000 | 10200 | Planned finish | Dec 11, 2026 | |
| 4 | 3 | Sep 11, 2026 | 8000 | 8900 | 18000 | 19100 | % complete | 0.62 | |
| 5 | 4 | Sep 18, 2026 | 8000 | 9200 | 26000 | 28300 | |||
| 6 | 5 | Sep 25, 2026 | 9000 | 9800 | 35000 | 38100 | Burn (last 4 wks) | 10875 | |
| 7 | 6 | Oct 2, 2026 | 9000 | 10400 | 44000 | 48500 | Weeks left | 2.5747126437 | |
| 8 | 7 | Oct 9, 2026 | 9000 | 9600 | 53000 | 58100 | Runs out | Nov 17, 2026 | |
| 9 | 8 | Oct 16, 2026 | 9000 | 11200 | 62000 | 69300 | |||
| 10 | 9 | Oct 23, 2026 | 9000 | 10900 | 71000 | 80200 | |||
| 11 | 10 | Oct 30, 2026 | 9000 | 11800 | 80000 | 92000 | |||
| 12 | 11 | Nov 6, 2026 | 8000 | 88000 | |||||
| 13 | 12 | Nov 13, 2026 | 8000 | 96000 | |||||
| 14 | 13 | Nov 20, 2026 | 7000 | 103000 | |||||
| 15 | 14 | Nov 27, 2026 | 6000 | 109000 | |||||
| 16 | 15 | Dec 4, 2026 | 6000 | 115000 | |||||
| 17 | 16 | Dec 11, 2026 | 5000 | 120000 |
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.
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.
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.
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.
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.
Result: spills 1 row × 3 columns
| 0.76666666667 | 0.62 | 0.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
AVERAGEMean of numbersTAKEFirst or last rows, spillsFILTERKeeps matching rows, spillsSUMAdds numbersIFPicks a result from a testROUNDDOWNRounds toward zeroXLOOKUPModern lookup in any directionSUMIFSAdds with several conditionsCOUNTIFSCounts with several conditionsHSTACKJoins arrays side by sideCOUNTCounts numbers
Related guides
- Earned value analysis in Excel
- How to calculate cash runway in Excel
- How to make a running total (cumulative sum) in Excel
- How to calculate budget vs actual variance in Excel
- Sum the last N values in a column
- Excel formulas cheat sheet: the 25 everyone should know
- How to clear contents in Excel without deleting formulas
Updated