The functions on a computer spreadsheet can greatly reduce the amount of hand work for equivalency computations involving compound interest and the terms P , F , A , i , and n . The use of a calculator to solve most simple problems is preferred by many students and professors. However, as cash flow series become more complex, the spreadsheet offers a good alternative. Microsoft Excel is used throughout this book because it is readily available and easy to use.

A total of seven Excel functions can perform most of the fundamental engineering economy calculations. The functions are great supplemental tools, but they do not replace the understanding of engineering economy relations, assumptions, and techniques. Using the symbols P , F , A , i , and n defined in the previous section, the functions most used in engineering economic analysis are formulated as follows.

§  To find the present value P: = PV( i%, n, A, F )

§  To find the future value F: = FV( i%, n, A, P )

§  To find the equal, periodic value A: = PMT( i%, n, P, F )

§  To find the number of periods n: = NPER( i%, A, P, F )

§  To find the compound interest rate i: = RATE( n, A, P, F )

§  To find the compound interest rate i: = IRR(fi rst_cell:last_cell)

§  To find the present value P of any series: = NPV( i %, second_cell:last_cell) + fi rst_cell

If some of the parameters don’t apply to a particular problem, they can be omitted and zero is assumed. For readability, spaces can be inserted between parameters within parentheses. If the parameter omitted is an interior one, the comma must be entered. The last two functions require that a series of numbers be entered into contiguous spreadsheet cells, but the first five can be used with no supporting data. In all cases, the function must be preceded by an equals sign (+) in the cell where the answer is to be displayed.

A Japan-based architectural fi rm has asked a United States–based software engineering group to infuse GPS sensing capability via satellite into monitoring software for high-rise structures in order to detect greater than expected horizontal movements. This software could be very beneficial as an advance warning of serious tremors in earthquake-prone areas in Japan and the United States. The inclusion of accurate GPS data is estimated to increase annual revenue over that for the current software system by $200,000 for each of the next 2 years, and by $300,000 for each of years 3 and 4. The planning horizon is only 4 years due to the rapid advances made internationally in building-monitoring software. Develop spreadsheets to answer the questions below.

1)      Determine the total interest and total revenue after 4 years, using a compound rate of return of 8% per year.

2)      Repeat part (1) if estimated revenue increases from $300,000 to $600,000 in years 3 and 4.

3)      Repeat part (1) if inflation is estimated to be 4% per year. This will decrease the real rate of return from 8% to 3.85% per year.

Refer to Figure (1–13) a to d for the solutions. All the spreadsheets contain the same information, but some cell values are altered as required by the question. (Actually, all the questions can be answered on one spreadsheet by changing the numbers. Separate spreadsheets are shown here for explanation purposes only.)

The Excel functions are constructed with reference to the cells, not the values themselves, so that sensitivity analysis can be performed without function changes. This approach treats the value in a cell as a global variable for the spreadsheet. For example, the 8% rate in cell B2 will be referenced in all functions as B2, not 8%. Thus, a change in the rate requires only one alteration in the cell B2 entry, not in every relation where 8% is used.

1)      Figure (1–13 a) shows the results, and Figure (1–13 b) presents all spreadsheet relations for estimated interest and revenue (yearly in columns C and E, cumulative in columns D and F). As an illustration, for year 3 the interest I3 and revenue plus interest R3 are

I₃ = (cumulative revenue through year 2) (rate of return)

    = $416,000(0.08)

    = $33,280

          R₃ = revenue in year 3 + I₃

   = $300,000 + 33,280

   = $333,280

The detailed relations shown in Figure (1–13 b) calculate these values in cells C8 and E8.

Cell C8 relation for I₃ : = F7×B2

Cell E8 relation for CF₃ := B8 + C8

The equivalent amount after 4 years is $1,109,022, which is comprised of $1,000,000 in total revenue and $109,022 in interest compounded at 8% per year. The shaded cells in Figure (1–13 a) and b indicate that the sum of the annual values and the last entry in the cumulative columns must be equal.

2)      To determine the effect of increasing estimated revenue for years 3 and 4 to $600,000, use the same spreadsheet and change the entries in cells B8 and B9 as shown in Figure (1–13 c). Total interest increases 22%, or $24,000, from $109,222 to $133,222.

3)      Figure (1–13 d) shows the effect of changing the original i value from 8% to an inflation adjusted rate of 3.85% in cell B2 on the first spreadsheet. [Remember to return to the $300,000 revenue estimates for years 3 and 4 after working part (2).] Inflation has now reduced total interest by 53% from $109,222 to $51,247, as shown in cell C10.

Figure (1-13 a): Total interest and revenue for base case, year 4

Figure (1-13 b): Spreadsheet relations for base case

Figure (1-13 c): Totals with increased revenue in years 3 and 4

Figure (1-13 d): Totals with inflation of 4% per year considered

Problems

(1-1)   List the four essential elements involved in decision making in engineering economic analysis.

(1-2)   What is meant by (a) limited capital funds and (b) sensitivity analysis?

(1-3)   List three measures of worth that are used in engineering economic analysis.

(1-4)   Identify the following factors as either economic (tangible) or noneconomic (intangible): first cost, leadership, taxes, salvage value, morale, dependability, inflation, profit, acceptance, ethics, interest rate.

(1-5)   Emerson Processing borrowed $900,000 for installing energy-efficient lighting and safety equipment in its La Grange manufacturing facility. The terms of the loan were such that the company could pay interest only at the end of each year for up to 5 years, after which the company would have to pay the entire amount due. If the interest rate on the loan was 12% per year and the company paid only the interest for 4 years, determine the following:

a)      The amount of each of the four interest payments

b)      The amount of the final payment at the end of year 5

(1-6)   Which of the following 1-year investments has the highest rate of return?

a)      $12,500 that yields $1125 in interest,

b)      $56,000 that yields $6160 in interest, or

c)      $95,000 that yields $7600 in interest.

(1-7)   The symbol P represents an amount of money at a time designated as present. The following symbols also represent a present amount of money and require similar calculations. Explain what each symbol stands for: PW, PV, NPV, DCF, and CC

(1-8)   What is meant by end-of-period convention?

(1-9)   Construct a cash flow diagram to find the present worth in year 0 at an interest rate of 15% per year for the following situation.

(1-1)   Construct a cash flow diagram that represents the amount of money that will be accumulated in 15 years from an investment of $40,000 now at an interest rate of 8% per year.

(1-2)   At an interest rate of 15% per year, an investment of $100,000 one year ago is equivalent to how much now?

(1-3)   University tuition and fees can be paid by using one of two plans.

Early-bird: Pay total amount due 1 year in advance and get a 10% discount.

On-time: Pay total amount due when classes start.

The cost of tuition and fees is $10,000 per year.

a)      How much is paid in the early-bird plan?

b)      What is the equivalent amount of the savings compared to the on-time payment at the time that the on-time payment is made?

(1-4)   If a company sets aside $1,000,000 now into a contingency fund, how much will the company have in 2 years, if it does not use any of the money and the account grows at a rate of 10% per year?

(1-5)   To finance a new product line, a company that makes high-temperature ball bearings borrowed $1.8 million at 10% per year interest. If the company repaid the loan in a lump sum amount after 2 years, what was:

a)      The amount of the payment

b)      The amount of interest?

(1-6)   If interest is compounded at 20% per year, how long will it take for $50,000 to accumulate to $86,400?

(1-7)   Give three other names for minimum attractive rate of return.

(1-8)   What is the weighted average cost of capital for a corporation that finances an expansion project using 30% retained earnings and 70% venture capital? Assume the interest rates are 8% for the equity financing and 13% for the debt financing.

(1-9)   State the purpose for each of the following built-in spreadsheet functions.

a)      PV ( i% , n , A , F )

b)      FV ( i %, n , A , P )

c)      RATE ( n , A , P , F )

d)      IRR (first_cell:last_cell)

e)      PMT ( i %, n , P , F )

f)       NPER ( i %, A , P , F )

(1-10)    What are the values of the engineering economy symbols P , F , A , i , and n in the following functions? Use a question mark for the symbol that is to be determined.

a)      NPER (8%,-1500,8000,2000)

b)      FV (7%,102000,-9000)

c)      RATE (10,1000,-12000,2000)

d)      PMT (11%,20,,14000)

e)      PV (8%,15,-1000,800)