Vertex42 The Excel Nexus
We would consider proceeding with a project only where the benefits outweigh the costs. However, in order to choose among projects, we need to take into account the timing of the costs and benefits as well as the benefits relative to the size of the investment.
Consider the project cash flow estimates for four projects at IOE shown in Exercise 3.2 Table 3.2. Negative values represent expenditure and positive values income.
Rank the four projects in order of financial desirability and make a note of your reasons for ranking them in that way before reading further.
In the following sections we will take a brief look at some common methods for comparing projects on the basis of their cash flow forecasts.
The net profit of a project is the difference between the total costs and the total income over the life of the project. Project 2 in Table 3.2 shows the greatest net profit but this is at the expense of a large investment. Indeed, if we had £lm to invest, we might undertake all of the other three projects and obtain an even greater net profit. Note also, that all projects contain an element of risk and we might not be prepared to risk £1 m. We shall look at the effects of risk and investment later in this chapter.
Table 3.2 Four project cash flow projections - figures are end of year totals (£)
Year Project I Project 2 Project 3 Project 4
1 10,000 200,000 30,000 30,000 Cash flows take place at
2 10,000 200,000 30,000 30,000 the ^ °f eaCh yean year 0 figure represents
3 10,000 200,000 30,000 30,000 the ^ investment made
4 20,000 200,000 30,000 30,000 at the start of the project.
5 100,000 300,000 30,000 75,000 Net profit 50,000 100,000 50,000 75,000
Moreover, the simple net profit takes no account of the timing of the cash flows. Projects 1 and 3 each have a net profit of £50,000 and therefore, according to this selection criterion, would be equally preferable. The bulk of the income occurs late in the life of project 1, whereas project 3 returns a steady income throughout its life. Having to wait for a return has the disadvantage that the investment must be funded for longer. Add to that the fact that, other things being equal, estimates in the more distant future are less reliable that short-term estimates and we can see that the two projects are not equally preferable.
The payback period is the time taken to break even or pay back the initial investment. Normally, the project with the shortest payback period will be chosen on the basis that an organization will wish to minimize the time that a project is 'in debt'.
Exercise 3.3 Consider the four project cash flows given in Table 3.2 and calculate the payback period for each of them.
The advantage of the payback period is that it is simple to calculate and is not particularly sensitive to small forecasting errors. Its disadvantage as a selection technique is that it ignores the overall profitability of the project - in fact, it totally ignores any income (or expenditure) once the project has broken even. Thus the fact that projects 2 and 4 are, overall, more profitable than project 3 is ignored.
The return on investment (ROI), also known as the accounting rate of return (ARR), provides a way of comparing the net profitability to the investment required. There are some variations on the formula used to calculate the return on investment but a straightforward common version is
R0I = average annual profit x ]0Q total investment
Exercise 3.4 Calculating the ROI for project 1, the net profit is £50,000 and the total investment is £100,000. The return on investment is therefore calculated as
RQI _ average annual profit x 10Q total investment
100,000
Calculate the ROI for each of the other projects shown in Table 3.2 and decide which, on the basis of this criterion, is the most worthwhile.
Table 3.3 Table ofNPV discount factors
Discount rate (%)
Table 3.3 Table ofNPV discount factors
Discount rate (%)
Year |
5 |
6 |
8 |
10 |
12 |
15 | |
1 |
0.9524 |
0.9434 |
0.9259 |
0.9091 |
0.8929 |
0.8696 | |
More extensive or detailed |
2 |
0.9070 |
0.8900 |
0.8573 |
0.8264 |
0.7972 |
0.7561 |
tables may be constructed |
3 |
0.8638 |
0.8396 |
0.7938 |
0.7513 |
0.7118 |
0.6575 |
using the formula | |||||||
4 |
0.8227 |
0.7921 |
0.7350 |
0.6830 |
0.6355 |
0.5718 | |
f |
5 |
0.7835 |
0.7473 |
0.6806 |
0.6209 |
0.5674 |
0.4972 |
(i+/•) |
6 |
0.7462 |
0.7050 |
0.6302 |
0.5645 |
0.5066 |
0.4323 |
for various values of r(the |
7 |
0.7107 |
0.6651 |
0.5835 |
0.5132 |
0.4523 |
0.3759 |
discount rate) and t (the |
8 |
0.6768 |
0.6274 |
0.5403 |
0.4665 |
0.4039 |
0.3269 |
number of years from now) |
9 |
0.6446 |
0.5919 |
0.5002 |
0.4241 |
0.3606 |
0.2843 |
10 |
0.6139 |
0.5584 |
0.4632 |
0.3855 |
0.3220 |
0.2472 | |
15 |
0.4810 |
0.4173 |
0.3152 |
0.2394 |
0.1827 |
0.1229 | |
20 |
0.3769 |
0.3118 |
0.2145 |
0.1486 |
0.1037 |
0.0611 | |
25 |
0.2953 |
0.2330 |
0.1460 |
0.0923 |
0.0588 |
0.0304 |
Exercise 3.5 Assuming a 10% discount rate, the NPV for project 1 (Table 3.2) would be calculated as in Table 3.4. The net present value for Project 1, using a 10% discount rate is therefore £618. Using a 10% discount rate, calculate the net present values for projects 2, 3 and 4 and decide which, on the basis of this, is the most beneficial to pursue.
Year |
Project 1 cash flow Discount factor @ (£) 10% |
Discounted cash flow (£) | |
0 |
-100,000 |
1.0000 |
-100,000 |
1 |
10,000 |
0.9091 |
9,091 |
2 |
10,000 |
0.8264 |
8,264 |
3 |
10,000 |
0.7513 |
7,513 |
4 |
20,000 |
0.6830 |
13,660 |
5 |
100,000 |
0.6209 |
62,090 |
Net Profit: |
£50,000 |
NPV: £618 |
It is interesting to note that the net present values for projects 1 and 3 are significantly different - even though they both yield the same net profit and both have the same return on investment. The difference in NPV reflects the fact that, with project 1, we must wait longer for the bulk of the income.
The main difficulty with NPV for deciding between projects is selecting an appropriate discount rate. Some organizations have a standard rate but, where this is not the case, then the discount rate should be chosen to reflect available interest rates (borrowing costs where the project must be funded from loans) plus some premium to reflect the fact that software projects are inherently more risky than lending money to a bank. The exact discount rate is normally less important than ensuring that the same discount rate is used for all projects being compared. However, it is important to check that the ranking of projects is not sensitive to small changes in the discount rate - have a look at the following exercise.
Calculate the net present value for each of the projects A, B and C shown in Exercise 3.6 Table 3.5 using each of the discount rates 8%, 10% and 12%.
For each of the discount rates, decide which is the best project. What can you conclude from these results?
Alternatively, the discount rate can be thought of as a target rate of return. If, for example, we set a target rate of return of 15% we would reject any project that did not display a positive net present value using a 15% discount rate. Any project that displayed a positive NPV would be considered for selection - perhaps by using an additional set of criteria where candidate projects were competing for resources.
Year |
Project A (£) |
Project B (£) |
Project C (£) |
0 |
-8,000 |
-8,000 |
- 10,000 |
1 |
4,000 |
1,000 |
2,000 |
2 |
4,000 |
2,000 |
2,000 |
3 |
2,000 |
4,000 |
6,000 |
4 |
1,000 |
3,000 |
2,000 |
5 |
500 |
9,000 |
2,000 |
6 |
500 |
-6,000 |
2,000 |
Net Profit |
4,000 |
5,000 |
6,000 |
Internal rate of return
One disadvantage of NPV as a measure of profitability is that, although it may be used to compare projects, it might not be directly comparable with earnings from other investments or the costs of borrowing capital. Such costs are usually quoted
The IRR may be estimated by plotting a series of guesses:
For a particular project, a discount rate of 8% gives a positive NPV of £7,898; a discount rate of 12% gives a negative NPV of -£5,829. The IRR is therefore somewhere between these two values. Plotting the two values on a chart and joining the points with a straight line suggests that the IRR is about 10.25%. The true IRR (calculated with a spreadsheet) is 10.167%.
as a percentage interest rate. The internal rate of return (IRR) attempts to provide a profitability measure as a percentage return that is directly comparable with interest rates. Thus, a project that showed an estimated IRR of 10% would be worthwhile if the capital could be borrowed for less than 10% or if the capital could not be invested elsewhere for a return greater than 10%.
The IRR is calculated as that percentage discount rate that would produce an NPV of zero. It is most easily calculated using a spreadsheet or other computer program that provides functions for calculating the IRR. Microsoft Excel and Lotus, for example, both provide IRR functions which, provided with an initial guess or seed value (which may be zero), will search for and return an IRR.
Manually, it must be calculated by trial-and-error or estimated using the technique illustrated in Figure 3.3. This technique consists of guessing two values
Was this article helpful?
What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.