Engineering Economic Analysis

220 RATEOF RETURNANALYSIS

PW of cost of differences=PW of benefits of differences

500 =25(P/A, i,12) +150(P/F, i,6) + 100(P/F, i, 12)

The sum of the benefits over the 12 years is $550, which is only a little greater than the $500

additional cost. This indicates that the rate of return is quite low. Tryi= 1%.

500 ~ 25(11.255) + 150(0.942) + 100(0.887)

~ 281 + 141 + 89 =511

The interest rate is too low. Tryi=11/2%:

500 '!/::25(10.908) + 150(0.914) + 100(0.836)

'!/::273 + 137 + 84 = 494

The internal rate of return on theY- X increment,IRRy-X, is about 1.3%, far below the 19%

minimum attractive rate of return. The additional investment to obtain MachineYyields an...

" unsatisfactory rate of return, therefore X is the preferred alternative.

Spreadsheets and Incremental Analysis

The spreadsheet functions covered in earlier chapters are particularly useful in caIculatiDg

internal rates of returns (IRRs). If a cash flow diagram can be reduced to at most"oneP,

oneA,and/or oneF,then the RATEinvestmentfunctioncan be used. Otherwise the IRR

blockfunctionis used with a cash flow in each period..

The Excel investmentfunction isRATE(n,A,P ,F,type,guess).TheA, P,andFcannot

all be the same sign. TheF,type, and guess are optional arguments. The "type" is end or

beginning of period cash flows (forA,but notF),and the "guess" is the starting value in

the search for the IRR..

ConsideringExample 7-1, whereP= -8200, A= 2000,andn= 5, the RATEfunction

would be:

RATE(5,2000,-8200)

which gives an answer of 7.00%, which matches that found in Example 7-1.

For Example 7-2, whereP = -700, A = 100, G = 75, andn = 4, the RATE

function cannot be used, since it has no provisions for the arithmetic gradient, G. Suppose

the years (row 1) and the cash flows (row 2) are specified in columns B through E. The

internal rate of retum calculated using IRR(B2:F2) is 6.91%.

A B C D E F

1

2

Figure 7-6 illustrates using a spreadsheet to graph the present worth of a cash flow series

versus the interest rate. The interest rate with present worth equal to 0 is the IRR. The

Year^01234

Cash flow -700 100 175 250 325