An example with nine projects is below. Each cash flow is characterized by an initial investment (a negative cash flow), followed by a series of annual net incomes (positive cash flows), and finally a salvage value (normally a positive cash flow). Each project has a life during which the incomes are received. The payment of the salvage value is a the end of the life. The projects may have different lives. For convenience we enter investments as positive numbers, but a positive investment implies a negative cash flow at time zero. All the projects in the example have positive parameters, but except for the life, the other parameters might be negative. For example, it is not unusual to have a negative salvage when the disposal of an asset results in an expense rather than a revenue.

The question of accepting or rejecting projects that require investments is ongoing for every modern business. It is common, however, to have a formal capital budgeting procedure, perhaps annually, where all major opportunities are compared and funding decisions are made. Since the problem is so ubiquitous it is the subject of many books and articles in business and engineering finance. A number of simple quantitative measures have been suggested for evaluation. Two measures have been computed for the projects of our example. The Net Present Worth (NPW) is the discounted value of the cash flow computed with a specific interest rate. The interest rate used to compute the NPW is called the minimal acceptable rate of return (MARR).

The Internal Rate of Return (IRR) is the value of the interest rate that makes the NPW equal to 0. The asterisk on the interest rate in the expression indicates that it is the value of i that solves the equation.

We assume that the projects are independent in the sense that the evaluation of a project does not depend on whether other projects are selected. We will allow constraints that will accommodate restrictions on the allowable portfolios. The table below shows the IRR values and the NPW values computed with 10%. NPW and IRR values shown are rounded to integer values.

Both NPW and IRR describe the financial merit of projects. Business people tend to prefer IRR because interest rates are familiar. We will use the NPW measure because it is commonly used for capital budgeting decisions and is more easily included in a math programming model. Also the IRR is misleading when the total investment in the portfolio is restricted by a strict budget constraint.

We describe here a mathematical programming approach for modeling the capital budget decisions. This approach allows the decision question to be addressed in a formal and objective manner. The models allow the specification of constraints on decisions, not possible with other decision analysis methods. The method is implemented in the Capital Budget Excel add-in. Figures in this section are screen shots from Excel.

The capital budgeting problem is to select from a set of alternative projects a portfolio of projects that provides the greatest economic return while not exceeding a budget on the amount of the total initial capital investment. To setup and solve such a problem, select Create Model from the Capital Budget menu.

The capital budgeting problem will be solved with integer programming. To create a model, the Math Programming add-in must be installed. The model may be solved with either the Jensen LP/IP Solver or the Excel Solver. In the case of the Jensen Solver the LP/IP Solver add-in must be installed. There is no need to create a model, as the add-in automatically constructs the required model. The figure shows the OR/MM menu with the two add-ins installed. If the Excel Solver is used, that add-in must be installed. Be sure to open and close the Excel Solver dialog before attempting to create a model. This will establish a connection to the add-in. The model will not be created if this step is omitted. The Excel Solver appears on the Excel Tools menu.

When selected, the Capital Budgeting item presents the dialog shown below. The name is important because a worksheet is created with this name and a number of ranges on the worksheet have names with this prefix. The name must be different than other named ranges in the workbook and cannot be changed once the worksheet is created. Names cannot include spaces or punctuation and must begin with a letter.

The dialog accepts the number of projects and the MARR for the analysis. The MARR may changed on the worksheet. The Extra Variables and Extra Constraints allows an expansion of the mathematical programming model to incorporate logical constraints on the portfolio and other features that an analysis may require.

The model assumes that the decision variables defining the portfolio are integer. The Binary Decisions button further restricts the values to 0 or 1 indicating that a project will be selected or not. When this button is not selected the upper bounds on the variables are set to larger values allowing multiple copies of an item to be selected.

When the Include Variance button is checked, the add-in constructs a model where risk is measured through the statistical variance of the project returns. The risk options are described later in this unit.

The Simple Data button on the dialog creates a model that does not compute the NPW for the projects. Rather the data can be entered directly by the user in the cells provided. Aspects of the analysis that depend on the MARR or the IRR are not included with this option.

The Make Random Problem adds randomly generated data for easy review of the features of the model.

On clicking OK, the program creates a model using the Math Programming add-in. The initial worksheet for a 9-project problem is shown below. The top part of worksheet holds the mathematical programming model. We describe the formal math programming model later on this page. The capital budgeting data starts at row 17 and is illustrated for the example below. Cells colored yellow hold formulas that should not be changed by the user. Cells colored white hold numbers that are input data that can be changed. Cells colored green can be changed by the user, but they will be replaced by the results of algorithm computations.

The goal of the optimization is to find the portfolio that maximizes the Net Present Worth (NPW) of the portfolio. This is the sum of the NPW values of the projects in the portfolio. Cell H18 holds the interest rate with which the present worth values are calculated. Traditionally this value is called the Minimum Acceptable Rate of Return or MARR. We restrict the sum of the initial investments for the portfolio to be no greater than the budget specified in cell K20. The MARR and budget can be changed.

Cell H19 holds a formula that computes the sum of the NPW values for the portfolio. Its yellow color indicates that the cell holds a formula that should not be changed by the user. The initial solution has no projects selected, so the cell evaluates to zero. Cell H20 holds the total investment for the portfolio. Again this is a calculated value that is zero for the initial solution. Cell H21 holds the Internal Rate of Return (IRR) for the portfolio. Its green color indicates that the program will fill this cell with the results of an algorithm. When no projects are selected, the IRR has no meaning.

Starting in row 23 the available projects are defined. For this analysis we use a project definition that is often sufficient for capital budgeting problems. Each project has a name, an initial investment, an annual net income that is assumed constant throughout the project's life, a salvage value that is returned at the end of the life, and a specified number of years, the life, when the project is retired. These values are entered in rows 24 through 29. Row 30 holds formulas that compute the NPW of each project using the Excel financial function PV. Row 31 holds formulas that compute the IRR of each project using the Excel financial function RATE.

Row 25 holds the solution to the portfolio problem. Initially the values are all zero, but the analyst can vary these values. When the problem is solved, the solution is provided by the mathematical program solver. The green color of the cells in row 25 indicates that the computer fills in the solution value.

The problem described can be formulated as a one constraint 0-1 integer programming model shown below. This problem has the form of a simple knapsack problem.

Rows 1 to 15 hold the model created by the Math Programming add-in. These rows are initially hidden from the user to focus on the data values in the following rows, but this is the model actually solved by the solver add-in. Unless extra rows and columns are provided to hold extra constraints and variables, it is not necessary for the user to interact with this model.

The objective coefficients in row 12 are the NPW values computed in row 30. There is one constraint in row 15 holding the budget constraint. The entries in this row are automatically linked by formulas to the data in row 26 and the budget value in cell K20.

Note the the solution depends on the MARR and the budget. Although it is probable that projects with a high IRR value will be selected, this is not always the result when the goal is to maximize the NPW. For the example, Project 3 has the greatest IRR, but it is excluded from the solution.

One might ask, why was project 9 included in the portfolio with an IRR value of 14%, while project 3 was not included with an IRR value of 26%? Both have the same initial investment of 50, so either could have been included with no change in the total investment. This provides a good example of why the IRR is not a good measure in this application. Although project 3 has a high return, its life is only two years, while project 9 has a lower return but maintains that return for 10 years. The IRR measure for project 3 uses only the results of the first two years of the time interval. To maintain a 26% return over the 10 years of analysis, project 3 must be replaced by a project equally profitable every two years. A more conservative estimate is that money returned by a project will continue to earn at a rate equal to the MARR. This is the assumption of the present worth analysis.

Integer programming allows a variety of logical constraints to be imposed upon the solution. For example we might require projects 4 and 5 to be mutually exclusive, that is, only one may be chosen. Also we might require that if project 9 is selected, project 8 must also be selected. With binary variables it is easy to impose these and a variety of other logical constraints.

We create a model with two extra constraints. The project data is the same as previously described but the math programming model is below. We add the constraints directly on the model. The figure holds the solution after the problem has been solved. The objective value (255.34) is smaller than the optimum obtained previously (265.84) because of the added constraints.

It may be that an analyst would like to require that the portfolio internal rate of return (IRR) be at least some specified value. We call this the IRR Goal. To impose this constraint we compute the NPW of each project using the IRR Goal as the interest rate. If the sum of these NPW values is greater than zero for the portfolio, the IRR for the portfolio is greater than the IRR Goal. With this constraint added, the model is as below.

To impose this constraint, click the IRR Goal Constraint box on the capital budgeting dialog. The model has a new cell at K22 holding the IRR Goal. The NPW values for the goal IRR are computed in row 32. The goal is satisfied if the goal NPW values for the selected projects sum to a value greater than zero. We see this constraint in row 16 of the model.

The solution to the model with a budget of 650 provides a different selection than without the goal constraint. It has a higher IRR (21%) than before (18%) , but a lower NPW (255.34) than before (265.84). Coincidentally, this is the same solution obtained when the constraints were added.

We have used a very simple model for the projects with a single investment followed by a uniform series of net incomes and then a salvage value at the end of life. For analyses with very many projects, this level of detail may be sufficient. The approach will also work with more complicated projects created with the Add Project command of the Economics add-in. The Project Definition may be arbitrarily complex with several investments and a variety of cash flows. The economics add-in computes the NPW for complicated problems and the results can be inserted directly into the capital budgeting worksheet.

The Simple Data option presents a data form containing only the NPW row. These values must be computed elsewhere and inserted into the form. Since the MARR does not enter into the calculation of the NPW, features related to interest rates are not included in the model.

When the Binary button on the dialog is not clicked, an integer programming model is constructed that allows more than one of each project to be selected. New rows are added to the data region to accommodate lower and upper bounds on the decisions. The figure below shows the decisions bounded by 2. The optimum solution with a 650 budget shows two each of projects 1, 5 and 6, with 1 of project 9.

The index row for the projects now include the "I-" prior to the project number. If the "I-" prefix is removed, the model treats the variable as continuous rather than integer. When all variables are continuous, the model becomes a linear program.