Investment and operating outflows are two types of cash outflows that occur in a business or a project. Investment outflows are the initial or periodic expenditures that are required to start or maintain the project, such as buying equipment, land, or inventory. Operating outflows are the ongoing expenses that are incurred to run the project, such as paying salaries, taxes, or utilities.
Both investment and operating outflows affect the profitability and viability of a project, and therefore, they need to be carefully estimated and analyzed. One of the tools that can help with this analysis is Excel, which has various functions and features that can help with calculating and presenting the cash flows of a project.
In this article, we will explain the basic theory of investment and operating outflows, the procedures to calculate them in Excel, and a scenario to illustrate the application of the Excel formula. We will also provide some alternative approaches that can be used to perform the same analysis.
The basic theory of investment and operating outflows is based on the concept of cash flow, which is the difference between the cash inflows and cash outflows of a project over a period of time. Cash flow can be positive or negative, depending on whether the project generates more cash than it spends, or vice versa.
Cash flow can be classified into three categories: operating cash flow, investing cash flow, and financing cash flow. Operating cash flow is the cash generated or consumed by the normal operations of the project, such as selling products or services, paying wages, or buying raw materials. Investing cash flow is the cash spent or received by investing in long-term assets, such as buying or selling equipment, land, or securities. Financing cash flow is the cash raised or paid by borrowing or repaying loans, issuing or repurchasing shares, or paying dividends.
Investment and operating outflows are subsets of investing and operating cash flows, respectively. They represent the negative cash flows that reduce the net cash flow of the project. Therefore, they need to be subtracted from the positive cash flows to obtain the net cash flow.
The net cash flow of a project is an important indicator of its profitability and feasibility, as it shows how much cash is left after paying for all the expenses. A positive net cash flow means that the project is generating enough cash to cover its costs and provide a return to the investors. A negative net cash flow means that the project is losing money and may not be able to sustain itself in the long run.
One of the methods to evaluate the profitability and feasibility of a project is to calculate its net present value (NPV), which is the sum of the present values of all the cash flows of the project, discounted by a certain rate of return. The NPV reflects the value of the project in today’s dollars, and it can be compared with the initial investment to determine whether the project is worth pursuing or not. A positive NPV means that the project is expected to generate more cash than the initial investment, and therefore, it is profitable. A negative NPV means that the project is expected to generate less cash than the initial investment, and therefore, it is unprofitable.
Another method to evaluate the profitability and feasibility of a project is to calculate its internal rate of return (IRR), which is the discount rate that makes the NPV of the project equal to zero. The IRR reflects the annualized return of the project, and it can be compared with the required rate of return to determine whether the project is acceptable or not. A higher IRR means that the project is more profitable, and a lower IRR means that the project is less profitable.
Procedures
To calculate the investment and operating outflows in Excel, we need to follow these steps:
- Create a table that lists the cash flows of the project for each year, including the initial investment, the operating inflows, the operating outflows, the investing inflows, and the investing outflows. The table should also include the net cash flow, which is the sum of all the cash flows, and the cumulative cash flow, which is the running total of the net cash flow.
- Use the NPV function to calculate the net present value of the net cash flow, excluding the initial investment. The NPV function has the syntax:
=NPV(rate, value1, [value2], ...), where rate is the discount rate, and value1, value2, … are the cash flows for each period. The NPV function assumes that the cash flows occur at the end of each period, so we need to adjust the formula by adding the initial investment to the result. - Use the IRR function to calculate the internal rate of return of the net cash flow, including the initial investment. The IRR function has the syntax:
=IRR(values, [guess]), where values is the range of cash flows for each period, and guess is an optional argument that specifies an initial estimate of the IRR. The IRR function iterates until it finds a result that is accurate within 0.00001 percent, or until it reaches 20 iterations. If the IRR function cannot find a result, it returns an error value.
Example
To illustrate the application of the Excel formula, let us consider a hypothetical scenario of a project that requires an initial investment of $100,000, and generates the following cash flows for the next five years:
| Year | Operating Inflows | Operating Outflows | Investing Inflows | Investing Outflows |
|---|---|---|---|---|
| 1 | $30,000 | $10,000 | $0 | $20,000 |
| 2 | $40,000 | $15,000 | $0 | $10,000 |
| 3 | $50,000 | $20,000 | $0 | $0 |
| 4 | $60,000 | $25,000 | $0 | $0 |
| 5 | $70,000 | $30,000 | $10,000 | $0 |
The discount rate for the project is 10%. We want to calculate the investment and operating outflows, the net cash flow, the NPV, and the IRR of the project.
To do this, we can use the following Excel table:
| Year | Operating Inflows | Operating Outflows | Investing Inflows | Investing Outflows | Net Cash Flow | Cumulative Cash Flow |
|---|---|---|---|---|---|---|
| 0 | $0 | $0 | $0 | $100,000 | -$100,000 | -$100,000 |
| 1 | $30,000 | $10,000 | $0 | $20,000 | $0 | -$100,000 |
| 2 | $40,000 | $15,000 | $0 | $10,000 | $15,000 | -$85,000 |
| 3 | $50,000 | $20,000 | $0 | $0 | $30,000 | -$55,000 |
| 4 | $60,000 | $25,000 | $0 | $0 | $35,000 | -$20,000 |
| 5 | $70,000 | $30,000 | $10,000 | $0 | $50,000 | $30,000 |
The formulas for the net cash flow and the cumulative cash flow are as follows:
| Year | Net Cash Flow | Cumulative Cash Flow |
|---|---|---|
| 0 | =C7+D7+E7+F7 | =G7 |
| 1 | =C8+D8+E8+F8 | =G8+H7 |
| 2 | =C9+D9+E9+F9 | =G9+H8 |
| 3 | =C10+D10+E10+F10 | =G10+H9 |
| 4 | =C11+D11+E11+F11 | =G11+H10 |
| 5 | =C12+D12+E12+F12 | =G12+H11 |
The formulas for the NPV and the IRR are as follows:
| NPV | =NPV(C4,G8:G12)+G7 |
|---|---|
| IRR | =IRR(G7:G12) |
The results are as follows:
| NPV | $3,146.27 |
|---|---|
| IRR | 11.32% |
We have explained how to calculate the investment and operating outflows in Excel formula, using the NPV and IRR functions. We have also provided a scenario to demonstrate the application of the formula. The results show that the project has a positive NPV and a higher IRR than the discount rate, which means that it is profitable and acceptable.
Alternative Approaches
There are some alternative approaches that can be used to perform the same analysis as the Excel formula. Some of them are:
- Using a financial calculator, such as the HP 10bII+, which has built-in functions for calculating NPV and IRR. To use the calculator, we need to enter the cash flows in the CFj register, and then press the NPV or IRR key to get the result. The calculator also assumes that the cash flows occur at the end of each period, so we need to adjust the formula by adding the initial investment to the NPV result.
- Using a spreadsheet software, such as Google Sheets, which has similar functions to Excel, such as NPV and IRR. The syntax and usage of these functions are almost identical to Excel, except that Google Sheets uses a comma (,) instead of a semicolon (;) to separate the arguments. The spreadsheet software also allows us to create charts and graphs to visualize the cash flows and the results.
- Using a mathematical software, such as MATLAB, which has numerical and symbolic tools for solving various problems, including financial ones. MATLAB has functions such as npv and irr that can calculate the net present value and the internal rate of return of a cash flow vector. MATLAB also has functions such as fzero and fsolve that can find the roots of nonlinear equations, which can be used to calculate the IRR manually. MATLAB also has a graphical user interface and a command window that can display the results and the plots.
