Uniform gradient present worth excel

CHAPTER 6 – ANNUAL CASH FLOW ANALYSIS

In the past chapters we have calculated Worth, Costs, and Benefits over the entire investment period, and we have seen that Net Present Worth (NPW) is equal to the difference between Present Worth of Benefits and Present Worth of Costs. We have considered uniform cash flows and non-uniform cash flows

If the original cash flow is uniform, we can easily apply the Compound Interest factors from the tables or the Excel Tool:

NPW ≈ PVbenefits(P/A, i, n) - PVcosts(P/A, i, n)

If the original cash flow is not uniform it is better to use the Excel function NPV and PV.

For several reasons, such as Undefined Life Period, Taxes, Annual Budgets, Stock Pricing, etc. , it is at times necessary to estimate the periodic uniform cash flow equivalent to the effective cash flow generated throughout its life by an investment (review the definition of BOOK COSTS, and in general Book Entries, introduced in Chapter 2). Equivalent means that the original cash flow and the uniform equivalent cash flow have the same Net Present Worth

Annual Cash Flow Analyses “ resolve the effective cash flow into an annual equivalent uniform cash flow ”, converting the effective cash flows into an equivalent uniform annual costs and/or benefits .

6.1 ANNUAL CASH FLOW ANALYSIS

The Annual Cash Flow Analysis is performed by converting each cash flow into an equivalent series of end-of-period cash flows. Keeping in mind the compound interest factors, and the relation

NPW = PVbenefits - PVcosts

Defining: EUAW EQUIVALENT UNIFORM ANNUAL WORTH

EUAB EQUIVALENT UNIFORM ANNUAL BENEFIT

EUAC EQUIVALENT UNIFORM ANNUAL COST

The above relation can be written as:

EUAW = EUAB – EUAC

Where:

EUAW = NPW (A/P, i, n)

EUAB = PVbenefits(A/P, i, n)

EUAC = PVcosts(A/P, i, n)

Example 6-1 – The CEO (Chief Executive Officer) of a Company intends to install a new Production Line which will cost $500,000 and estimates it would increase sales revenue by $80,000/year, but would require an annual cost of $10,000 for O&M. The New Production Line is anticipated having a useful life of 7 years, at the end of which it could be sold as used for $90,000 . The Company’s CFO (Chief Financial Officer) who is responsible for procuring the finances necessary for the investment, and for preparing yearly budgets, tax returns, dividend distributions, etc. asks what would be the equivalent annual impact of the planned Production Line on the Company’s finances knowing that the cost of capital for the company, i.e. the interest rate he/she is able to secure, is 8.5%; in other words he/she asks for the EUAW.

The Cash Flow Diagram for the potential investment is:

The calculation of the EUAW is done by “uniformly spreading” each single cash flow over the duration of the investment; using the Compound Interest Factors we obtain:

EUAW = EUAB - EUAC

EUAB = [80,000 + 90,000 (A/F, 8.5, 7)] = 80,000 + (90,000 x 0.110) = 89,900

EUAC = [500,000 (A/P, 8.5, 7) + 10,000] = (500,000 x 0.195) + 10,000 = 107,500

EUAW = 89,900 – 107,500 = -$17,600

The Equivalent Uniform Annual Worth of the new proposed Production Line is negative, i.e. the Company would have to report on its books a uniform loss of $ 17,600 per year ; the CFO would likely advise against this investment.

Note that in the calculations above the increase in sales revenues of $80,000/year, and the O&M cost of $10,000/year were not multiplied by any compound interest factors because they are already “per year”, i.e. already uniformly spread over the life of the investment.

The amount [(90,000 x 0.110) - (500,000 x 0.195)] is called Capital Recovery Sinking Fund , which is the amount which must be set aside yearly, at an interest rate of 8.5%, to “recover” the initially invested capital

By the way, it is worth noting that the same result can be obtained by doing:

EUAW = NPW x (A/P, i, n) =

=[ -500,000 + (80,000 – 10,000) (P/A, 8.5, 7) + 90,000 (P/F, 8.5, 7)] x (A/P, 8.5, 7) =

=[-500,000 + 70,000 x 5.119 + 90,000 x 0.565] x 0.152 =

= (-500,000 + 358,330 + 50,850) x 0.195 = -90,820 x 0.195 = -17,709

· (The NPW of the investment would be -$90,828)

The slight difference in results (0.6%) is due to the rounding programmed into excel financial functions and is perfectly acceptable at this level of economic evaluations.

However The CEO is not at all satisfied with this result, and still wants to install the additional Production Line, so he/she asks the CFO to procure better financing terms (the interest), and the Chief Of Operations (COO) research the market for a better deal. The COO found a supplier who would sell a Production Line for $330,000, however such line would be able to generate sale revenues of only $70,000 per year, with an O&M cost of $8,000 per year, and an estimated salvage value of $70,000. Meanwhile, the CFO was able to secure a loan at 6.5%. Now the situation is:

EUAB = [70,000 + 70,000 (A/F, 6.5, 7)]

EUAC = [330,000 (A/P, 6.5, 7) + 8,000]

EAUW = [70,000 + 70,000 (A/F, 6.5, 7)] - [330,000 (A/P, 6.5, 7) + 8,000]=

= [70,000 + (70,000 x 0.117)] – [(330,000 x 0.182) + 8,000] =

= 78,190 – 68,060 = $10,130

This time the EAUW is positive, i.e. the equivalent investment worth per year is positive , the Accounting Department would be able to report a yearly positive earning, and therefore the CFO will likely agree with the implementation.

In this case the net present worth of the investment would be:

NPW = 10,130 / (A/P, 6.5, 7) = 10,130 / 0.182 = $ 55,659

Note that we have taken the EUAW, i.e. the equivalent uniform annual worth, and “returned” it to its present value.

Similar NPW result can also be obtained directly:

NPW = -330,000 – 8,000 (P/A, 6.5, 7) + 70,000 (P/A, 6.5,7) + 70,000 (P/F, 6.5, 7)=

= -330,000 – (8,000 x 5.485) + (70,000 x 5.485) + (70,000 x 0.644) =

= -330,000 – 43,880 + 383,950 + 45,080 = $ 55,150

Again, the small difference between the two results (0.09%) is due to the effect of Excel’s rounding on large numbers, and is perfectly acceptable in economic evaluations

Example 6-2 – Your Company has purchased a new Diesel Generator (DG) to supply power at a remote construction site anticipated to be active for the next 8 years. The Company’s Accountant needs to know the Equivalent Uniform Annual Cost (EUAC) to the Company for maintaining the DG, and he/she tells the Maintenance Manager that the Company’s cost of money is 7.33%. The maintenance cost is anticipated to be $700 for the first year of operation, increasing by a constant $100 per year.

The relative Cash Flow Diagram is:

The Equivalent Uniform Annual Cost of maintenance is

EUAC = 700 + 100 (A/G, 7.33, 8) = 700 + (100 x 3.131) = $ 1,013

Again, note in the above calculation that the value $700 was not multiplied by a compound factor because it is already “uniform” over the project period. The G portion of the cost has been evaluated as the “equivalent uniform series” of cash flows.

Suppose now that the DG, due to unforeseen harsh environmental conditions, needs an extraordinary maintenance intervention after 4 years of operation. The extraordinary maintenance costs $2,000, and after the intervention the regular scheduled maintenance restart at $700 per year, increasing by $100 per year . The Cash Flow now becomes:

This case is more complicate since we have two series with gradient G and a one-time cash flow at period 4; the way to solve for EUAC is to “bring” all the values to the beginning and then “spread” the total over the entire 8 periods:

EUAC =

= 700 + 100 x (A/G, 7.33, 4) + 100 x [(P/G, 7.33, 4) x (P/F, 7.33, 4) x (A/P, 7.33, 8)] +

+ 2,000 x (P/F, 7.33, 4) x (A/P, 7.33, 8)=

= 700 + 100 x 1.412 + 100 x (4.746 x 0.754 x 0.170) + 2,000 x (0.754 x 0.170) =

= 700 + 141.2 + 60.8 + 256.36 = $1,158.36

Let us analyze the elements of the equation:

· 700 is the uniform portion of the yearly maintenance cost, so it does not need to be multiplied by any compound interest factor

· 100 (A/G, 7.33, 4) is the value A of the uniform series equivalent to the first gradient G (the first four periods)

· 100 x [(P/G, 7.33, 4) x (P/F, 7.33, 4) x (A/P, 7.33, 8) ] is the value of the second gradient G “brought” to end of period 4, then “brought” to the present, and then “spread” over the entire 8 years duration to find the value of the series A equivalent to the second gradient G (the second four periods)

· 2,000 x (P/F, 7.33, 4) x (A/P, 7.33, 8) is the present value of the $2,000 paid for extra maintenance at the end of period 4 , “brought” to the present then “spread” over the entire 8 periods

To confirm the result we can do the reverse: calculate the present value PVcost of the cost and then “spread” it over the entire period to obtain the EUAC again

PVcost = 700 (P/A, 7.33, 8) + 100 (P/G, 7.33, 4) + 2,000 (P/F, 7.33, 4) +

+ 100 (P/G, 7.33, 4) (P/F, 7.33, 4) =

= (700 x 5.896) + (100 x 4.746) + (2,000 x 0.754) + (100 x 4.746 x 0.74)

= 4,127.2 + 474.6 + 1,508 + 351.20 = $ 6,461

EUAC = 6,461 x (A/P, 7.33, 8) = $ 1,098.4

Again, the two results are slightly different (approx. 0.5%) due to Excel’s rounding effect on large numbers, but perfectly acceptable.

6.2 CRITERIA FOR DECISION

Recalling the criteria, mentioned in Chapter1, to be applied in decision making, according to the three possible situations encountered in economic analyses, and adopting them to Annual Cash Flow Analysis:

· Neither input nor output are fixed Maximize EUAW

· Input is fixed Maximize EUAB

· Output is fixed Minimize EUAC

Example 6-3 (similar to text example 6-6) – A Company is considering improving the production capacity of one of its Facilities, and is considering several alternatives anticipated to be in service for 8 years:

Alt. A Alt. B Alt. C

Installation Cost 30,000 25,000 40,000

Yearly Sales Increase 8,000 7,000 9,000

Yearly O&M cost 2,000 1,800 2,400

Salvage Value 7,000 5,000 10,000

Knowing that the Company’s cost of money is 6.5%, the Company’s Accountant wants to know which alternative will be selected and what would be the EUAW of this investment (he/she needs it as an input for the annual budget forecast).

This is the typical “Neither input nor output are fixed”, so the objective is to find the alternative with Maximum EUAW

EUAW = EUAB – EUAC

EUABA = 8,000 + 7,000 (A/F, 6.5, 8) = 8,000 + 7,000 x 0.099 = 8,693

EUABB = 7,000 + 5,000 (A/F, 6.5, 8) = 7,000 + 5,000 x 0.099 = 7,495

EUABC = 9,000 + 10,000 (A/F, 6.5, 8) = 9,000 + 10,000 x 0.099 = 9,990

EUACA = 30,000 x (A/P, 6.5, 8) + 2,000 = 30,000 x 0.164 + 2,000 = 6,920

EUACB = 25,000 x (A/P, 6.5, 8) + 1,800 = 25,000 x 0.164 + 1,800 = 5,900

EUACC = 40,000 x (A/P, 6.5, 8) +2,400 = 40,000 x 0.164 + 2,400 = 8,960

EUAWA = 8,693 – 6,920 = 1,773

EUAWB = 7,495 – 5,900 = 1,595

EUAWC = 9,990 – 8,960 = 1,030

Therefore, the preferred Alternative is Alternative A, which would produce the larger Equivalent Uniform Annual Worth.

It is worth noting that the same decision would be reached by analyzing the NPW of the alternatives: referring to the Excel Spreadsheet, the NPW for alternative A at i=6.5% is the highest of the three alternatives

NPW @ i=6.5% =

$10,105

NPW @ i=6.5% =

$9,092

NPW @ i=6.5% =

$5,848

NOTE: at this point, a legitimate question would be “since, when it comes to deciding among several Alternatives, the EUAW and the NPW give the same result, what is the need for calculating the EUAW which is mainly used by Accountants or Financers (normally not by engineers)?”. The answer to this question is that in some cases not all the parameters necessary to calculate the NPW are available: as we have seen these parameters are Cash Flows, Number of Periods, Interest Rates; when the life span of the investment is indefinite, the Number of Periods is not a usable parameter. This case is discussed in the following paragraph

6.3 ANALYSIS FOR INDEFINITE PERIODS

When the horizon of the analysis is indefinite, i.e. the potential investment being analyzed is supposed to continue operate for a non-defined number of years, for instance an Aqueduct, an High Way, etc., the comparison among alternatives is done by estimating the relative annual costs under the assumption that when each alternative needs replacing a component, such replacement is made with a new identical one having same cost, same performance, and same useful life .

With such assumption, the EUAC of each Alternative will not change over the indefinite horizon of the considered investment, therefore potential alternatives can be compared limiting the period of comparison to the useful life of the replacement for each alternative. This approach is clarified in the following example

Example 6-5 – Two proposals are being considered by a Municipality for the construction of a new aqueduct. To finance the project, the Municipality intends to issue Bonds at an interest of 5.25%. The two proposals for the supply of the aqueduct’s pumping station are being evaluated from the point of view of several criteria, one of which is to minimize the EUAC for Pumping Station replacement. The team in charge of evaluating equipment EUAC has the following data

Alternate A Alternate B

Cost of Pumping Station 8,000 5,000

Useful Life of Station 15 years 8 years

Salvage value 2,000 1,000

The cash flows for the two alternatives are:

It is evident that each alternative presents a sequence of identical periodic expenditures, the pumping station cost, and periodic cash receipts, the salvage value.

Each period within each sequence (15 years for alternate A and 8 years for alternate B) will have the same annual equivalent expenditure, i.e. the EUAC will be the same within each repeated sequence. It is clear therefore that to compare the two alternatives is sufficient to compare EUACA versus EUACB:

EUACA = 8,000 (A/P, 5.25, 15) – 2,000 (P/F, 5.25, 15) (A/P, 5.25, 15)=

= 8,000 x 0.098 – (2,000 x 0.464 x 0.098) = 784 – 90.94 = $693

EUACB = 5,000 (A/P, 5.25, 8) – 1,000 (P/F, 5.25, 8) (A/P, 5.25 ,8)=

= 5,000 x 0.156 – (1,000 x 0.664 x 0.156) = 780 -103.58 = $676.42

These are costs, therefore the preferred alternative, as far as the equipment cost is concerned, would be Alternate A which has the lower EUAC.

Note in the calculations the salvage cost has a negative sign because is not a cost, it is a benefit, an in-flow of money.

As indicated, the above analysis rests on the assumption that future replacements are made with identical equipment (cost, performance, useful life), obviously this is not the case in reality, but for estimating purposes this approach is universally accepted.

6.4 ANALYSIS FOR INFINITE PERIODS

A different situation arises when the analysis horizon is infinite and the useful life of the equipment in question is infinite , for example considering the proposed investment in a Dam and evaluating the EUAC of future service for an infinite number of years. Note that, depending on which investment is considered, the term “future service” is composed of several items, including but not limited to:

· Capital Recovery

· Maintenance

· Operation

· Environmental Considerations

· Etc.

Recalling from Chapter 5 that Capitalized Cost is the amount of money P that must be presently put aside in a saving account with interest i, which would yield sufficient periodic funds A as required for providing services for an infinite period:

A = i P

It follows that

EUAC∞ = P (A/P, i, ∞) + Other Annual Costs

The sum P is what must be set aside, in an fund earning i interest, to “service” the project for an infinite time, i.e having A available “ad infinitum”. It follows:

(A/P, i, ∞) = i

Leading to:

EUAC∞ = i P + Other Annual Costs

Example 6-6 (text example 6-9) – Suppose a Municipality is considering the construction of a new aqueduct, which must last for infinite periods of time, to bring water to the City from a far-away lake. The City’s cost of money is estimated to be 6%, and the City Manager wants to estimate the Capital Recovery Cost EUAC to the City for each of two possible alternatives:

Alternative A – tunnel through a mountain

Alternative B – build a pipeline going around the mountain

The two alternatives have the following characteristics:

Alternative A Alternative B

Initial Cost $5.5 Million $5 Million

Maintenance 0 0

Useful Life ∞ 50 years

Salvage Value 0 0

Using the equation for infinite period:

EUACA = 5.5 M x 0.06 = $330,000

EUACB = 5 M (A/P, 6, 50) = 5 M x 0.063 = $315,000

The Equivalent Uniform Annual Cost for Capital Recovery of Alternate B is the lower and therefore is the one to be preferred

Note that the difference between the value of i and the value of (A/P, i, n) tends to zero as n grows:

lim (A/P, i, n) = i

n∞

6.5 LOAN ANALYSIS

A loan requires periodic repayments for a number of periods, at a fixed interest rate. Analysis of a loan requires building an AMORTIZATION SCHEDULE (house loans require paying a “mortgage”), which lists:

· Loan periodic payment

· Amount of interest paid

· Amount of principal paid

· Remaining balance

Example 6-7 (text example 6-11) – Consider assuming a loan of $2,400, repayable in 6 monthly installments, at an interest rate of 6%. Build the Amortization Schedule.

The annual interest rate is 6% , so the monthly rate is 0.06/12 = 0.005 = 0.5% . The monthly payments are:

2,400 (A/P, 0.5, 6) = 2,400 x 0.170 = $ 408/month

The excel spreadsheet is:

· The monthly Interest Payments are obtained by multiplying the Ending Balance of the previous period by the monthly interest rate

· The Principal Payments are obtained by subtracting the present period Interest Payment from the Monthly Payment

· The Ending Balance are obtained by subtracting from the Ending Balance of the previous period the present period Principal Payment

The Amortization Schedule is one method of analyzing a loan, the other method is to consider that AT ANY PERIOD THE BALANCE DUE EQUALS THE PRESENT WORTH (present meaning at that period) OF THE REMAINING STREAM OF PAYMENTS

Consider the example above, if we want to know the “balance due” say at the end of the third period

Balance due = 408 (P/A, 0.5, 3) = 408 x 2.97 = $1,212

6.6 ANNUITY DUE

When a UNIFORM cash flow (in or out) happens at the BEGINNING OF PERIOD , is referred to as ANNUITY DUE , examples are rent, lease, insurance, etc. in general payments for services, as opposed to tangible goods, are in this form for the simple reason that, in case of payment default, an already provided service cannot be taken back.

The method to convert these beginning-of-period annuities to end-of-period EUAC is to consider the first payment as a present worth payment P and the rest of the series of payment as happening at end-of-period but for an n-1 number of periods

Example 6-8 (text example 6-14) – Find the present worth PW (we do not say NPW because there are no Benefits) and the Equivalent Uniform Monthly Cost EUMC for a lease payment of $1,200 per month at a monthly interest of 1%

First we find the PW (it is a cost)

PW = -1,200 – 1,200 (P/A, 1, 11) = -1,200 – 1,200 x 10.368 = -$13,642

Then we find the EUMC

EUMC = 13,642 (A/P, 1, 12) = 13,642 x 0.089 = $1,214