Excel workbook previewBudgeting, forecasting and FP&A
Capital Budgeting Calculator (NPV, IRR, Payback)
3 free downloads per account. Just your email and a 6-digit code.
About this template
Capital budgeting decides whether a long-term investment, such as a new machine, a system or a building, is worth its cost. The usual tests are net present value, which discounts future cash flows at the required return, the internal rate of return, and the payback period. A capital committee or board normally wants all three on one page, built from after-tax cash flows rather than accounting profit.
This calculator appraises an invented robotic welding cell at Ashcombe Group over a seven-year life. It turns operating gains into after-tax free cash flow with straight-line depreciation, working capital and residual value, then returns NPV, IRR, modified IRR, profitability index and both payback measures. A sensitivity sheet and a five-project comparison follow. It relies on general corporate finance practice and standard spreadsheet functions only.
Who it is for
The finance manager or FP&A analyst asked to show whether a capital project earns its hurdle rate before it goes to the capital committee or the board.
What is inside
- Inputs and Results sheet with 9 yellow inputs and 9 live results, each with how it is calculated
- Cash Flows sheet for years 0 to 10: operating gains, depreciation, tax, capex, working capital, residual value
- NPV calculated two ways, plus IRR, MIRR and profitability index
- Simple and discounted payback in years, worked out from the cumulative cash flow rows
- Sensitivity sheet: NPV at 11 discount rates with a chart, and NPV if operating gains change by 9 steps
- Project Comparison sheet for up to 10 projects, 5 worked, ranked by NPV, with payback from a cumulative cash flow block
- Worked example: a robotic welding cell for an invented manufacturer
How to use it
Enter the capital outlay, project life, discount rate and tax rate on the Inputs and Results sheet, then the yearly operating gains on the Cash Flows sheet.
Files
- XLSXcapital-budgeting-calculator.xlsx20 KB
Opens in Microsoft Word or Excel, Google Docs or Sheets, and LibreOffice. Based on general finance practice, not on one published standard. A template to adapt, not legal, tax or accounting advice.
Questions about this template
What is the difference between NPV and IRR?
NPV is the value today of all future cash flows, discounted at your hurdle rate, minus the investment. A positive NPV means the project earns more than the hurdle rate. IRR is the discount rate at which NPV is exactly zero. NPV gives a money amount and is the safer basis for choosing between projects of different sizes.
Why use cash flows instead of profit for capital budgeting?
Investors and lenders are repaid in cash, and the timing of cash matters for the time value of money. Depreciation is not a cash cost, so it is used only to work out the tax effect. Working capital tied up at the start and released at the end, and any residual value, are included because they are real cash movements.
What discount rate should I use for NPV?
Use the return your organization requires from projects of similar risk, often called the hurdle rate. Many companies start from their weighted average cost of capital and add a premium for riskier projects. The calculator treats the rate as an input and shows NPV across eleven rates, so you can see how much the answer depends on it.




