122
bre44380_ch05_105-131.indd 122 09/02/15 04:05 PM
● ● ● ● ●
USEFUL SPREADSHEET FUNCTIONS
❱ Spreadsheet programs such as Excel provide built-in
functions to solve for internal rates of return. You can
find these functions by pressing fx on the Excel toolbar. If
you then click on the function that you wish to use, Excel
will guide you through the inputs that are required. At
the bottom left of the function box there is a Help facility
with an example of how the function is used.
Here is a list of useful functions for calculating inter-
nal rates of return, together with some points to remem-
ber when entering data:
∙ IRR: Internal rate of return on a series of regularly
spaced cash flows.
∙ XIRR: The same as IRR, but for irregularly
spaced flows.
Note the following:
∙ For these functions, you must enter the addresses of
the cells that contain the input values.
∙ The IRR functions calculate (at most) only one IRR
even when there are multiple IRRs.Spreadsheet Questions
The following questions provide an opportunity to
practice each of the above functions:- (IRR) Check the IRRs for project F in Section 5-3.
- (IRR) What is the IRR of a project with the follow-
ing cash flows: - (XIRR) What is the IRR of a project with the
following cash flows:
(All other cash flows are 0.)Internal Rate of Return
C 0 C 1 C 2 C 3- $5,000 +$2,200 +$4,650 +$3,330
C 0 C 4 C 5 C 6- $215,000 . . . +$185,000 +$85,000 +$43,000
Soft Rationing Many firms’ capital constraints are “soft.” They reflect no imperfections
in capital markets. Instead they are provisional limits adopted by management as an aid to
financial control.
Some ambitious divisional managers habitually overstate their investment opportunities.
Rather than trying to distinguish which projects really are worthwhile, headquarters may find
it simpler to impose an upper limit on divisional expenditures and thereby force the divisions
to set their own priorities. In such instances budget limits are a rough but effective way of deal-
ing with biased cash-flow forecasts. In other cases management may believe that very rapid
corporate growth could impose intolerable strains on management and the organization. Since
it is difficult to quantify such constraints explicitly, the budget limit may be used as a proxy.
Because such budget limits have nothing to do with any inefficiency in the capital market,
there is no contradiction in using an LP model in the division to maximize net present value