PROBLEM 6.8
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + CF – CF NET CF
0 -$12.00 -$12.00
1 -$1.00 -$1.00
2$5.00 $5.00
3$2.00 $2.00
4$5.00 $5.00
5$5.00 $5.00
6$2.00 $2.00
7$5.00 $5.00
IRR = IRR(NET_CF_COLUMN)
IRR = 15.70%
b DECISION RULE
IF IRR >= MARR (18%), ACCEPT; OTHERWISE, REJECT
c IMAGINEERING SHOULD NOT INVEST
PROBLEM 6.9
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + CF – CF NET CF
0 -$5,000.00 -$5,000.00
1 -$200.00 -$200.00
2$2,000.00 -$300.00 $1,700.00
3$4,000.00 -$600.00 $3,400.00
4$3,000.00 -$1,000.00 $2,000.00
5$1,600.00 -$1,500.00 $100.00
IRR = IRR(NET_CF_COLUMN)
IRR = 11.45%
b DECISION RULE
IF IRR >= MARR (6%), ACCEPT; OTHERWISE, REJECT
c DURATECH SHOULD IMPLEMENT THE PROCESS IMPROVEMENT
PROBLEM 6.10
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + CF – CF NET CF
0 -$6,000.00 -$6,000.00
1$1,000.00 $1,000.00
2$1,000.00 $1,000.00
3$1,000.00 $1,000.00
4$1,000.00 $1,000.00
5$1,000.00 $1,000.00
6$3,000.00 $3,000.00
IRR = IRR(NET_CF_COLUMN)
IRR = 7.45%
b DECISION RULE
IF IRR >= MARR (8%), ACCEPT; OTHERWISE, REJECT
c MAYBERRY SHOULD NOT IMPLEMENT THE DESIGN CHANGE
PROBLEM 6.11
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + CF – CF NET CF
0 -$1,000.00 -$1,000.00
1$600.00 -$300.00 $300.00
2$600.00 -$300.00 $300.00
3$700.00 -$300.00 $400.00
4$700.00 -$300.00 $400.00
5$700.00 -$300.00 $400.00
IRR = IRR(NET_CF_COLUMN)
IRR = 21.84%
b DECISION RULE
IF IRR >= MARR (10%), ACCEPT; OTHERWISE, REJECT
c HOME INNOVATION SHOULD PURSUE THE NEW PRODUCT
PROBLEM 6.12
a EOY CF
0 -$70,000.00
1$30,000.00
2$30,000.00
3$30,000.00
4$30,000.00
5$30,000.00
6$32,000.00
IRR = IRR(CF_COLUMN)
IRR = 36.35%
b IF IRR >= MARR (10%), THEN ATTRACTIVE; OTHERWISE, UNATTRACTIVE
c INVESTMENT IS JUSTIFIED
PROBLEM 6.13
a EOY + CF – CF NET CF
0 -$100,000.00 -$100,000.00
1$12,000.00 -$2,000.00 $10,000.00
2$12,000.00 -$2,000.00 $10,000.00
3$12,000.00 -$2,000.00 $10,000.00
4$12,000.00 -$2,000.00 $10,000.00
5$12,000.00 -$2,000.00 $10,000.00
6$12,000.00 -$2,000.00 $10,000.00
7$12,000.00 -$2,000.00 $10,000.00
8$12,000.00 -$2,000.00 $10,000.00
9$12,000.00 -$2,000.00 $10,000.00
10 $12,000.00 -$2,000.00 $10,000.00
11 $12,000.00 -$2,000.00 $10,000.00
12 $12,000.00 -$2,000.00 $10,000.00
13 $12,000.00 -$2,000.00 $10,000.00
14 $12,000.00 -$2,000.00 $10,000.00
15 $12,000.00 -$2,000.00 $10,000.00
IRR = IRR(NET_CF_COLUMN)
IRR = 5.56%
b DECISION RULE
IF IRR >= MARR (5%), ACCEPT; OTHERWISE, REJECT
c BAILEY SHOULD BUY THE GANG PUNCH
PROBLEM 6.14
CURRENT ANNUAL INSURANCE PREMIUM = ($700,000/$100)*$1.00 = $7,000
NEW ANNUAL INSURANCE PREMIUM = ($720,000/$100)*$0.40 = $2,880
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + CF – CF NET CF
0 -$20,000.00 -$20,000.00
1$4,120.00 -$400.00 $3,720.00
2$4,120.00 -$400.00 $3,720.00
3$4,120.00 -$400.00 $3,720.00
4$4,120.00 -$400.00 $3,720.00
5$4,120.00 -$400.00 $3,720.00
6$4,120.00 -$400.00 $3,720.00
7$4,120.00 -$400.00 $3,720.00
8$4,120.00 -$400.00 $3,720.00
9$4,120.00 -$400.00 $3,720.00
10 $4,120.00 -$400.00 $3,720.00
11 $4,120.00 -$400.00 $3,720.00
12 $4,120.00 -$400.00 $3,720.00
13 $4,120.00 -$400.00 $3,720.00
14 $4,120.00 -$400.00 $3,720.00
15 $4,120.00 -$400.00 $3,720.00
16 $4,120.00 -$400.00 $3,720.00
17 $4,120.00 -$400.00 $3,720.00
18 $4,120.00 -$400.00 $3,720.00
19 $4,120.00 -$400.00 $3,720.00
20 $4,120.00 -$400.00 $3,720.00
b DECISION RULE
IF IRR >= MARR (15%), ACCEPT; OTHERWISE, REJECT
c EDDIE SHOULD BUY THE SPRINKLER SYSTEM
PROBLEM 6.15
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + CF – CF NET CF
0 -$10,000.00 -$10,000.00
1$30,000.00 -$25,000.00 $5,000.00
2$30,000.00 -$25,000.00 $5,000.00
3$33,000.00 -$25,000.00 $8,000.00
IRR = IRR(NET_CF_COLUMN)
IRR = 32.91%
b DECISION RULE
IF IRR >= MARR (24%), ACCEPT; OTHERWISE, REJECT
c NANCY SHOULD BUY THE TRUCK
PROBLEM 6.17
MARR = 10%
EOY CF
0 -$70,000
1$20,000
2$19,000
3$18,000
4$17,000
5$16,000
6$15,000
7$14,000
8$13,000
9$12,000
10 $11,000
11 $10,000
12 $9,000
13 $8,000
14 $7,000
15 $6,000
16 $5,000
17 $4,000
18 $3,000
19 $2,000
20 $1,000
IRR = 22.272% > MARR
RECOMMEND THE INVESTMENT
PROBLEM 6.18
MARR = 15%
INVESTMENT $20,000 EOY CF
SALVAGE VALUE $5,000 0 -$20,000
LIFE (YEARS) 6 1 $6,200
ANNUAL INCOME $8,500 2 $6,200
ANNUAL EXPENSES
$2,300 3 $6,200
NET REVENUE $6,200 4 $6,200
5 $6,200
6 $11,200
IRR = 24.204% =IRR(H6:H12)
24.204% =RATE(D7,D10,-D5,D6)
SINCE IRR > MARR, RECOMMEND THE INVESTMENT
PROBLEM 6.20
a IRR > MARR
b IRR = MARR
c IRR < MARR
d IRR > MARR
e IRR = MARR
f IRR < MARR
g IRR > MARR
h IRR = MARR
i IRR < MARR
PROBLEM 6.21
SOLUTION USING EXCEL’S IRR FUNCTION
IN K$
EOY BUILDING FURNITURE O&M REVENUE NET CF
0 -$8,000.00 -$700.00 -$8,700.00
1 -$800.00 $1,460.00 $660.00
2 -$800.00 $1,752.00 $952.00
3 -$800.00 $2,044.00 $1,244.00
4 -$800.00 $2,336.00 $1,536.00
5 -$700.00 -$800.00 $2,628.00 $1,128.00
6 -$800.00 $2,628.00 $1,828.00
7 -$800.00 $2,628.00 $1,828.00
8 -$800.00 $2,628.00 $1,828.00
9 -$800.00 $2,628.00 $1,828.00
10 -$700.00 -$800.00 $2,628.00 $1,128.00
11 -$800.00 $2,628.00 $1,828.00
12 -$800.00 $2,628.00 $1,828.00
13 -$800.00 $2,628.00 $1,828.00
14 -$800.00 $2,628.00 $1,828.00
15 $900.00 -$800.00 $2,628.00 $2,728.00
IRR = IRR(NET_CF_COLUMN)
IRR = 13.68%
MARR = 12.00%
DECISION: VALUE LODGES SHOULD BUILD THE MOTEL
PROBLEM 6.22
SOLUTION USING EXCEL’S IRR FUNCTION
EOY FILTER LOAN O&M SAVINGS NET CF
0 -$100,000.00 $50,000.00 -$50,000.00
1 -$14,915.78 -$20,000.00 $50,000.00 $15,084.22
2 -$14,915.78 -$21,500.00 $50,000.00 $13,584.22
3 -$14,915.78 -$23,000.00 $50,000.00 $12,084.22
4 -$14,915.78 -$24,500.00 $50,000.00 $10,584.22
5 -$14,915.78 -$26,000.00 $50,000.00 $9,084.22
6 -$27,500.00 $50,000.00 $22,500.00
7 -$29,000.00 $50,000.00 $21,000.00
8 -$30,500.00 $50,000.00 $19,500.00
9 -$32,000.00 $50,000.00 $18,000.00
10 $10,000.00 -$33,500.00 $50,000.00 $26,500.00
IRR = IRR(NET_CF_COLUMN)
IRR = 26.83%
MARR = 12.00%
DECISION: BAON CHEMICALS SHOLD PURCHASE THE FILTER
PROBLEM 6.23
SOLUTION USING EXCEL’S IRR FUNCTION
SPRINKLER SYSTEM
EOY SPRINKLERS LOAN O&M SAVINGS NET CF
0 -$30,000.00 $30,000.00 $0.00
1 -$9,877.03 -$9,000.00 $15,000.00 -$3,877.03
2 -$9,877.03 -$9,000.00 $15,000.00 -$3,877.03
3 -$9,877.03 -$9,000.00 $15,000.00 -$3,877.03
4 -$9,877.03 -$9,000.00 $15,000.00 -$3,877.03
5 -$9,000.00 $15,000.00 $6,000.00
6 -$9,000.00 $15,000.00 $6,000.00
7 -$9,000.00 $15,000.00 $6,000.00
8 -$9,000.00 $15,000.00 $6,000.00
9 $2,000.00 -$9,000.00 $15,000.00 $8,000.00
IRR = IRR(NET_CF_COLUMN)
IRR = 17.23%
MARR = 15.00%
REALTURF SHOULD PURCHASE THE SPRINKLER SYSTEM
PROBLEM 6.24
YEAR 5 ADDITION = $5,000 COMPUTER SALVAGE VALUE
LOAN PAYMENTS = 25000*(A|P 15,3) = 25000*(0.43798) = $10,949.50
ALTERNATIVELY, LOAN PAYMENTS = PMT(15%,3,25000) = $10,949.42
THE FACTOR-BASED VALUE IS USED IN THE CASH FLOW PROFILE BELOW
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + INV CF – INV CF + LOAN CF – LOAN CF NET CF
0 -$100,000.00 $25,000.00 -$75,000.00
1$55,000.00 -$25,000.00 -$10,949.50 $19,050.50
2$55,000.00 -$25,000.00 -$10,949.50 $19,050.50
3$55,000.00 -$25,000.00 -$10,949.50 $19,050.50
4$55,000.00 -$25,000.00 $30,000.00
5$60,000.00 -$25,000.00 $35,000.00
IRR = IRR(NET_CF_COLUMN)
IRR = 16.52%
b DECISION RULE
IF IRR >= MARR (18%), ACCEPT; OTHERWISE, REJECT
c GALVANIZED PRODUCTS SHOULD NOT PURCHASE THE NEW COMPUTER SYSTEM
PROBLEM 6.25
YEAR 7 ADDITION = $7,500 SYSTEM SALVAGE VALUE
LOAN PAYMENTS = 20000(F|P,8,1)(A|P 8,3) = 20000(1.08000)(0.38803) = $8,381.45
ALTERNATIVELY, LOAN PAYMENTS = PMT(8%,3,20000*1.08000) = $8,381.52
THE FACTOR-BASED VALUE IS USED IN THE CASH FLOW PROFILE BELOW
a SOLUTION USING EXCEL’S IRR FUNCTION
EOY + INV CF – INV CF + LOAN CF – LOAN CF NET CF
0 -$40,000.00 $20,000.00 -$20,000.00
1$12,000.00 -$2,000.00 $10,000.00
2$12,000.00 -$2,000.00 -$8,381.45 $1,618.55
3$12,000.00 -$2,000.00 -$8,381.45 $1,618.55
4$12,000.00 -$2,000.00 -$8,381.45 $1,618.55
5$12,000.00 -$2,000.00 $10,000.00
6$12,000.00 -$2,000.00 $10,000.00
7$19,500.00 -$2,000.00 $17,500.00
IRR = IRR(NET_CF_COLUMN)
IRR = 25.16%
b DECISION RULE
IF IRR >= MARR (10%), ACCEPT; OTHERWISE, REJECT
c AEROTRON ELECTRONICS SHOULD PURCHASE THE WATER FILTRATION SYSTEM
PROBLEM 6.26
SOLUTION USING EXCEL’S IRR FUNCTION
DELIVERY VAN
EOY VAN LOAN O&M SAVINGS NET CF
0 -$18,000.00 $12,000.00 -$6,000.00
1$0.00 -$3,305.88 -$700.00 $3,800.00 -$205.88
2$0.00 -$3,305.88 -$700.00 $3,800.00 -$205.88
3$0.00 -$3,305.88 -$700.00 $3,800.00 -$205.88
4$0.00 -$3,305.88 -$700.00 $3,800.00 -$205.88
5$0.00 -$700.00 $3,800.00 $3,100.00
6$500.00 -$700.00 $3,800.00 $3,600.00
IRR = IRR(NET_CF_COLUMN)
IRR = -0.35%
MARR = 6.00%
DELTA DAWN’S SHOULD NOT PURCHASE THE DELIVERY VAN
PROBLEM 6.27
a THREE SIGN CHANGES, THEREFORE, EITHER 3 OR 1 POSITIVE REAL ROOTS GRAPHIC PRESENTATION OF PW VERSUS i
b EOY CF CUM CF i PW
0 -$100.00 -$100.00 0.00 $65.00
1 $25.00 -$75.00 0.01 $59.30
2 $25.00 -$50.00 0.02 $53.91
3 $60.00 $10.00 0.03 $48.78
4 -$30.00 -$20.00 0.04 $43.92
5 $60.00 $40.00 0.05 $39.30
6 $25.00 $65.00 0.06 $34.91
0.07 $30.73
MULTIPLE SIGN CHANGES, THEREFORE, NO CONCLUSION CAN BE DRAWN 0.08 $26.75
0.09 $22.96
c EOY CF 0.10 $19.34
0 -$100.00 0.11 $15.90
1 $25.00 0.12 $12.60
2 $25.00 0.13 $9.46
3 $60.00 0.14 $6.45
4 -$30.00 0.15 $3.58
5 $60.00 0.16 $0.83
6 $25.00 0.17 -$1.80
0.18 -$4.33
IRR = IRR(CF_COLUMN) 0.19 -$6.75
IRR = 16.31% 0.20 -$9.07
THE GRAPH TO THE RIGHT INDICATES THE PRESENCE OF A SINGLE ROOT 0.21 -$11.29
0.22 -$13.43
d IRR >= MARR; THEREFORE, ATTRACTIVE 0.23 -$15.48
0.24 -$17.46
0.25 -$19.35
0.26 -$21.18
0.27 -$22.94
0.28 -$24.63
0.29 -$26.26
0.30 -$27.83
0.31 -$29.35
0.32 -$30.81
0.33 -$32.22
0.34 -$33.58
0.35 -$34.90
0.36 -$36.17
0.37 -$37.40
0.38 -$38.59
0.39 -$39.74
0.40 -$40.85
0.41 -$41.93
0.42 -$42.98
0.43 -$43.99
0.44 -$44.97
0.45 -$45.92
0.46 -$46.85
0.47 -$47.74
0.48 -$48.61
0.49 -$49.45
0.50 -$50.27
-60
-40
-20
0
20
40
60
80
0% 10% 20% 30% 40% 50% 60%
VALUES OF PW
VALUES OF i
PW VERSUS i