Using spreadsheet functions to compute arithmetic gradients

Using spreadsheet functions to compute arithmetic gradients

Eschenbach, Ted

Computerized spreadsheets are available nearly everywhere, and they are quite useful in engineering economy. Many would argue that they are essential to the modern day practice of engineering economy. However, the leading spreadsheet packages do not include functions for arithmetic gradients, although add-in packages may contain such functions. Spreadsheets are written principally for the business community, whose academic sources (such as finance texts) generally do not use arithmetic gradient functions. Thus, the lack of gradient functions should not be surprising. However, virtually every text in engineering economy includes factors for arithmetic gradients. Spreadsheet functions which do not exist can be adapted to calculations of arithmetic gradients, as shown in this technical note. These functions can be used instead of year by year tabulations. This technical note also briefly summarizes the financial functions available in three leading packages. Exceltm, Lotustm, and Quattro Protm are owned by Microsoft, Lotus, and Borland respectively. This paper uses without further definition the ANSI standard notation for engineering economy factors and quantities.

SPREADSHEET ANNUITY FUNCTIONS

The engineering economy factors that are tabulated in engineering economy and finance texts can be calculated using spreadsheet annuity functions. In tabulated engineering economy factors, i typically identifies a table; N specifies the row; and then two of P, F, and A identify a column. The cash flows occur respectively at time O, period N, and periods 1 through N. The most commonly used tables correspond to an end-of-period assumption, but tables exist for beginning-of-period and mid-period assumptions.

The corresponding annuity functions, that are summarized in Table 1, often include a Type variable, that defaults to an end-of-period assumption, but which can specify beginning-of-period cash flows. (Table 1 omitted) The power of the spreadsheets also allows direct calculations that include non-zero, values for all three of P, F, and A. However, some of the basic engineering economy factors are not available in some packages, such as Lotus.

Note that the annuity functions for Excel and Quattro Pro are considerably more powerful than those for Lotus. The important differences include the ability:

1. to calculate the internal rate of return with an A.

2. to convert between P and F.

3. to include all three of P, A, and F as non-zero values, rather than assuming that one equals 0.

4. to switch between a default end-of-period assumption, and a beginning-of-period assumption by specifying a Type.

These more powerful functions have the same sign convention and the same power as the functions built into many business calculators (such as those made by Hewlett Packard). The sign convention is to calculate the value that leads to a present worth of 0. Thus the PV (Excel) or (a)PV (Quattro Pro) of $200 per period for 10 periods is negative.

SPREADSHEET BLOCK FUNCTION

As noted by a reviewer of an earlier draft, one of the main advantages of a spreadsheet is the ability to specify cash flows on a period-by-period basis. These cash flows are analyzed by block functions that identify the row or column entries for which a present worth of an internal rate of return should be calculated. In Excel’s documentation these cells are identified as values, while in the other two packages the terminology of range is used.

For cash flows involving one P, E and A this block approach seems be inferior to the annuity functions. However, this is a conceptually easy approach for gradients, as shown by the following example. Suppose a project’s revenues begin at $1000 in the first year, with an annual increase of $300 per year over its 5-year life. Then as summarized in the spreadsheet format of Table 2, the revenues use increase or range from $1000 to $2200. (Table 2 omitted)

If an interest rate of .08 is assumed, then the present worth of the cash flows can be calculated as follows. Excel’s function is NPV (.08,B2:F2). For Lotus and Quattro Pro the function is (a)NPV(.08, B2..F2). For comparison purposes and to support numerical checks the answer using engineering economy factors is found as follows.

PW = 1000 . (P/A, 8%, 5) + 300 (P/G, 8%, 5)

= 1000 . 3.993 + 300 . 7.32 = 6205

If the investment cost were inserted at period 0, then the internal rate of return could be calculated using IRR (values, guess) or (a)IRR (guess, range). It is worth emphasizing that the two common block functions for present worth and internal rate of return make different assumptions about the range of years included (a)NPV (i, range) and NPV(i, values) assume year 0 is NOT included, while (a)IRR (guess, range) and IRR (values, guess) assume year 0 is included. These functions require equally spaced cash flows. The cash flows for 1 to N are assumed to be end-of-period flows.

BASIC FACTOR TO FUNCTION CONVERSIONS

Before developing the conversions for gradient factors, it seems worthwhile to present the conversions for the basic P, A, and F factors. Table 3 notes the conversion of these functions to the factors that we are used to using. (Table 3 omitted)

CONVERTING GRADIENTS TO FUNCTIONS

The algebra far verifying Equations 1 and 2 is straightforward and thus it is omitted.

(P/G,i,N) = (P/A,i,N) – N . (P/F,i,N)]/i (1)

(A/G,i,N) = (1 – N)/(F/A,i,N)]/i (2)

Converting Eq. 2 into spreadsheet functions is easy, but somewhat tedius within all three packages. The(A/G,i,N) function is [1 – N/(a)FV(1,i,N)]/i for Lotus and Quattro Pro. For Excel only the parameters of the function are rearranged for [1 – N/FV(i,N,-1)]/i.

The present worth functions for Excel or Quattro Pro are powerful enough to simplify Eq. 1 as follows. Similar conversions are not possible with the more limited functions available in Lotus.

Excel Quattro Pro (Equations omitted)

These functions also support a simpler solution for converting Eq. 2. (Equations omitted)

On the other hand, for Lotus the conversion is much more awkward. (Equations omitted)

CALCULATIONS FOR THE TYPICAL COMBINED A AND G SERIES

Now to calculate the present worth of the typical combined A and G series, the factor is found using Eq. 3 or 3′. Note that an F term could easily be included in either formulation for Excel or Quattro Pro. Similar series are much more awkward for Lotus so that use of the block formulation is more appropriate.

PRESENT WORTH

Excel: PV(i,N,-G,N.G)/i + PV(i,N,-A) (3)

Quattro Pro: (a)PVAL(i,N,-G,N. G)/i + (a)PVAL(i,N,-A) (3)

or

Excel: PV(i,N,-G/i-A,N.G/i) (3′)

Quattro Pro: (a)PVAL(i,N,-G/i-A,N-G/i) (3′)

To calculate the equivalent annual worth of the combined A and G series, the factor can be found using Eq. 2 for Lotus or by using the following equations for Excel and Quattro Pro.

EQUIVALENT ANNUAL WORTH

Excel: A + PMT(i,N,-PV(i,N,-G,N.G)/i (4)

Quattro Pro: A + (a)PAYMT(i,N,-(a)PVAL(i,N,-G,N.G)/i) (4)

or equivalently

Excel: A + PMT(i,N,PV(i,N,G,-N.G)/i) (4′)

Quattro Pro: A + (a)PAYMT(i,N,(a)PVAL(i,N,G,-N.G)/i) (4′)

To apply these equations to our example, both A at 1000 and G at 300 are positive. With N equal to 5 and i equal to 8%, these equations yield the correct answers of a PW of 6205 and an EAW of 1554.

CONCLUSION

The development of the formulas included here is not a major breakthrough. However, if one is doing sensitivity analysis, using formulas such as these can be much easier than having to build complete year by year tables of gradient cash flows.

The author also hopes that this note will stimulate others to publish the spreadsheet shortcuts that they have found most useful.

REFERENCES

[1] Bussey and Eschenbach, The Economic Analysis of Industrial Projects, 2nd, Prentice-Hall, 1992, pp. 64-65.

BIOGRAPHICAL SKETCH

TED G. ESCHENBACH, P.E., is the founding and current editor of the Engineering Management Journal for the American Society of Engineering Management. He has served on the faculty of the University of Alaska Anchorage since 1975 with sabbaticals at the U.S. General Accounting Office (faculty fellow), the Naval Postgraduate School, and the Robert B. Koplar Professor of engineering management at the University of Missouri-Rolla ((1988-1990). His research areas include engineering economy, forecasting, and strategic management of technology. His Cases in Engineering Economy was published by Wiley in 1989. His second edition of Lynn Bussey’s advanced text was published by Prentice-Hall in 1992. His introductory text Engineering Economy: Applying Theory to Practice will be available from irwin in Fall 1994. Dr. Eschenbach holds a B.S. from Purdue University and the M.S. in operations research and Ph.D. in industrial engineering from Stanford University.

Copyright Institute of Industrial Engineers, Inc. Spring 1994

Provided by ProQuest Information and Learning Company. All rights Reserved.