In this section we review some simple operations of electronic spreadsheets, using NPV and IRR calculations as illustrations. The reader who is familiar with the use of spreadsheets in financial analysis may wish to skip this section.
Consider Figure 3.2 which illustrates a simple NPV calculation using an Excel© spread-sheet. For the reader who is not already familiar with a spreadsheet, the best way to understand what a spreadsheet is is to think of it as nothing more than a very large calculator that is organised in rows (1, 2, 3 … etc.) and columns (A, B, C, … etc), forming a matrix of cells, each with its own address (A1, B3, X75, … etc). One important difference between a calcula-tor and a spreadsheet is that you can use any given cell in a spreadsheet for a variety of operations. For instance you could use it to enter plain text. In cell A1 we have written the text Example 1(a). We could have entered a whole sentence as with a word processor.
In this spreadsheet we have also set up the cash flow of two projects used as examples earlier in this Chapter in the section on Discounting and the Time Value of Money: Project A and Project B. You will notice that column A contains a set of labels. In other cells we have 54 Benefit-Cost Analysis
Figure 3.2 Spreadsheet Presentation of DCF Calculation
Formula bar
Entering numerical values
Entering simple formulae Entering a label
entered numbers. In row 3 we have numbered the years: year 0 in B3, year 1 in C3, etc. In row 4 we have entered the net cash flow for project A: –100 in B4, 50 in C4, and so on. In row 5 we have entered the NCF for project B. (Note that we have chosen to set up the cash flows in rows running from left to right rather than in columns from top to bottom. Either method will work but we believe, from our own practical experience, that setting-up a spreadsheet in rows makes it easier to handle the more complicated sorts of analyses we will be doing later.)
Having set up the two cash flows like this we are now ready to compute their NPVs. To do this we use another function of the spreadsheet cell: we enter a formula into a cell. You can enter almost any formula into a cell. For instance, you could simply instruct the spreadsheet to add together, say, “50” and “30” by entering the formula “=50+30”. (Note that when using Excel©we always begin with the “=” sign (or some other operator such as a “+” sign) when entering a formula. This is to distinguish the entry of a formula from the entry of a label.)
Alternatively, instead of using actual values in the formula, you could instruct the spreadsheet to add together the contents of two cells, by giving their reference addresses. For example, if we entered the formula “=C3+C4” into cell G6, then the amount “80” would appear in that cell; i.e. 50+30=80. Although you do not see the formula you have entered in the cell itself, you can see what it is by placing the cursor on the cell and looking in the
“window” or Formula Bar near the top of the spreadsheet. In the spreadsheet shown in Figure 3.2 the cursor is positioned on cell B12, so the formula in that cell appears in the Formula Bar.
In this simple example we have also entered the discount factors (at a 10% discount rate) for each project year in row 6. To derive the NPV for each project we firstly calculate the discounted cash flow by multiplying each year’s cash flow by the corresponding discount factor.
This is shown in rows 8 and 9 for projects A and B respectively. For instance, in cell C8 we have entered the formula “=C4*C6”: i.e. 50 × 0.909. The result displayed in C8 is “45.45”. If we do this for each cell of the cash flow we can then find the NPV by adding up the values in each cell. To do this we place another formula in cells B11 and B12 instructing the spread-sheet to add up the cells B8, C8, D8 and E8 to get the NPV of A, and B9 through to E9 for project B. The solutions are shown in cells B11 and B12: +$1.02 and +$1.99 respectively.
Note that there are two types of formula we could have used in cells B11 and B12: a
“self-made” formula or a “built-in formula”. If we had used a self-made formula we would have written into cell B11, “=B8+C8+D8+E8”. (Note that the symbol “ * ” is used as the multiplication sign, “ / ” is used as the division sign, and “ ^ ” is used as the exponent sign.) When there are a lot of cells to add up this becomes a laborious exercise and one that is prone to careless error. The alternative is to use one of the built-in formulae. In this case we use the “SUM” formula which is displayed in the Formula Bar at the top of the spreadsheet. This tells us that in cell B12 we have entered the formula “=SUM(B9:E9)” where the colon sign indicates that all other cells in between B8 and E8 are to be included in the summation. With built-in formulae the spread-sheet is programmed to know what functions to perform when it reads certain words. In this instance, the word “SUM” instructs it to add-up. Similarly, “AVERAGE” would instruct it to find the mean; “STDEV” would instruct it to find the standard deviation, and so on. Informa-tion about the range of formulae available and their operaInforma-tion can be found in the “Help”
section of the spreadsheet program.
What we have just done demonstrates the use of some of the very basic functions of a
Investment Appraisal: Decision-Rules 55
spreadsheet. In practice you would not need to enter in the discount factors as we have done in row 6. We could have written our own formulae for the discount factor instead and let the spreadsheet calculate the actual figure, but even this is unnecessary because the spreadsheet has built-in formulae for all the DCF calculations you need to use, including the NPV and IRR formulae.
To demonstrate the use of the built-in NPV formula, look at Figure 3.3 where we show the same NPV calculations as before, but using the built-in formula. Note that we do not have a row of discount factors or a row showing the discounted cash flow of each project. Instead we simply enter one formula into cells B8 and B9 to calculate the NPVs for projects A and B respectively. The first thing you will notice is that the answers are not exactly the same as those shown in Figure 3.2. The reason is due to rounding differences. When we entered the discount factors into the spreadsheet in Figure 3.2, we rounded them off to three decimal places. As the spreadsheet is capable of using very many more decimal places in its computa-tions, the solutions in Figure 3.3 are more precise.
The first part of the formula for the NPV calculation entered into cell B8 is shown in the Formula Bar at the top of the spreadsheet: “=NPV(0.1,C4:E4)+B4”. The “0.1” tells the program to use a 10% discount rate, then follows a comma, and then “C4:E4” tells it to 56 Benefit-Cost Analysis
Figure 3.3 Using Built-in Spreadsheet Formulae
The built-in NPV formula (adapted) as entered in cell B8
discount the contents of cell C4 (year 1) through to cell E4 (year 3). Note that we have to include “+B4” in the formula to instruct it to include the undiscounted year 0 amount of –$100.
The reason we have to do this is that the spreadsheet does not use the same convention that we do when numbering project years. Our convention is to treat all project costs and benefits accruing in the initial year as year 0, which therefore remain undiscounted in our NPV calcula-tions. The spreadsheet, on the other hand, is programmed to treat the earliest year of the cash flow as accruing one year later and therefore will always discount the initial year of the cash flow. For this reason we have to adapt the formula by adding back the year zero cash flow on an undiscounted basis, and beginning the NPV computation for our project in year 1.
If we change the discount rate in the NPV formula, the solution in the corresponding cell will automatically change. In cells B11 and B12 we have entered the identical formula as in B8 and B9 except we have set the discount rate at 15%: e.g. “=NPV(0.15,C4:E4)+B4”. It should be noted that all entries in column A of the spreadsheet are labels. We have typed into cell A8, for example, “NPV of A (10%) =”. This is not a formula! It is only there for the user’s convenience. If a label were accidentally used in performing a calculation, the spreadsheet will treat it as a zero. This also applies where numbers have been entered as part of a label as in the case of “10%” in cells A8 and A9.
The discount rate is a variable in most benefit-cost analysis as the analyst will generally want to see how the NPV of a project changes with different discount rates. To construct an NPV curve you will need to recalculate a project’s NPV at several discount rates. To simplify undertaking this sort of sensitivity analysis there is another “trick” we use when designing a spreadsheet. Instead of entering the actual values in the NPV formula we can enter a reference to another cell somewhere else in the spreadsheet. This cell might contain the discount rate, for example, so, whenever we change the value of the discount rate in that cell, the value of the NPV will change wherever in the spreadsheet there is an NPV formula that refers to that cell for the discount rate. For instance, if we placed the discount rate in cell B10 and then entered the value “0.1” into that cell, we could then change the formulae in cells B8 and B9 to read
“=NPV(+B10,C4:E4)+B4” and “=NPV(+B10,C5:E5)+B5” respectively. The results will be the same as we have in Figure 3.3 but if we were to change the value in cell B10 to 0.15, the NPVs of projects A and B, in cells B8 and B9 respectively, would immediately change to the values already computed in cells B11 and B12.
Similarly, what we enter into the net cash flows in rows 4 and 5 could also be references to other cells in the spreadsheet rather than actual values. For instance, the net cash flow in each year is likely to be derived from other calculations consisting perhaps of a number of dif-ferent costs and benefits. These calculations can be performed elsewhere in the same spreadsheet, and then, instead of re-entering the values derived from these calculations we would simply refer to the cells in which the solutions to these “working calculations” appear.
An example of this is shown in Figure 3.4 where we have inserted a “working table” in the spreadsheet, still using the same example as previously. In the working table, rows 21 and 22 contain simple arithmetic formulae that derive the net cash flows for projects A and B from the raw data in the rows above. (These values could themselves be based on calculations else-where in the spreadsheet.) In the final table in the upper part of Figure 3.4, that we now label
“Net Cash Flow”, we no longer have any values in any of the cells. The cells in that table
Investment Appraisal: Decision-Rules 57
contain only references to cells in the working table. Similarly, the NPV formulae in cells B7 and B8 contain a reference to cell B10 where we have entered “15%”. (Remember, the rest of the NPV formula is also in the form of references to the appropriate rows in the “Net Cash Flow” table above.)
Finally, spreadsheets also contain built-in formulae for the IRR. In cells E7 and E8 we have entered the formulae for the IRR of projects A and B respectively. The IRR formula for project A is displayed in the Formula Bar at the top of the screen in Figure 3.4, as
“=IRR(B4:E4,0.1)”. Notice two things here. First, there is no need to leave out of the formula the cash flow in year zero and then add it back in as we had to with the NPV formula. Second, we have had to include a “trial” or “prompt” discount rate in the formula. The program needs this to know where it should begin the iteration process to derive the IRR (this process was illustrated in Figure 2.7 of Chapter 2). In this case it makes no difference what rate we enter.
You can try changing it yourself to, say, 5% or 20% and you will always get the same answer, because, as we learned earlier, a cash flow with only one change in sign, as we have here, has only one IRR. There are exceptions however where there is more than one solution, as seen in Chapter 2. In this case entering a different “trial” discount rate in the formula could produce a 58 Benefit-Cost Analysis
Figure 3.4 Referencing within the Spreadsheet
All entries in this table are references to cells in the ‘Working Table’ below
Using the built-in NPV and IRR formulae
All project data needed to derive costs and benefits should be entered in a
‘Working Table’ like this
different IRR. (Note that some spreadsheets are programmed to report an error when an attempt is made to use the IRR formula when there is more than one positive IRR.)
Until now, when using the built-in formulae such as “NPV” and “IRR”, we have needed to type out the formulae and enter the appropriate parentheses, values, cell references, commas, etc. for the spreadsheet to perform the desired calculation. The spreadsheet contains very many formulae that are extremely useful and time saving for the project analyst. These include numerous financial formulae for performing other commonly used calculations in financial analysis, such as interest and repayments on loans or depreciation allowances, as well as statistical formulae and others. It is not necessary to have to memorize or even look up the appropriate words and format for all these formulae so that each time a function is to be used you may enter the details correctly. In the spreadsheet’s main toolbar at the top of the screen there is a function button (fx) which, when entered, displays all available formulae. Once you have selected the one you wish to use, this can be pasted into the appropriate cell in the spreadsheet. These processes are illustrated in Figure 3.5 for the built-in IRR formula.
Investment Appraisal: Decision-Rules 59
Figure 3.5 Selecting and Pasting a Built-in Formula
Step 2: Select type of formula wanted
Step 3: Select desired built-in formula and press “OK”
Brief description of formula and its format Step 1: Select
“Paste Function”
button on toolbar
Further Reading
One example of a book containing discussion of investment decision-rules is H. Bierman and S. Smidt, The Capital Budgeting Decision (Macmillan, 1971). Further information about the operation of spreadsheets can be obtained from the relevant manual, such as Microsoft Excel User’s Guide. See also Boardman et al., Cost-Benefit Analysis: Concepts and Practice (Prentice Hall, 2001), Ch. 6.
Exercises
1. (a) What is meant by the term “mutually exclusive projects”?
(b) Explain why the IRR decision-rule could give the wrong result when comparing mutually exclusive projects.
2. The following net cash flows relate to two projects:
NET CASH FLOWS (IN $1,000)
YEAR 0 1 2 3 4 5 6
PROJECT A –60 20 20 20 20 20 20
PROJECT B –72 45 22 20 13 13 13
(i) Calculate the NPVs for each project, assuming 10% cost of capital.
(ii) Assuming that the two projects are independent, would you accept them if the cost of capital is 15%?
(iii) What is the IRR of each project?
(iv) Which of the two projects would you prefer if they are mutually exclusive, given a 15% discount rate?
3. Using a spreadsheet generate your own set of Discount and Annuity Tables for, say, all discount rates between 1% and 20% (at 1 percentage point intervals) and for time periods 1 to 30 (at one time-period intervals), as well as time periods 50 and 100. You should generate these tables by inserting the numbers for the time periods in the first column of each row and the discount rates in the first row of each column, and then inserting the appropriate formula into one cell of the Table – year 1 at 1% – and then copying it to all other cells in the matrix. (Hint: Do not forget to anchor the references to periods and discount rates using the “$” symbol.)
4. A firm has a capital budget of $100 which must be spent on one of two projects, each requiring a present outlay of $100. Project A yields a return of $120 after one year, whereas Project B yields $201.14 after 5 years. Calculate:
(i) the NPV of each project using a discount rate of 10%;
(ii) the IRR of each project.
What are the project rankings on the basis of these two investment decision-rules?
(iii) Suppose that you are told that the firm’s reinvestment rate is 12%, which project should the firm choose?
60 Benefit-Cost Analysis
5. A firm has a capital budget of $100 which must be spent on one of two projects, with any unspent balance being placed in a bank deposit earning 15%. Project A involves a present outlay of $100 and yields $321.76 after 5 years. Project B involves a present outlay of $40 and yields $92 after one year. Calculate:
(i) the IRR of each project;
(ii) the B/C ratio of each project, using a 15% discount rate.
What are the project rankings on the basis of these investment decision-rules?
(iii) Suppose that if Project B is undertaken its benefit can be reinvested at 17%; what project should the firm choose? Show your calculations (spreadsheet printout is acceptable as long as entries are clearly labelled).
6. A firm has a capital budget of $30,000 and is considering three possible independent projects. Project A has a present outlay of $12,000 and yields $4, 281 per annum for 5 years. Project B has a present outlay of $10,000 and yields $4,184 per annum for 5 years.
Project C has a present outlay of $17,000 and yields $5,802 per annum for 10 years. Funds which are not allocated to one of the projects can be placed in a bank deposit where they will earn 15% (the discount rate for the project).
(a) Identify six combinations of project investments and a bank deposit which exhaust the budget.
(b) Which of the above combinations should the firm choose:
(i) when the reinvestment rate is 15%?
(ii) when the reinvestment rate is 20%?
Explain your answer and show your calculations (spreadsheet printout is acceptable as long as entries are clearly labeled).
7. A public decision-maker has a budget of $100 which must be spent in the current year.
Three projects are proposed, each of which is indivisible (it is not possible to undertake less than the whole project) and non-reproducible (it is not possible to construct two versions of the same project). The discount rate is 10% per annum. The project benefits and costs are summarized in the following Table:
Project Cost ($) Benefits ($)
Year 0 Year 1 Year 2
A 30 40 0
B 30 0 50
C 70 0 100
(i) Work out the Net Present Value (NPV), Internal Rate of Return (IRR) and Benefit-Cost Ratio (BCR) for each project;
(ii) Rank the projects according to the NPV, IRR and BCR investment criteria;
(iii) Which projects should be undertaken to spend the budget:
(a) if the reinvestment rate is 12% per annum;
(b) if the reinvestment rate is 18% per annum?
Investment Appraisal: Decision-Rules 61
6 2