The Problem

A businessman is considering the purchase of an asset that has an initial cost of $2000. The asset promises an annual return of $600. It's operating cost is $100 the first year, $150 the second, and increases by $50 in each subsequent year. The salvage value for the asset in 10 years is $400, or 20% if its initial cost. The cash flow for this situation showing the individual components explicitly is in the figure below. If the businessman's minimal acceptable rate of return is 10%, should he invest in this project?

The Project

Install the Economics add-in. Choose Add Project from the menu.

Fill in the dialog with the number of cash flows (3, one for the revenue, one for the uniform part of the operating cost, and one for the gradient part of the operating cost). Fill in the number of investments (1 for the 2000 investment at time 0). Fill in the Minimum Acceptable Rate of Return (MARR = 10%) and Life (10 years). These can be changed later. The bottom half of the dialog is used when taxes and/or inflation is to be modeled.

The add-in creates a form with some items filled in. Specify the investment in row 9 as a negative number. The Salvage is the percentage of the investment that is returned at the end of the life of the asset (20% = 400/2000). The Start value indicates when the asset is put into service and the End value indicates the end of its life.

The return is entered in row 13 as a uniform series with value 600. The first payment time (at the end of year 1) is entered in the Start cell and the last payment time (at the end of year 10) is entered in the End cell. The uniform part of the operating cost is entered in row 14 as a negative number (-100). The operating cost is increasing by an amount $50 per year. This is entered as a negative gradient (-50) in row 15. The Start cell indicates when the first non-zero payment occurs (2) and the End cell indicates when the last payment occurs. The Parameter for the gradient is the periodicity of the payments (1 means that it occurs annually). All the words and numbers in the white cells can be changed.

The yellow cells in column S are computed by formulas inserted by the add-in. These are the results of the analysis. Cell S9 is the present worth of the investment considering both the amount of the investment and the salvage. Cells S13 through S15 hold the present worths of the annual cash flow components.

Cell S2 holds the sum of the investment and cash flow present worths. The is the net present worth of the project over its life. Since the value (81.93) is positive, the investment returns more than 10%, the MARR. This implies that the investment should be acceptable to the decision maker. A negative value implies that the project does not return the MARR. Cell S3 holds the equivalent net annual worth of the project. It is also positive because it is proportional to the net present worth. Cell S4 holds the present worth over the study period. Since the study period (10) is the same as the life, this is the same as the present value over the life.

The Internal Rate of Return (IRR)

The internal rate of return is the interest rate that makes the net present worth of the project equal to zero. It is a measure of the profitability of the project. It is discovered by a binary search procedure. Selecting Compute Rates presents the dialog below. The project E6_2 is selected from the Name field.

For some projects there may be more than one solution that yields a zero net present worth. Different solutions may be found by specifying the initial guess and range of the search process. The example is called a simple investment since a single investment is followed by net revenues in the following years. This kind of investment has only one solution for the IRR.

The add-in successively changes the MARR in cell P1 until the IRR is determined. The rate is placed in cell S5 on the project definition. It is colored green to indicate that the number is computed by an algorithm. If some of the parameters of the project are changed, the yellow cells will automatically compute new values. The IRR cell will not change, however, unless the algorithm is run again.

The IRR is 11.13% for the example. The fact that it is greater than 10%, the MARR means that the project is acceptable. The IRR will always be greater than the MARR when the project net present worth is positive.

The Cash Flow

For a complicated project with several cash flow components, it is often difficult to determine the flow of cash values in the several years of the project life. For a tabular presentation choose Show Cash Flow from the menu. In the resulting dialog, choose the project in the name field. The buttons on the right select the results to be displayed.

It is often instructive to view a graph of the cash flow. Select Graph Cash Flow from the menu and select the project name in the dialog. The terms Actual and Real refer to different evaluations of the dollar amounts when inflation is considered. With no inflation, we use Actual to refer to the cash amounts at each period. The Cash Scale and Time Scale control the size of the display.

A cash flow table must have already been constructed for a project to appear on the name list.

We have illustrated the evaluation of a single project when inflation and taxes are neglected. The form holding the project data may be much more complex than the simple example presented here. A project may have several investments occurring at different times and many annual receipts and disbursements. There is no limit to the complexity the add-in can handle except the size of the worksheet and the limitations of the user's computer.

The evaluation of projects with taxes and inflation is more complex requiring additional data and more difficult computations. The add-in handles these variations using commands and dialog boxes similar to those illustrated on this page.