What is capital budgeting?
It is through capital budgeting that organizations decide what to invest in. The investment might be as simple as a piece of machinery, or as complicated as building an entirely new manufacturing facility. Capital budgeting doesn’t just work for equipment, though. Any long-term project can benefit from capital budgeting.
Several different calculations are used to make these capital budgeting decisions. These include net present value (NPV), internal rate of return (IRR), modified internal rate of return (MIRR), payback period, and profitability index (PI).
Future cash inflows and outflows are used to calculate all of these metrics. How they are each calculated is explained below. They all measure future cash inflows and outflows one way or another.
Calculating and understanding NPV, IRR, and the other metrics in Google Sheets (Excel) can help your small business invest in the most profitable projects and avoid unprofitable ones.
Most of the information needed for capital budgeting is easily obtainable. There are two critical estimates that you will need to make, however. The first is the future cash flows for the project. The other is the rate used to discount cash flows. This too will be explained in-depth below.
The downloadable spreadsheet template contains two worksheets. Input all information related to future cash flows on the Data Entry worksheet. The Executive Summary worksheet shows the NPV, IRR, and other metrics. A chart is also included to aid in visualization.
Capital budgeting workbook download
Need a financial (cash) budget too? Read this post:
EXAMPLE + TEMPLATE OF A SMALL BUSINESS FINANCIAL BUDGET
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
Data Entry Worksheet
Need an operating budget for next year too? Read this post:
OPERATING BUDGET TEMPLATE FOR SMALL BIZ – OVERVIEW & EXAMPLE
Original cost of old asset(s)
Enter the original cost of any assets that will be replaced as a part of this project. Remember transportation, setup, and any other costs included in the original depreciated cost of the asset.
Salvage value of old asset(s)
This is the original expected salvage value of the assets that will be replaced. The Original cost of old asset(s) minus the Salvage value of old asset(s) is the amount that gets depreciated on the income statement.
Depreciable life of old asset(s) in years
Curious how SFB can help with other business financial needs? Read this:
SPREADSHEETS FOR BUSINESS HOME
This is the number of years you can deduct part of the depreciable value of an asset from your income. This value is set by the IRS.
Method of depreciation old asset(s)
Here, we have a dropdown menu where you can choose the method utilized to depreciate the old assets. Options are double declining balance, straight line, declining balance, sum of years digits, and MACRS.
Current age of old asset(s) in years
Enter the number of years the asset to be replaced has been in service (been depreciated). This will affect the subsequent tax treatment and therefore future cash flows.
Current market value of old asset(s)
This is the price at which the asset to be replaced could be sold at the time of project start.
Cost of new asset
The purchase price of the new asset. Be sure to include any costs that will be incurred to bring the asset into service. For example, transportation, setup, consultations, etc.
Salvage value of new asset(s)
The expected value of the new asset once all depreciation has been subtracted.
Depreciable life of new asset(s) in years
Enter the number of years that the new asset will be subject to depreciation. The IRS sets this value.
Useful life of new asset(s) in years
This is the number of years you actually plan to use the new asset. This doesn’t have to be the same as the Depreciable life of new asset(s) in years.
Method of depreciation new asset(s)
This determines how the new asset will be depreciated against income. The amount and timing of depreciation will affect taxes. This method does not have to be the same method used to depreciate the old asset.
Effective tax rate
Your organization’s tax rate on income. This ties into the depreciation information entered previously and will affect the timing and amount of cash flows due to taxes.
Cost of capital/hurdle rate
The discount rate that will be used in the NPV calculation and other metrics on the Executive Summary worksheet. An entire post could be written on picking an appropriate discount rate.
Here are two things you might consider when picking a Cost of capital/hurdle rate.
First, you can use your organizations cost of capital rate. The weighted average cost of your debt and equity. The theory goes – this is what it costs you to raise capital, so any project you undertake is going to have to provide a better return.
Also, you can use a hurdle rate for your organization. A hurdle rate is a minimally acceptable rate of return. I.e. you would not undertake a project that did not provide at least this much of a return.
Use either rule-of-thumb (or any other rate you feel is appropriate).
Change in working capital
Working capital = current assets – current liabilities. If working capital will increase due to this project, then that amount should be entered as a negative number. This is because more money will be tied up in things such as inventory or accounts receivable.
Conversely, enter a positive number if working capital will decrease.
Capital costs and proceeds
This includes the initial cash outflow from the purchase of the new asset. Also, the cash inflows from the eventual liquidation of the new asset (if applicable and net of taxes). Automatically calculated.
Additional costs
In this column, you will list all anticipated costs, year by year, from the undertaking of this project. Costs not incurred otherwise. Also, keep in mind that these are cash flows – when cash leaves the organization. Not accrual accounting costs that may fall in a different period.
Taxes
An estimate of the timing and amount of tax payments. Automatically calculated.
Net sales proceeds of old asset(s)
The expected cash inflow from the disposition of the asset to be replaced. Includes the effects of a gain or loss and taxes. Automatically calculated.
Additional revenue
These are the forecasted cash inflows that the organization will enjoy as a result of taking this project on. These are revenues that would not have otherwise happened without the project. List only cash inflows, not accrual accounting revenue that might take place in a different time period.
Additional cost savings
It is possible that a project might provide cost savings to the organization in lieu of, or in addition to, Additional Revenue. List forecasted cost savings, by year, in this column.
Depreciation tax shield
The savings on taxes as a result of depreciation. Note that the effects only last as long as the number of years entered in the Depreciable life of new asset(s) in years field. Automatically calculated.
Net cash flow
This is the sum of all of the expected cash inflows and outflows for each year of the project. On the Executive Summary worksheet, these amounts will factor into the calculation of the NPV and other metrics. Automatically calculated.
Executive Summary worksheet
NPV
The sum of all of the future cash flows discounted back to the present minus the initial cash outflow. A positive NPV implies that a project will be profitable. A negative NPV implies that a project will not. The accuracy of the NPV is contingent upon accurate cash flow forecasts and an appropriate discount rate.
IRR
The Cost of capital/hurdle rate that would give the project an NPV of $0. Consider this the annualized rate of return this project is earning.
MIRR
Similar to the IRR. Except the assumption is that cash inflows get reinvested at the Cost of capital/hurdle rate, not the IRR rate. I.e. other projects won’t have the same returns as this one.
Payback Period
The number of years it will take undiscounted cash flows to pay back the initial cash outflow. More of an informational metric than one to base decisions off of.
Profitability Index
Equals the present value of all future cash flows divided by the initial cash outflow. Therefore, the PI puts the NPV into perspective. The PI increases in proportion to the amount that cash inflows dwarf initial cash outflows.
NPV spreadsheet chart
This chart provides a quick visualization of the timing and magnitude of cash flows throughout the project.