Using Spreadsheets for Present Worth Analysis

Spreadsheet- or calculator-based evaluation of equal-life, mutually exclusive alternatives can be performed using the single-cell PV function when the annual amount A is the same. The general format to determine the PW is

It is important to pay attention to the sign placed on the PV function in order to get the correct answer for the alternative’s PW value. The spreadsheet function returns the opposite sign of the A series. Therefore, to retain the negative sense of a cost series A, place a minus sign immediately in front of the PV function. This is illustrated in the next example.

Cesar, a petroleum engineer, has identified two equivalent diesel-powered generators to be purchased for an offshore platform. Use i = 12% per year to determine which is the more economic. Solve using both spreadsheet and calculator functions.

Spreadsheet: Follow the format in Equation (4-3) in a single cell for each alternative. Figure (4-4) shows the details. Note the use of minus signs on P, the PV function, and AOC value. Generator 2 is selected with the smaller PW of costs  (numerically larger value).

Calculator: The function and PW value for each alternative are:

Generator 1: -80000 - PV(12,3,-30000,15000)           PW1 = $-141,378

Generator 2: -120000 - PV(12,3,-8000,40000)           PW2 = $-110,743

As expected, the PW values and selection of Generator 2 are the same as the spreadsheet solution.

Figure (4-4): Equal-life alternatives evaluated using the PV function, Example (4-5).

When different-life alternatives are evaluated, using the LCM basis, it is necessary

to input all the cash flows for the LCM of the lives to ensure an equal-service evaluation. Develop the NPV function to find PW. If cash flow is identified by CF, the general format is

It is very important that the initial cost P not be included in the cash flow series identified in the NPV function. Unlike the PV function, the NPV function returns the correct sign for the PW value.

Continuing with the previous example, once Cesar had selected generator 2 to purchase, he approached the manufacturer with the concerns that the first cost was too high and the expected life was too short. He was offered a lease arrangement for 6 years with a $20,000 annual cost and an extra $20,000 payment in the first and last years to cover installation and removal costs. Determine if generator 2 or the lease arrangement is better at 12% per year.

Figure (4-5):Different-life alternatives evaluated using the NPV function, Example (4-6)

Assuming that generator 2 can be repurchased 3 years hence and all estimates remain the same, PW evaluation over 6 years is correct. Figure 4.5 details the cash flows and NPV functions. The year 3 cash flow for generator 2 is S - AOC - P = $ - 88,000. Note that the first costs are not included in the NPV function but are listed separately, as indicated in Equation (4-4). The lease option is the clear winner for the next 6 years.

 When evaluating alternatives for which the annual cash flows do not form an A series, the individual amounts must be entered on the spreadsheet and Equation (4-4) is used to find PW values. Also, remember that any zero-cash-flow year must be entered as 0 to ensure that the NPV function correctly tracks the years.