||The problem is to choose
a portfolio from a collection of projects that generate cash
flows over time. The models available with the add-in differ
in the detail with which the cash flows are described. This page
describes two models where the primary measure is the net
present worth of the portfolio.
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
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
Both NPW and IRR describe the financial merit
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
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.
|Creating a Model
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
The capital budgeting problem will be
solved with integer programming. To create a model,
Programming add-in must be installed. The model
may be solved with either the Jensen
LP/IP Solver or the Excel
the case of the Jensen Solver the LP/IP Solver
must be installed. There is no need to create a
model, as the add-in automatically constructs the required
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
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
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
||We solve this model by
selecting the Actions item from the menu. The Actions
are described on the next page. This
calls the solver add-in, in this case the Jensen LP/IP add-in,
to find the optimum solution. The optimum portfolio
is shown below. For a budget of 650, the portfolio consists
of projects 1, 2, 4, 5, 6, and 9. The portfolio has a NPW
of 266 with an investment of 650. The internal rate of return
for the portfolio is 18%. The values on the table are rounded
to integers, but can be shown with more accuracy by increasing
the number of significant digits using the Excel format commands.
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.
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.
||One of the advantages
of using math programming models in this application is the ability
to add logical constraints relating different projects.
|IRR Goal Constraint
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
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.
|Simple Data Option
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.
|Integer but not Binary
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.
||The next page shows the
actions that can be performed on the model.