Engineering Economic Analysis

.. -- - --..-- ---

Spreadsheets for Economic Analysis 123

To find the equivalentP

To find the equivalentA

To find the equivalentF

To findn

To findi

-PV(i,n,A,F,Type)

-PMT(i,n,P,F,Type)

• FV(i ,n,A,P,Type)
  NPER(i,A,P ,F,Type)
  RATE(n,A,P ,F,Type,guess)

The sign convention for the first three functions seems odd to some students. The PV

of $200 per period for 10 periods is negative and the PV of -$200 p.erperiod is positive.

So a minus sign is inserted to find the equivalentP, A,orF.Without this minus sign, the

calculated value is not equivalent to the four given values.

A new engineer wants to save money for down payment on a house. The initial deposit is $685,

and $375 is deposited at the end of each month. The savings account earns interest at an annual

nominal rate of 6% with monthly compounding. How much is on deposit after 48 months?

Because deposits are made monthly, the nominal annual interest rate of 6% must be converteq to

1/2% per month for the 48 months. Thus we must findFifi=0.5% =0.005,n=48,A = 375,

and P... 685. Note both the initial and periodic deposits are positive cash flows for the savings

account. The Excel function is multiplied by-lor -FV(0.005,48,375,685,0), and the result is

$21,156.97.

A new engineer buys a car with 0% down financing from the dealer. The cost with all taxes,

registration, and license fees is $15,732. If each of the 48 monthly payments is $398, what is the

monthly interest rate? What is the effective annual interest rate?

-.. -'. ." .-- .~ - ". ".-
.. -.' '-..~SOlUTION~:;' ::. I. -~ :.:.","--. .., - ..-, '

The RATE function can be used to find the monthly interest rate.. given thatn= 48,A =

-398,P=15,732, andF=O. The Excel function is RATE(48,-398,15732,0) and the result

is 0.822%. The effective annual interest rate is 1.0082212- 1 =10.33%.

Spreadsheet Block Functions

Cash flows can be specified period-by-period as a block of values. These cash flows are

analyzed byblock functionsthat identify the row or column entries for which a present

worth or an internal rate of return should be calculated. In Excel the two functions are

NPV(i,values) and IRR(values,guess).

T - -- --- -