Blank L., Tarquin A. Engineering Economy (McGraw-Hill Series in Industrial Engineering and Management)
Подождите немного. Документ загружается.
SECTION A.3
Excel Functions Important to Engineering Economy
Example Jack wants
to
start a savings account that can be increased
as
desired.
He will deposit
$12,000
to
start the account and plans
to
add $500
to
the account
at the beginning of each month for the next 24 months. The bank pays
0.25% per
month. How much will be
in
Jack's account at the end
of
24 months?
Future value
in
24 months: = FV(0.25%,24,500,12000,l)
IF
(IF
Logical
Function)
Determines which
of
two entries is entered into a cell based on the outcome
of
a
logical check on the outcome
of
another cell. The logical test can be a function or
a simple value check, but it must use
an
equality or inequality sense.
If
the re-
sponse
is
a text string, place it between quote marks (" "). The responses can
themselves be IF functions.
Up
to
seven IF functions can be nested for very com-
plex logical tests.
= IF(logical_test,value_
iCtrue,value_iCfalse)
valu
e_
iCtru
e
value_
iCfalse
Any worksheet function can be used here,
including a mathematical operation.
Result if the logical_test argument is true.
Result if the logical_test argument is false.
Example The entry
in
cell B4 should be "selected"
if
the PW value in cell B3
is
greater than or equal
to
zero and "rejected"
if
PW <
O.
Entry
in
cell B4: = IF(B3
>=
O,"selected","rejected")
Example The entry in cell
C5
should be "selected"
if
the
PW
value in cell C4 is
greater than or equal
to
zero, "rejected" if PW < 0, and "fantastic" if PW
2::
200.
Entry in cell C5: =IF(C4< 0,"rejected",IF(C4>=200,"fantastic",
"selected")
)
IPMT
(Interest
Payment)
Calculates the interest accrued for a given period n based on constant periodic
payments and interest rate.
=IPMT(rate,
per, nper, pv,
fv,
type)
rate Interest rate per compounding period.
per
Period for which interest
is
to be calculated.
nper Number
of
compounding periods.
pv
Present value.
If
pv is not specified, the function
will assume it to be
O.
fv
Future value.
If
fv
is omitted, the function will assume
it
to
be
O.
The
fv
can also be considered a cash balance after the last
payment is made.
type (optional entry) Either
0 or
1.
A 0 represents payments made at
the end
of
the period, and 1 represents payments made at the
beginning
of
the period.
If
omitted, 0 is assumed.
705
706
APPENDlXA Using Spreadsheets
and
Mi
cr
os
oft Excel©
Example
Calculate the interest due in the tenth month for a 48-month, $20,000
loan. The interest rate is 0.
25
% per month.
Interest due:
= IPMT(0.
2S
%,
10,48,20000)
IRR
(Intern
al
Ra
te
of
Return)
Calculates the internal rate
of
return between - 100% and infinity for a se
ri
es
of
cash
fl
ows at regular periods.
values
=IRR
(values, guess)
A set
of
numbers in a spreadsheet column (or row) for w
hi
ch
the rate
of
return wi
ll
be calculate
d.
The set
of
numbers
mu
st
consist
of
at least one positive a
nd
one negative numbe
r.
Negative numbers denote a payment made or cash outflow,
and positive numbers denote income or cash inflow.
guess (optional entry) To reduce the number
of
iterations, a guessed
rate
of
return. can be entered. In most cases, a guess is not
required, and a 10% rate
of
retu
rn
is initially assumed. If
th
e
#NUM! error appears, try usi
ng
different values for guess.
I
np
utting different guess values makes it possible to
determine the multiple roots for the rate
of
return equa
ti
on of
a nonconventi
ona
l cash
fl
ow serie
s.
Exam
pl
e John wants to start a printing
bu
siness.
He
will need $
25
,000
in
cap-
ital and anticipates that t
he
business will generate the following incomes during
the first
5 years. Calculate his rate
of
return after 3 years and after 5 years.
Ye
ar 1 $5,000
Year 2 $7,500
Y
ea
r 3 $8,000
Year 4 $10,000
Year 5 $
15
,000
Set up an array in the spreadsheet.
In ce
ll
AI
, type - 25000 (negative for payment
).
In
cell A2, type 5000 (positive for income).
In ce
ll
A3, type 7500.
In ce
ll
A4, type 8000.
In ce
ll
AS
, type 10000.
In ce
ll
A6, type 15000.
Therefore, ce
ll
s
Al
through A6 contain the array
of
cash flows for
th
e first
5 years, including the capital outlay. Note that any years with a zero cash flow
must have a ze
ro
e
nt
ered to ensure that the year value is correctly mainta
in
ed for
computation purposes.
To calculate the internal rate
of
return after 3 years, move to ce
ll
A 7, and type
= IRR(A1:A4).
SECTION
A.3
Excel Functions Important
to
Engineering Economy
To
calculate the internal rate
of
return after 5 years and specify a guess value
of
5%, move to cell A8, and type
=IRR(Al:A6,5%).
MIRR (Modified Internal Rate
of
Return)
Calculates the modified internal rate
of
return for a series
of
cash flows and rein-
vestment
of
income and interest at a stated rate.
=MIRR(values,
financeJate,
reinvestJate)
values
finance_rate
reinvesCrate
Refers to an array
of
cells in the spreadsheet. Negative
numbers represent payments, and positive numbers
represent income. The series
of
payments and income
must occur at regular periods and must contain at least
one positive number and one negative number.
Interest rate
of
money used in the cash flows.
Interest rate for reinvestment on positive cash flows.
(This
is
not the same reinvestment rate on the net
investments when the cash flow series is
nonconventional. See Section 7.5 for comments.)
Example Jane opened a hobby store 4 years ago. When she started the busi-
ness, Jane borrowed
$50,000 from a bank at 12%
per
year. Since then, the
business has yielded
$10,000 the first year, $15,000 the second year, $18,000 the
third year, and
$21,000 the fourth year. Jane reinvests her profits, earning 8% per
year. What
is
the modified rate
of
return after 3 years and after 4 years?
In cell A
I , type - 50000.
In cell A2, type 10000.
In cell A3, type 15000.
In cell A4, type 18000.
In cell
AS,
type 21000.
To
calculate the modified rate
of
return after 3 years,
move
to cell A6, and type
= MIRR(A I :A4, 12%,8%).
To calculate the modified rate
of
return after 4 years, move to cell A 7, and type
= MIRR(A
I :A5,12%,8%).
NPER(NumberofPeriod~
Calculates the number
of
periods for the present worth
of
an investment to equal
the future value specified, based on uniform regular payments and a stated inter-
est rate.
=NPER(rate,
pmt, pv,
fv,
type)
rate Interest rate per compounding period.
pmt Amount paid during each compounding period.
pv
Present value (lump-sum amount).
707
708
APPENDIX
A Using Spreadsheets
and
Microsoft Excel
""
fv (optional entry) Future value or cash balance after the last
payment.
If
fv
is omitted, the function will assume a value
of
O.
type (optional entry) Enter 0
if
payments are due at the end
of
the
compounding period, and 1 if payments are due at the
beginning
of
the period.
If
omitted, 0
is
assumed.
Example Sally plans
to
open a savings account which pays 0.25% per month.
Her initial deposit
is
$3000, and she plans to deposit $250 at the beginning
of every month. How many payments does she have
to
make
to
accumulate
$15,000
to
buy a new car?
Number
of
payments:
=NPER(0
.2S%,
-250,
- 3000,15000,1)
NPV
(Net
Present Value)
Calculates the net present value
of
a series
of
future cash flows at a stated inter-
est rate.
=NPV(rate, series)
rate Interest rate per compounding period.
series Series
of
costs and incomes set
up
in a range
of
cells in the spreadsheet.
Example Mark
is
considering buying a sports store for $100,000 and expects
to
receive the following income during the next 6 years
of
business:
$25
,000,
$40,000, $42,000, $44,000, $48,000, $50,000. The interest rate
is
8% per year.
In
cell
AI,
type - 100000.
In
cell A2, type 25000.
In
ce
ll
A3, type 40000.
In
cell A4, type 42000.
In
cell
AS
, type 44000.
In
cell A6, type 48000.
In
cell A 7, type 50000.
In
cell
A8
, type =NPV(8%,
A2:A7)+Al.
The cell
Al
value is already a present value.
Any
year with a zero cash flow must
have a
0 entered to ensure a correct result.
PMT
(Payments)
Calculates equivalent periodic amounts based on present value andlor future
value at a constant interest rate.
=PMT(rate, nper, pv,
fv,
type)
rate Interest rate per compounding period.
nper Total number
of
periods.
SECTION A.3
Excel Functions Important to Engineering Economy
pv Present value.
fv Future value.
type (optional entry) Enter
0 for payments due at the end
of
the
compounding period, and 1
if
payment is due at the start
of
the
compounding
period.
If
omitted, 0 is assumed.
Example Jim plans to take a $15,000 loan to buy a new car. The interest rate is
7%. He wants to pay the loan off
in
5 years (60 months). What are his monthly
payments?
Monthly payments:
= PMT(7%/12,60,15000)
PPMT (Principal Payment)
Calculates the payment on the principal based on uniform payments at a speci-
fied interest rate.
=PPMT(rate, per, "per, pv,
fv,
type)
rate Interest rate per compounding period.
per
Period for which the
payment
on the principal is required.
nper Total number
of
periods.
pv Present value.
fv Future value.
type (optional entry) Enter
0 for payments that are due at the end
of
the compounding period, and 1
if
payments are due at the
start
of
the compounding period.
If
omitted, 0 is assumed.
Example Jovita is planning to invest $10,000 in equipment which is expected
to
last
10
years with no salvage value. The interest rate is 5%. What is the prin-
cipal payment at the end
of
year 4 and year 8?
At the end
of
year 4: = PPMT(5%,4,1 0,
-10000)
At the end
of
year
8:
= PPMT(5%,8,10,
-10000)
PV (Present Value)
Calculates the present value
of
a future series
of
equal cash flows and a single
lump sum
in
the last period at a constant interest rate.
=PV(rate, "per, pmt,
fv,
type)
rate Interest rate per compounding period.
nper Total number
of
periods.
pmt Cash flow at regular intervals. Negative numbers represent
payments (cash outflows), and positive numbers represent income.
fv Future value or cash balance at the end
of
the last period.
type (optional entry) Enter
0 if payments are due at the
end
of
the
compounding period, and 1
if
payments are due
at
the start
of
each compounding period.
If
omitted, 0 is assumed.
709
710
APPENDIX A Using Spreadsheets and Microsoft Excel©
There are two primary differences between the
PV
function and the NPV func-
tion:
PV allows for end or beginning
of
period cash flows, and PV requires that
all amounts have the same value, whereas they may vary for the
NPV function.
Example Jose
is
considering leasing a car for $300 a month for 3 years
(36 months). After the 36-month lease, he can purchase the car for
$12,000.
Using
an interest rate
of
8% per year, find the present value
of
this option.
Present value: =
PV(8%/12,36,
- 300,
-12000)
Note the minus signs on the pmt and
fv
amounts.
RAND
(Random
Number)
Returns
an
evenly distributed number that
is
(1) 2': 0 and <
1;
(2) 2': 0 and < 100;
or
(3) between two specified numbers.
=RANDO
=RANDO*100
=RANDO*(b-a)+a
a = minimum integer to be generated
b = maximum integer to be generated
for range 0 to 1
for range
0 to 100
for range a to b
The Excel function RANDBETWEEN(a,b) may also be used to obtain a ran-
dom number between two values.
Example Grace needs random numbers between 5 and 10 with 3 digits after
the decimal. What
is
the Excel function? Here a = 5 and b = 10.
Random number:
=RANDO*5
+ 5
Example Randi wants to generate random numbers between the limits
of
-10
and 25. What is the Excel function?
The
minimum and maximum values are
a = - 10 and b = 25, so b - a = 25 -
(-10)
= 35.
Random number: =
RANDO*35 - 10
RATE
(Interest Rate)
Calculates the interest rate
per
compounding period for a series
of
payments or
Incomes.
=RATE(nper, pmt,
pv,
fv,
type, guess)
nper Total number
of
periods.
pmt
Payment
amount
made each compounding period.
pv
Present
value.
fv Future value (not including the
pmt
amount).
SECTION
A.3
Excel Functions Important
to
Engineering Economy
type (optional entry) Enter
0 for payments due at the end
of
the
compounding period, and 1
if
payments are due at the start
of
each compounding period.
If
omitted, 0 is assumed.
guess (optional entry)
To
minimize computing time, include a
g
ue
ssed interest rate.
If
a value
of
guess is not specified,
the f
un
ction will assume a rate
of
10%. This function
usually converges to a solution,
if
the rate is between 0%
and 100%.
Example Mary wants to start a savings account at a bank. She will make an ini-
tial deposit
of
$1000 to open the account and plans to deposit $100 at the begin-
ning
of
each month. She plans to do this for the next 3 years (36 months).
At
the
end
of
3 years, she wants to have at least $5000. What is the minimum interest
required to achieve this result?
Interest rate: = RATE(36, -
100, - 1000,5000,1)
SLN
(Straight
Line
Depreciation)
Calculates the straight line depreciation
of
an asset for a given year.
=SLN(cost,
salvage, life)
cost
salvage
life
First cost or basis
of
the asset.
Salvage value.
Depreciation life.
Example Maria purchased a printing machine for $100,000. The machine has
an allowed depreciation life
of
8 years and an estimated salvage value
of
$15,000. What is the depreciation each year?
Depreciation:
= SLN(100000,15000,
8)
SYD
(Sum
-of-Year-Digits Depreciation)
Calculates the sum-of-year-digits depreciation
of
an asset for a given year.
=SYD(cost,
salvage,
life,
period)
cost
salvage
life
period
First cost or basis
of
the asset.
Salvage value.
Depreciation life.
The year for which the depreciation is sought.
Example Jack bought equipment for $100,000 which has a depreciation life
of
J 0 years. The salvage value is $10,000. What
is
the depreciation for year 1 and
year 9?
Depreciation for year
1:
=SYD(100000,10000,1O,l)
Depreciation for year
9:
=SYD(100000,10000,10,9)
711
712
APPENDIX A Using Spreadsheets
and
Microsoft
ExceJ
©
VDB (Variable Declining Balance)
Calculates the depreciation using the declining balance method with a
sw
itch
to
straight line depreciation in the year in which straight line has a larger deprecia-
tion amount. This function automatically implements the switch from
DB
to
SL depreciation, unless specifically instructed
to
not switch.
=VDB
(cost, salvage, life, starCperiod, end_period, factor, no_switch)
cost
salvage
life
start_period
end_period
factor
First cost
of
the asset.
Salvage value.
Depreciation lif
e.
First period for depreciation to be calculated.
Last period for depreciation to be calculated.
(optional entry)
If
omitted, the function will use the
double declining rate
of
2/
n, or twice the straight
line rate.
Other entries define the declining balance
method, for example,
l.5
for 150% declining balance.
(optional entry)
If
omitted or entered as FALSE, the
function will switch from declining balance to
straight line depreciation when the latter is greater
than DB depreciation.
If
entered as TRUE, the
function will not switch to
SL depreciation at any
time during the depreciation life.
Example Newly purchased equipment with a first cost
of
$300,000 has a
depreciable life
of
10
years with no salvage value. Calculate the 175% declining
balance depreciation for the first year and the ninth year
if
switching
to
SL de-
preciation is acceptable, and
if
switching is not permitted.
Depreciation for first year, with switching: =VDB(300000,0,10,0,1,1.75)
Depreciation for ninth year, with switching: =VDB(300000,0,1O,8,9,1.75)
Depreciation for first year,
no
switching: = VDB(300000,0, 1 0,0, 1,1.75, TR UE)
Depreciation for ninth year,
no
switching: = VDB(300000,0, 10,8,9, 1.75,TRUE)
A.4
SOLVER-AN
EXCEL
TOOL
FOR BREAKEVEN
AND
"WHAT
IF?" ANALYSIS
SOLVER is a powerful tool used to change the value in one or more cells based
upon a specified (target) cell value.
It
is especially helpful in performing
breakeven and sensitivity analysis
to
answer "what
if"
questions. The SOLVER
template is shown in Figure
A-2.
Set Target Cell box. Enter a cell reference or name. The target cell itself must
contain a formula or function. The value
in
the cell can be maximized (Max),
minimized (Min), or restricted
to
a specified value (Value of).
By
Changing Cells box. Enter the cell reference for each cell
to
be adjusted,
using commas between nonadjacent cells. Each cell must be directly or indirectly
related
to
the target cell. SOLVER proposes a value for the changing cell based
SECTION A.S List
of
Excel Financial Functions
S~t
Target
Cell:
1
$B$9
::J
Equal
To:
r-
t1ax
r 1
"iD.
r
:Lalue
of:
]0
~v
Changing
Cells:
......"..~....,.."=.,,..
=
:::1
',Lbject
to the Constraints:
---
"'="~~-."..
~--
--"
~
!2.elete
on input provided about the target cell.
The
Guess button will list a
ll
possible
changing cells related to the target cell.
Subject
to
the Constraints box. Enter any constraints that may apply, for ex-
ample, $C$I
< $50,000. Integer and binary variables are determined
in
this box.
Options box. Choices here allow the user to specify various parameters
of
the
solution: maximum time and number
of
iterations allowed, the precision and tol-
erance
of
the values determined, and the convergence requirements as the final
solution is determined. Also, linear and nonlinear model assumptions can
be
set here. If integer
or
binary variables are involved, the tolerance option must
be set
to
a small numbel; say, 0.0001. This is especially important for the binary
variables when selecting from independent projects (Chapter 12).
If
tolerance
remains at the default value
of
50/0,
a project may be incorrectly included in the
solution set at a very low level.
SOLVER Results box. This appears after Solve
is
clicked and a solution
appears.
It
is
possible,
of
course, that no solution can be found for the scenario
described.
It
is
possible to update the spreadsheet by clicking Keep Solver Solu-
tion,
or
return to the original entries using Restore Original Values.
A.S
LIST
OF EXCEL FINANCIAL FUNCTIONS
Here is a listing and brief description
of
the output
of
all Excel financial func-
tions. Not a
ll
these functions are available on all versions
of
Microsoft Excel.
The Add-ins command can help you determine if the function is available on the
system you are using.
ACCRINT
ACCRINTM
AMORDEGRC
AMORLlNC
COUPDAYBS
Returns the accrued interest for a security that pays
periodic interest.
Returns the accrued interest for a security that pays interest
at maturity.
Returns the depreciation for each accounting period.
Returns the depreciation for each accounting period.
Returns the number
of
days from the beginning
of
the coupon
period to the settlement date.
Figure
A-2
Excel's
SOLVER
template.
713
714
APPENDlXA
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
CUMIPMT
CUMPRINC
DB
DDB
DISC
DOLLARDE
DOLLARFR
DURATION
EFFECT
FY
FYSCHEDULE
INTRATE
IPMT
IRR
ISPMT
MDURATION
MIRR
NOMINAL
NPER
NPY
ODDFPRICE
ODDFYlELD
ODDLPRICE
ODDLYlELD
Using Spreadsheets
and
Microsoft Excel©
Returns the number
of
days
in
the coupon period that contains the
settlement date.
Returns the number
of
days from the settlement date to the next
coupon date.
Returns the next coupon date after the settlement date.
Returns the number
of
coupons payable between the settlement
date and maturity date.
Returns the previous coupon date before the settlement date.
Returns the cumulative interest paid between two periods.
Returns the cumulative principal paid on a loan between
two periods.
Returns the depreciation
of
an asset for a specified period using
the fixed declining balance method.
Returns the depreciation
of
an
asset for a specified period using
the double declining balance method or some other
method you specify.
Returns the discount rate for a security.
Converts a dollar price expressed as a fraction to a dollar price
expressed as a decimal number.
Converts a dollar price expressed
as
a decimal number to a dollar
price expressed as a fraction.
Returns the annual duration
of
a secUlity with periodic
interest payments.
Returns the effective annual interest rate.
Returns the future value
of
an investment.
Returns the future value
of
an initial principal after applying a
series
of
compound interest rates.
Returns the interest rate for a fully invested security.
Returns the interest payment for an investment for a given period.
Returns the internal rate
of
return for a series
of
cash flows.
Returns the interest paid during a specific period
of
an
investment.
(Provides compatibility with Lotus 1-2-3.)
Returns the Macauley modified duration for a security with an
assumed par value
of
$100.
Returns the internal rate
of
return where positive and negative
cash flows are financed at different rates.
Returns the annual nominal interest rate.
Returns the number
of
periods for
an
investment.
Returns the net present va
lu
e
of
an investment based on a series
of
periodic cash flows and a discount rate.
Returns the price per
$100 face value
of
a security with an odd
first period.
Returns the yield
of
a security with an odd first period.
Returns the price per
$ I 00 face va
lu
e
of
a security with an odd
last period.
Returns the yield
of
a security with an odd last period.