In an investment analysis, one starts with a project’s expected cash flows and evaluates the profitability of the project by comparing it with a given alternative. Consider project Alfa where you invest €10 million and receive €2 million at the end of each year for the next three years, and €12 million at the end of the fourth year. This can be formulated in the following way, with the amounts representing millions of euros:
(–10, 2, 2, 2, 12)
Since you invest 10, receive 2 each year and have the entire amount invested returned the last year, the rate of return must be 2/10 = 20 per cent for project Alfa. Whether or not this is a satisfactory return on the investment depends on the project’s risk. Assume that you can receive 8 per cent return if you place the money in a bank or government bonds. This is regarded as a risk-free investment. If project Alfa is entirely without risk, and the future cash flows are completely secure, then Alfa is a much better alternative than putting money in the bank. If Alfa is a less secure investment, where future cash flows are uncertain estimates, you must have a much higher rate of return to justify that risk. You do this by choosing a rate of return, r, which increases proportionally with the risk. This required rate of return can be calculated by taking the return from a risk-free investment, and adding some percentage points for the project’s risk. If possible, one can also see what rate of return one can achieve for alternative projects with the same risk. If the project under consideration gives a higher rate of return than the required rate of return, then the project should be completed. Otherwise, one should place the money in an investment that does meet the required rate of return.
There are a number of methods for completing an investment analysis. The most important of these are shown in the following sections.
The best way to evaluate the profitability of an investment is to use the net present value (NPV) method. NPV is calculated by discounting and adding together all the discounted cash flows C1, C2, …, Cn from the project, and subtracting the sum invested, I. The current date is used as the starting point for discounting the cash flows.
If NPV > 0, then the project is profitable, and gives a higher rate of return than is required. Otherwise, the project is unprofitable.
A business can invest $70 million in machinery and equipment for production of a new product. The project will last five years. (One is currently at the end of year 0.) The cash flows will come regularly through the years, but to simplify calculations, it is assumed that all cash flows occur at the end of each year. For the first five years, the project budgets with the following inflows (from product sales) and outflows (production costs). At the end of year 5 the project ends, and the production equipment is sold for $12 million. Taxes are not considered, and all cash flows are in millions:
The project’s cash flows can also be written: (–70, 16, 24, 32, 19, 23) One assumes that an alternative project with the same risk has a rate of return of 12 per cent. Net present value (in million dollars) for the project is calculated by discounting and adding the future cash flows, then subtracting the investment:
In Excel, the present value of future cash flows can be calculated using the function NPV. When the investment is subtracted, one obtains NPV as shown in figure 7.1.
Figure 7.1 Net present value calculation.
In this example NPV = $11.3 million. That means that one obtains $11.3 million more by investing in the project than one can obtain by investing the money with a 12 per cent rate of return, with the same risk.
Remember that you must always subtract the investment to obtain the correct net present value.
Whether an investment project’s NPV is positive or negative depends on the rate of return specified (see equation 7.1). Rather than specifying only a single rate of return, varying the rate of return can provide insight into how the NPV changes with the different rates of return. This will be done in the next chapter.
The net cash flows for the project shown in example 7.1 are (–70, 16, 24, 32, 19, 23). A present value profile, as shown in figure 7.2, can be constructed using Excel’s line chart. An alternative to calculating NPV values using different rates of return is shown in cells B12:C25. These contain the various rate of return values (column B), and the NPV obtained (column C). Thereafter, one can make a scatter diagram with B12:B25 as the X-values and C12:C25 as the Y-values.
Figure 7.2 Present value profile.
We note that NPV > 0 and that the project is profitable for rate of returns under ca. 18 per cent. For rate of returns above ca. 18 per cent the NPV < 0 and the project is unprofitable.
7.2 Internal rate of return method
In example 7.2 NPV = 0 with a rate of return of 18 per cent. The rate of return that gives NPV = 0 is called the project’s internal rate of return. For a project lasting n years, with an investment I and cash flows C1, C2, …, Cn, the internal rate of return IRR is found by solving the following equation:
We understand that if the internal rate of return is higher than the discount rate for a project, then NPV > 0, and the project is profitable. If the internal rate of return is lower than the discount rate, then the project is unprofitable. An internal rate of return equal to the discount rate gives NPV = 0. The internal rate of return method gives the same result as the present value method, in this example. However, as we will see later in this chapter, the internal rate of return method can be inappropriate in certain situations. The present value method is always the best method for evaluating an investment project.
In the next examples we will see how Excel can be used to find IRR in different situations.
Example 7.3
A project requires an investment of €12 million and gives an annual cash surplus of €3 million for five years. In principle, the internal rate of return can be found from the following expression (see equation 6.2):
Figure 7.3 Internal rate of return calculation using the RATE function.
This fifth degree equation is difficult to solve. When one has fixed future cash flows, Excel’s RATE function can be used to find the internal rate of return. This is illustrated in figure 7.3.
Example 7.4
An investment project gives the following cash flow in $ million: (–18, 5, 6, 8, 5)
(The initial investment is $18 million.)
It will be fairly difficult to find the internal rate of return from the following expression (see equation 7.2):
When future cash flows vary, the easiest approach is to use Excel’s IRR function to find the internal rate of return. All cash flows must be entered into the spreadsheet, including the initial investment, which is also one of the function’s arguments.
Figure 7.4 Internal rate of return calculation using the IRR function.
In situations where future cash flows occur on specified dates, the XIRR function should be used.
An annuity loan of £80,000 with an annual interest rate of 6 per cent is to be repaid with monthly instalments over 20 years. There is an arrangement fee of £350, and an instalment fee of £5. Find the effective annual interest rate for the loan.
The interest rate per instalment is (6/12) per cent = 0.5 per cent and the number of instalments is 12 · 20 = 240. The instalment payment without fees can be calculated using the function PMT. This results in a payment of £573.14. The instalment payment with fees will be £(573.14 + 5) = £578.14. The sum received by the borrower is £(80,000 – 350) = 79,650. The effective interest rate per instalment is given by the expression:
IRR can be calculated using Excel’s RATE function as 0.5136 per cent. The effective annual interest rate is:
Figure 7.5 An annuity loan.
Example 7.6
You pay $120 for a security. The next four years (at the end of each year) you receive $8 in dividends. Thereafter, the dividends will increase by 3 per cent each year in perpetuity. What is the annual rate of return on this investment?
The internal rate of return for the investment has to be calculated by setting the expression for NPV equal to 0 (see equations 6.1 and 6.2):
This complicated equation can be solved using Excel’s Goal Seek, as shown in figure 7.6. Here we have chosen to put the left-hand side of the equation in cell B8. In the formula we refer to cell B9 where we have placed the value for IRR. In Excel you click on Data on the ribbon. You then locate the Data Tools panel and the What-If Analysis item. From the What-If Analysis menu, you select Goal Seek. The Goal Seek dialogue box appears. Here you indicate that you will change the content of cell B9 (IRR) so that the content in cell B8 (NPV) becomes 0. When you click OK in the dialogue box, the answer, IRR = 9.16 per cent, appears in cell B9.
Figure 7.6 Internal rate of return calculation using Goal Seek.
Problems with the internal rate of return method
Care must be taken when using the internal rate of return method. For some projects, the cash flows change their sign more than once. This can result in the internal rate of return being undefined. The comparison of different projects can be difficult if the internal rate of return method is used. We will illustrate these weaknesses with some examples. Remember that the present value method does not have these weaknesses. It will always give the correct answer.
A project requires a net payment of €50 million at its start. After one year the project returns a net cash flow of €110 million. After two years the production facilities have to be removed and the property restored. This gives a (negative) cash flow of –€60.48 million:
(–50,000,000, 110,000,000, –60,480,000)
If you try to calculate the internal rate of return for this situation, you obtain two solutions:
The project cannot return both 8 per cent and 12 per cent! Both of these internal rates of return are meaningless. The internal rate of return is not defined for the project. If we attempt to calculate the internal rate of return with Excel’s IRR function, we will be given either 8 per cent or 12 per cent as the answer. If we want to obtain one of these as the answer, we can provide an appropriate start value (guess) in the dialogue box for the function. In figure 7.7 we provide 1 per cent as the start value. Excel then calculates 8 per cent as the answer. If we provide a high start value, e.g. 100 per cent, Excel will calculate 12 per cent as the answer.
Figure 7.7 The IRR dialogue box.
To study the profitability of the investment, we should create a present value profile as shown in figure 7.8. This shows NPV > 0 and that the project is profitable when the discount rate is between 8 and 12 per cent.
Figure 7.8 Present value profile.
Example 7.8
For the two projects A and B the following cash flows are given:
Internal rate of return calculations show that project A gives a higher rate of return. But the choice of A or B in this case depends on the investors’ discount rate. The sum of the cash stream is higher for project B, but project A provides a higher cash flow earlier in the project. Project B will provide a higher NPV with low discount rates, while A will give higher NPV with higher discount rates. This is shown in the present value profiles in figure 7.9.
The exact intersection between the present value profiles, at r = 8.0 per cent, can be found by calculating the IRR for the investment difference B – A. As an alternative, Goal Seek can be used by setting NPV (A) – NPV (B) = 0 and changing the discount rate.
The conclusion is that project B is the more profitable with discount rates under 8.0 per cent, while project A is more profitable with discount rates over 8.0 per cent. Project A will be unprofitable for discount rates exceeding 15.9 per cent.
Figure 7.9 Present value profiles.
7.3 Payback method
A simplified approach for determining the profitability of an investment is to calculate the time it takes to earn back the investment – the payback period. One does not take into consideration the time value of money. The only consideration is to have a net cash flow equal to the investment in the shortest possible time. Using this approach, cash flows after the investment has been repaid have no value. This method has many significant weaknesses.
Excel provides no function to calculate the payback period. However, two suggestions for calculating this in Excel are shown in the two examples that follow. The first is for fixed cash flows, the second for cash flows that vary.
Example 7.9
The following investment project is given:
(-250,000, 70,000, 70,000, 70,000, 70,000, 70,000)
Calculating the payback period with fixed cash flows is easy:
An Excel model of the calculation is shown in figure 7.10. In cell B8 the net cash flow is found by multiplying the payback period (in cell B6) and the fixed net cash flow (in cell C4) and then subtracting the initial investment (in cell B4). Goal Seek is used to change the sum in cell B8 to 0 by changing the payback period in cell B6. The payback period is, as expected, 3.57 years.
Figure 7.10 Calculation of payback period using Goal Seek.
Example 7.10
The following investment project is given: (-8,000, 1,200, 1,800, 2,200, 1,800, 1,200, 900).
After four years the project has earned 1,200 + 1,800 + 2,200 + 1,800 = 7,000. There remains 1,000 to be paid before the project is paid back. The payback period is:
With variable cash flows, the Excel model will be complicated. See figure 7.11. In cells B6 to H6 the accumulated cash flow is calculated. Cells C8 to H8 are checked to see when the accumulated cash flow changes sign, using Excel’s IF function. In figure 7.11 the formula in cell G8 is shown. If the sign is changed going from cell F6 to cell G6, the year becomes registered in cell F3, at the same time that the proportion of the next year’s cash flow needed to cover the investment is added. The payback period is shown in the cell that is the closest for paying back the investment (rounded downwards). In order to accommodate situations where the investment is never paid back, the solution is presented in its own cell (B10) along with a “payback complete” test.
Figure 7.11 Calculation of payback period.
7.4 Equivalent annual income
A net present value of a project can also be presented as an equivalent annual income, which is the same as an annuity. In this case we distribute the value NPV over the project’s lifetime (in years) with identical amounts equal to NPV
If NPV > 0, then the project is profitable. Annuities are useful if we want to compare projects with different lifetimes, or want to find the optimal lifetime for an asset. Let’s take a look at some examples.
Example 7.11
Max Schmeling Ltd needs a new machine for production of electronic components. The discount rate is 12 per cent and the company can choose among four different machines with different lifespans. Investment sums and cash flows for the machines are budgeted as follows. All values are in £000.
Figure 7.12 Comparing equivalent annual incomes of different machines.
If the investment is to be made once, the four NPV-values should be compared. In that case machine B gives the highest NPV and is the most profitable.
Let us now assume that the chosen machine should be replaced repeatedly with a new one when it is worn out. Since the machines have different lifespans, we must choose an infinitely long time horizon to be able to compare them.
For each machine the NPV value can be distributed over the lifetime with the fixed sum NPV. 
With an infinitely long time horizon, these fixed sums for the machines can be compared.
Under these assumptions, machine C gives the highest fixed sum and is the most profitable.
Example 7.12
Helen Ltd invests $1,120,000 in a new production plant. The company wants to find the optimal number of years to operate the plant. The discount rate is 11 per cent. Cash inflows, outflows and sales values of the plant are budgeted as follows. All values are in $000.
a) Calculate the optimal lifetime if the plant is not to be replaced.
b) Calculate the optimal lifetime if the plant is to be replaced in perpetuity.
Figure 7.13 Calculation of optimal lifespan for a machine.
The calculations are done in Excel as shown in figure 7.13. The functions MAX and VLOOKUP are used to read optimal lifetimes based on the calculated NPVs and annuities.
a) If the plant is not to be replaced, the project with the highest NPV is the most profitable. In that case, the optimal lifetime is three years.
b) If the plant is to be replaced in perpetuity, the project with the highest annuity is the most profitable. In that case, the optimal lifetime is two years.
Problems
7-1. An investment project has the following budgeted cash flow.
a) The discount rate is 13 per cent. Calculate the net present value for the project.
b) Calculate the internal rate of return for the project.
c) Draw the NPV profile for the project.
7-2. Calculate internal rates of return and net present values for the following projects. The discount rate is 18 per cent.
7-3. Draw the present value profile for the project (–£180, £486, –£324) and find the discount rates that allow the project to be profitable.
7-4. The following investment alternatives are mutually exclusive. Discuss which one is the best alternative.
7-5. The following investment alternatives are mutually exclusive. Discuss which one is the best alternative.
7-6. A contractor has been asked to build a new road. They can do the job in either two or three years. If they chooses the two-year alternative, they will have $40,000,000 in payable costs the first year, and receive $48,000,000 the second year. If they chooses the three-year alternative, they will have $18,000,000 in payable costs the first year, $16,000,000 in payable costs the second year, and receive $44,000,000 the third year. Discuss which alternative they should choose.
7-7. Find the payback period for the following projects (cash flows in £000,000):
7-8. (All amounts in £000)
A company invests 390 in a new machine and wants to find out how many years the machine should be operated. The discount rate is 15 per cent. Payments in, payments out, and market values at the end of each year are budgeted as follows:
a) Calculate the optimal economic lifetime for the machine if it is not to be replaced.
b) Calculate the optimal economic lifetime for the machine if it is to be replaced in perpetuity.