Free, Downloadable Sample: Capital Budget Template (Excel)
Defining Capital Budgeting
Capital budgeting is the process of evaluating a firm’s investment opportunities that can enhance shareholder value. Management uses capital budgeting to determine if a project is worth pursuing based on the amount of capital available and the expected return on investment over an appropriate period of time. Capital budgeting estimates the expected cash flows and payback period and calculates the Net Present Value (NPV) and the Internal Rate of Return (IRR) on investment.
Using a Capital Budget Template
A capital budget template is used to assist investors, stakeholders and managers in accurately:
- Estimating a project’s expected cash flows.
- Determining the cost of capital (WACC) for a project.
- Estimating a project’s payback period.
- Calculating the Net Present Value (NPV) of a project.
- Estimating a project’s Internal Rate of Return (IRR).
A capital budget template allows the cash flows of independent or mutually exclusive projects to be compared in order to decide which project is worth pursuing. To better understand how a capital budget template is used, we assume that the management has to decide between two independent projects that meet specific investment criteria. To that end, certain assumptions have to be made and certain steps need to be followed in order to create a template. You can find a capital budget template in Bright Hub’s Media Gallery; and the views below will show you how to work with it.
1. Net Working Capital for Project A and Project B
Net working capital is the difference between a firm’s current assets and current liabilities. If the difference is positive, the company is less likely to default on its short-term liabilities.
We assume these net working capital for Project A and Project B for a period of 7 years (image to the left).
2. WACC Calculation for Project A and Project B
We assume the same cost of capital (WACC) for both projects to facilitate calculations on Net Present Value (NPV). However, generally the project with the lower cost of capital is selected. For instance, if Project A had WACC 10.7% and Project B had WACC 12%, Project A would be selected.
3. Calculation of Payback Period
The payback period is the expected number of years required to cover the cost of the investment. The year in which the cumulative cash inflows exceed the initial cash outflows is the payback year. As a rule of thumb, the shorter the payback period, the better the investment.
According to calculations, Project B should be selected because it has a shorter payback period (2.88 years) over Project A (4.63 years).
4. Calculation of Net Present Value (NPV) for Project A and Project B
The Net Present Value (NPV) is the difference between the present value of incremental inflows and the present value of incremental costs. When comparing two independent projects, management selects the project with the greater Net Present Value because this project is more likely to generate sufficient cash flows to pay back the invested capital and to provide the required return to shareholders.
If Project A is undertaken, the shareholder’s value will be increased by $11,305, while if Project B is undertaken, the shareholder’s value will be increased by $27,217. Therefore, Project B should be undertaken as its NPV indicates that the project’s cash flows (both inflows and outflows) after the payback period are more profitable than those of Project A.
5. Calculation of Internal Rate of Return (IRR)
The Internal Rate of Return (IRR) is the discount rate that makes the Net Present Value equal to zero. The IRR is the growth rate that the project is expected to generate to make the NPV of the expected cash flows equal to zero. As a rule of thumb, the higher the IRR, the better the investment.
The IRR for Project A is 18.6% and for Project B is 28.2%. Therefore, Project B should be undertaken because it has a higher growth potential. (The IRR is calculated using the IRR function in Excel.)
Some Important Considerations in Capital Budgeting
Although a capital budget template is extremely useful in assisting management with selecting between two investment projects, there are some important factors that need to be considered:
Project relationship: If the projects are independent, the cash flows of Project A are not affected if Project B is selected and vice versa. In this case, the project with the positive NPV is selected. If the projects are mutually exclusive, selecting Project A may adversely impact Project B. In this case, the project with the largest NPV should be selected.
Using NPV or IRR: In independent projects, the NPV and IRR yield the same result, either rejecting or undertaking a project. In mutually exclusive projects, the NPV and IRR are usually conflicting. This is because the project sizes may vary or the timing of cash flows may be different. The IRR should be preferred over NPV only when the project can sustain a high growth and the IRR seems reasonable. Management should examine historical records and future business potential to determine if the project sustains a growth opportunity to reinvest cash flows at a high IRR. If the IRR seems unrealistic, the NPV method should be preferred.
Payback Period and NPV: Payback period does not consider the time value of money and the cash flows that are paid or received after the payback period. As the idea that the payback period provides about how long the invested capital is at risk is pretty rough, NPV is introduced as a more reliable method of capital budgeting to determine a project’s worthiness.
Resources
Capital Budgeting - https://www.willamette.edu/~fthompso/501/Fin10.pdf
Net Working Capital - https://www.willamette.edu/~fthompso/501/Fin10.pdf
Payback Period - https://www.willamette.edu/~fthompso/501/Fin10.pdf
Net Present Value - https://www.willamette.edu/~fthompso/501/Fin10.pdf
Internal Rate of Return - https://www.willamette.edu/~fthompso/501/Fin10.pdf
Image Credit: - Net Working Capital created for this article by author Christina Pomoni
Image Credit: - WACC created for this article by author Christina Pomoni
Image Credit: - Payback Period created for this article by author Christina Pomoni
Image Credit: - NPV Calculation created for this article by author Christina Pomoni
Image Credit: - Capital Budget Template created for this article by author Christina Pomoni
