Capital Budgeting Model Excel Template (Excel XLSX)

BENEFITS OF THIS EXCEL DOCUMENT

1. Calculate capital budgeting for expansion projects
2. Calculate capital budgeting for replacement projects
3. Calculate mutually exclusive projects with unequal lives.

BUSINESS CASE DEVELOPMENT EXCEL DESCRIPTION

Editor Summary Capital Budgeting Model Excel Template is an XLSX Excel workbook that implements 3 capital budgeting methods: expansion projects, replacement projects, and mutually exclusive projects with unequal lives (using Equivalent Annual Annuity). Read more Created by a Financial Modeling, Planning & Performance Consultant with 15+ years at Ernst & Young, Toyota, Viohalco, and Upstream, and certified by the Corporate Finance Institute and the Financial Modeling Institute. Includes calculations for NPV, IRR, payback period, average accounting return, profitability index, and sensitivity analysis; investment duration capped at 10 years. Sold as a digital download on Flevy with immediate digital download.

Use this model when you must evaluate long‑term investment decisions—whether approving greenfield expansions, replacing existing assets, or comparing projects with unequal lives.

  FP&A analysts building financial projections and computing NPV, IRR, payback, and profitability index for a proposed expansion using up to 10 years of forecasts.
  Corporate finance managers modeling an equipment replacement by estimating incremental cash flows for both new and old assets.
  Project finance modelers comparing 2 repeatable projects using Equivalent Annual Annuity to normalize unequal lives.

The workbook’s separation of expansion, replacement, and unequal‑life analysis, plus incremental cash‑flow logic and EAA, aligns with standard corporate finance modeling practices and CFI/FMI training.

Capital Budgeting consists of a process that companies use for decision making on investment projects that usually have a long-term horizon (over a year). This Excel model contains three types of methods: capital budgeting for expansion projects, capital budgeting for replacement projects, and finally mutually exclusive projects with unequal lives.

So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.

On the manual tab, you can feed the general information for the model such as: model name, responsible, timeline of the model, and the date and currency conventions.

Additionally, there is a description of the color-coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.

There is also color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally, light blue tabs are admin tabs (for example: the cover page, contents, and checks).

The next 2 tabs are related to an expansion project, which is an independent project that does not affect the cash flows for the rest of the company. The assumptions for an expansion project consist of general assumptions such as marginal tax rate, tax credit, discount rate, and investment duration. The investment duration is limited to 10 years.

Sunk costs are costs that have already been incurred and are not considered in the capital budgeting process. Afterward, the user needs to set the price of the new investment, how much of the investment will be depreciable, its useful life, and market salvage value (which is the amount that the investment can be sold at the end of its life), as well as the net working capital.

The user will also need to input the first-year sales and their yearly growth, as well as the operating expenses and their growth. Finally, the user will also need to set the sensitivities steps.

Everything is calculated on the next tab, the initial investment, operating cash flows and terminal cash flows are computed resulting in various capital budgeting metrics such as the Net Present Value, the Internal Rate of Return, the Payback Period, the Average Accounting, as well as the profitability index. Sensitivity Analysis follows, for the Net Present Value and Internal Rate of Return. As well as various graphs related to the sensitivity analysis.

The next 2 tabs concern a replacement project, which is a project that affects the company's cash flows. In this case, we must consider the incremental cash flows of the project. Or in other words, the cash flows if the project is made less the cash flows if the project is not made. In this tab, we present the case of the replacement of an old investment with a new investment.

This tab follows the same logic as previously, except that the user needs to fill the assumptions for both the new investment and the old investment. This includes the investment (both new and old), the incremental working capital, the operating cash flows (both new and old).

Everything is calculated on the next tab, the initial investment, operating cash flows and terminal cash flows are computed resulting in various capital budgeting metrics such as the Net Present Value, the Internal Rate of Return, the Payback Period, the Average Accounting, as well as the profitability index. Sensitivity Analysis follows, for the Net Present Value and Internal Rate of Return. As well as various graphs related to the sensitivity analysis.

The third case considers the comparison between two projects which have unequal lives, and that can be repeated or replicated at the end of their life. In this case, the Net Present Value criteria cannot be used, and we need to use the Equivalent Annual Annuity concept to compare these 2 projects.

Finally, we present a set of capital budgeting pitfalls, which have to be taken into account in any capital budgeting process.

This Capital Budgeting Model Excel Template is designed to streamline your investment analysis process, providing a clear framework for evaluating expansion and replacement projects. The user-friendly interface allows for quick adjustments to assumptions, ensuring that you can effectively assess financial metrics like NPV and IRR while visualizing the impact of various scenarios through comprehensive sensitivity analyses.

TOPIC FAQ

What are the common financial metrics used in capital budgeting analysis?

Capital budgeting typically uses Net Present Value (NPV), Internal Rate of Return (IRR), payback period, average accounting return, and profitability index to evaluate projects. When lives differ, analysts apply Equivalent Annual Annuity to compare options. The Flevy Capital Budgeting Model Excel Template computes NPV, IRR, payback, average accounting return, and profitability index.

How should sunk costs and salvage value be treated in a capital budgeting model?

Sunk costs already incurred are excluded from decision analysis; only incremental future cash flows matter. Salvage value is modeled as the market amount realized at asset end‑of‑life and combined with depreciable portion and useful life to compute terminal cash flows. Use explicit inputs for market salvage value and depreciable life.

When is Equivalent Annual Annuity (EAA) the appropriate comparison method?

EAA is appropriate when comparing mutually exclusive projects that have unequal lives, but can be repeated or replicated at end of life. Because NPV alone misleads in unequal‑life scenarios, converting NPVs into an annualized annuity lets you compare projects on an equivalent annual basis using the EAA concept.

What features should I look for when choosing a capital budgeting Excel template?

Look for clear navigation (contents and manual tabs), color‑coded inputs and formulas, support for expansion and replacement cases, incremental cash‑flow logic, built‑in metrics (NPV, IRR, payback), and sensitivity analysis with charts. Confirm the template includes EAA for unequal lives and sensitivity steps for NPV and IRR.

How does using a prebuilt template speed up capital budgeting work compared with building from scratch?

A template provides predefined structure—contents navigation, a manual/assumptions tab, color‑coded inputs, and prebuilt calculations for initial investment, operating and terminal cash flows—reducing setup time and modelling errors. The Flevy Capital Budgeting Model Excel Template includes these components plus sensitivity analysis and graphs.

How do I model an asset replacement decision and the incremental cash flows involved?

Model replacement by estimating cash flows if the new asset is acquired minus cash flows if the old asset remains. Include inputs for both new and old investments, incremental working capital, operating cash flows for each option, and terminal values to compute incremental NPV and IRR based on differential cash flows.

What role does sensitivity analysis play in capital budgeting decisions?

Sensitivity analysis tests how NPV and IRR respond to changes in key assumptions like sales growth, operating expenses, discount rate, and working capital. It helps identify variables with greatest impact and supports scenario planning; the model offers sensitivity steps and visual charts for NPV and IRR.

How should I judge the cost versus value of buying a capital budgeting template?

Evaluate templates by the methods and outputs they provide—support for expansion, replacement, unequal lives, incremental cash‑flow logic, built‑in metrics, and sensitivity analysis—rather than price alone. Flevy’s Capital Budgeting Model Excel Template, for example, bundles those features across 3 project types.

Source: Best Practices in Business Case Development, Business Case Example Excel: Capital Budgeting Model Excel Template Excel (XLSX) Spreadsheet, Big4WallStreet