Capital Budgeting Model Excel Template (Excel XLSX)

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.