Engineering Economic Analysis

124 MORE INTERESTFORMULAS

Excel Function

NPV(i,values)

IRR(values,guess);

guess argument is optional

Excel's IRR function can be used to find the interest rate for a loan with irregular payments

(other applications are covered in Chapter 7).

These block functions make different assumptions about the range of years included.

For example, NPV(i,values) assumesthat Year0 is NOT included, while IRR(values,guess)

assumes that Year 0 is included. These functions require that a cash flow be identified for

each period. Youcannot leavecells blank even if the cash flowis $0. The cash flowsfor 1ton

are assumed to be end-of-period flows. All periods are assumed to be the same length of time..

Also the NPV functions returns the present worth equivalent to the cash flows, unlike

the PV annuity function, which returns the negative of the equivalent value.

For cash flowsinvolvingonly constant values ofP, F,andAthis block approach seems

to be inferior to the annuity functions. However, this is a conceptually easy approach for

more complicated cash flows, such as arithmetic gradients. Suppose the years {row 1) and

the cash flows (row 2) are specifiedin columns B through E.

Economic Criteria

Net present value

Internal rate of return

Values for Periods

1 ton

Oton

A B C D E F

1

2

If an interest rate of 8% is assumed, then the present worth of the cas~ .flowscan be

calculated as =B2+NPV(.08,C2:F2), which equals $4172.95. This is the present worth

equivalent to the five cash flows, rather than the negative of the.present worth equivalent

returned by the PV annuityfunction. The internal rate of return calculatedusing IRR(B2:F2)

is 14.5%.Notice how the NPV function does not include the Year 0 cash flow in B2, while

the IRR function does.

· PW=B2+NPV(.08,C2:F2)

· IRR = IRR(B2:F2)

NPV range without Yearq

IRR range with Year0.

Using Spreadsheets for Basic Graphing

Often we are interested in the relationship between two variables. Examples include the

number and size of payments to repay a loan, the present worth of an M.S. degree and how

long until we retire, and the interest rate and present worth for a new machine. This kind of

two-variable relationship is best shown with a graph.

As we will show in Example 4-26, the goal is to place one variable on each axis of the

graph and then plot the relationship. Modem spreadsheets automate most steps <;>fdrawing

a graph, so that it is quite easy. However, there are two very similar types of chart, and

we must be careful to choose thexychart and not thelinechart. Both charts measure the

yvariable, but they treat thexvariable differently. Thexychart measures thexvariable;

thus itsxvalue is measured along thexaxis. For thelinechart, eachxvalue is placed an

equal distance along thexaxis. Thusxvalues of 1, 2,4,8 would be spaced evenly,rather

Year 0 1 2 3 4

Cash flow -25,000 6000 8000 10,000 12,000