How is NPV calculated in this template?

Each year's net cash flow is multiplied by the discount factor 1 / (1 + rate)^year and the results are added, with year 0 undiscounted. The calculator example of 350,000 invested and 95,000 a year for 7 years at 10% gives an NPV of 112,499.79, and the Verify sheet runs exactly that case through the workbook.

How are tax and depreciation handled?

Savings minus added operating cost minus depreciation is taxed at your Setup rate. Depreciation is the capital spent in the previous N years divided by N. With 110,000 of savings, 350,000 depreciated over 7 years (50,000 a year) and 25% tax, the tax is 15,000 and the after-tax cash is 95,000. Set the tax rate to 0% for a pre-tax view.

Why is the payback not a whole number of years?

In the year cumulative cash turns positive, the template adds the share of that year's cash needed to recover what was left, assuming cash arrives evenly. If 50,000 is still unrecovered at the end of year 3 and year 4 brings 100,000, payback is 3.5 years. Discounted payback does the same on cumulative present value.

What does the NPV swing chart show?

Each estimate (capital spend, labour savings, material savings, other savings, operating cost, terminal value) is made worse and better by the Setup swing, 20% by default, and the change in NPV is plotted, largest first. If labour savings are worth 608,000 of after-tax present value, a 20% miss moves NPV by about 122,000: that is the estimate to check hardest.

What is the break-even savings level?

It is the share of the planned savings at which NPV falls to zero: 1 - NPV / (present value of savings x (1 - tax rate)). A break-even level of 52% means the project still earns the hurdle rate if only a little over half of the savings arrive.

Is there a CSV version?

Yes. The CSV with live formulas holds the Cash Flows table with the example rows, units in every header and the settings the formulas read listed below the data, so it opens in any spreadsheet app. Depreciation, tax, net cash, present value, cumulative cash and payback are live formulas, and the Total row's present value is the NPV. The dashboard, charts, dropdowns and self-check are in the .xlsx. Excel in a locale that uses semicolons or non-English function names may need Data > From Text/CSV; the .xlsx works everywhere.

Finance and Investment

Capital Project NPV, IRR and Payback Excel Template

Type a project's capital spend and yearly savings and get NPV, IRR and payback against your company's rules, plus which estimate moves the answer most.

Overview

This capital project template turns a project's year-by-year spend and savings into the numbers a capital committee asks for: net present value, internal rate of return, simple and discounted payback and the profitability index. It ships with a worked example for a fictional metal stamping plant evaluating a robotic press-tending cell over ten years, so every number can be traced before you type your own project.

Each year's net cash flow is the savings, less the operating cost the project adds, less tax on the extra profit, less the capital spent, plus any resale value in the last year. Tax depreciation is straight line from the year after each spend, so the tax effect falls as the asset is written off. Discounting each year's cash at your hurdle rate and adding it up gives NPV; IRR is the rate at which NPV is zero; payback is read off the cumulative cash line, assuming cash arrives evenly through the year.

The Dashboard also shows which estimate matters most. The NPV swing chart moves each estimate worse and better by a percentage you set and ranks them, and the sensitivity table shows NPV at five discount rates and four levels of savings achieved, with the break-even savings level at which NPV falls to zero. In the example the cell clears NPV and IRR comfortably but misses a three-year payback rule, which is exactly the discussion the sheet is built for.

What this template includes

Setup for the project name, currency, discount (hurdle) rate, tax rate, tax depreciation period, payback limit and the sensitivity settings
Cash Flows table, one row per year from year 0 to 15: capital spend, labour, material and other savings, added operating cost and after-tax terminal value
Per-year straight-line depreciation, tax effect, net cash flow, discount factor, present value, cumulative cash, cumulative PV and the payback crossing
A Check column that names duplicate years, gaps, years out of order and a terminal value before the last year
Dashboard tiles for NPV, IRR, simple and discounted payback against your hurdle rate and payback limit, capital spend, net cash, profitability index and the break-even savings level
Two charts: cash flow with cumulative cash and cumulative present value by year, and an NPV swing (tornado) chart ranking the estimates that move NPV most
A sensitivity table of NPV at five discount rates and four levels of savings achieved
Method and Verify sheets: every formula explained, and a self-check that runs the Project NPV calculator's own example through the workbook

Suggested use case

Use it to prepare or review a capital request: a robot cell, a new press, an energy retrofit or any project whose case rests on savings over several years, and to show the committee which assumption the answer depends on.

How to use it

On Setup, type the project name, your currency, the discount rate, tax rate, depreciation period and maximum payback your finance team uses.
On Cash Flows, type one row per year from year 0: capital spend, the three kinds of savings, any added operating cost and the after-tax resale value in the last year.
Fix anything the Check column names (a missing year, years out of order).
Open the Dashboard: NPV, IRR and payback against your rules, the NPV swing chart and the sensitivity table.
Change the estimate ranked first on the swing chart and see how far NPV moves before the request goes in.

Frequently Asked Questions

How is NPV calculated in this template?: Each year's net cash flow is multiplied by the discount factor 1 / (1 + rate)^year and the results are added, with year 0 undiscounted. The calculator example of 350,000 invested and 95,000 a year for 7 years at 10% gives an NPV of 112,499.79, and the Verify sheet runs exactly that case through the workbook.
How are tax and depreciation handled?: Savings minus added operating cost minus depreciation is taxed at your Setup rate. Depreciation is the capital spent in the previous N years divided by N. With 110,000 of savings, 350,000 depreciated over 7 years (50,000 a year) and 25% tax, the tax is 15,000 and the after-tax cash is 95,000. Set the tax rate to 0% for a pre-tax view.
Why is the payback not a whole number of years?: In the year cumulative cash turns positive, the template adds the share of that year's cash needed to recover what was left, assuming cash arrives evenly. If 50,000 is still unrecovered at the end of year 3 and year 4 brings 100,000, payback is 3.5 years. Discounted payback does the same on cumulative present value.
What does the NPV swing chart show?: Each estimate (capital spend, labour savings, material savings, other savings, operating cost, terminal value) is made worse and better by the Setup swing, 20% by default, and the change in NPV is plotted, largest first. If labour savings are worth 608,000 of after-tax present value, a 20% miss moves NPV by about 122,000: that is the estimate to check hardest.
What is the break-even savings level?: It is the share of the planned savings at which NPV falls to zero: 1 - NPV / (present value of savings x (1 - tax rate)). A break-even level of 52% means the project still earns the hurdle rate if only a little over half of the savings arrive.
Is there a CSV version?: Yes. The CSV with live formulas holds the Cash Flows table with the example rows, units in every header and the settings the formulas read listed below the data, so it opens in any spreadsheet app. Depreciation, tax, net cash, present value, cumulative cash and payback are live formulas, and the Total row's present value is the NPV. The dashboard, charts, dropdowns and self-check are in the .xlsx. Excel in a locale that uses semicolons or non-English function names may need Data > From Text/CSV; the .xlsx works everywhere.