PROBLEM 6.66
a SOLUTIONS USES GOAL SEEK TO DETERMINE ERR
MARR IS 13.5%
14.07% 13.50%
EOY – CF + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF -CF FW OF +CF FW OF CF
0 -$1,400.00 -$1,400.00 7.20501 6.68248 -$10,087.02 $0.00 -$10,087.02
1 $0.00 6.31625 5.88765 $0.00 $0.00 $0.00
2 $500.00 $500.00 5.53712 5.18736 $0.00 $2,593.68 $2,593.68
3 $500.00 $500.00 4.85409 4.57036 $0.00 $2,285.18 $2,285.18
4 $500.00 $500.00 4.25532 4.02675 $0.00 $2,013.37 $2,013.37
5 $500.00 $500.00 3.73041 3.54780 $0.00 $1,773.90 $1,773.90
6 $0.00 3.27025 3.12581 $0.00 $0.00 $0.00
7 $500.00 $500.00 2.86685 2.75402 $0.00 $1,377.01 $1,377.01
8 $600.00 $600.00 2.51321 2.42645 $0.00 $1,455.87 $1,455.87
9 $700.00 $700.00 2.20320 2.13784 $0.00 $1,496.49 $1,496.49
10 $800.00 $800.00 1.93143 1.88356 $0.00 $1,506.85 $1,506.85
11 $900.00 $900.00 1.69318 1.65952 $0.00 $1,493.57 $1,493.57
12 -$1,000.00 -$1,000.00 1.48432 1.46214 -$1,484.32 $0.00 -$1,484.32
13 -$2,000.00 -$2,000.00 1.30122 1.28823 -$2,602.44 $0.00 -$2,602.44
14 -$3,000.00 -$3,000.00 1.14071 1.13500 -$3,422.13 $0.00 -$3,422.13
15 $1,600.00 $1,600.00 1.00000 1.00000 $0.00 $1,600.00 $1,600.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i’ INTEREST RATE (THE ERR)
ERR =14.07%
NOTE: EXCEL’S SOLVER RESULTS IN THE SAME SOLUTION (14.07%)
b IF ERR >= MARR (13.5%), THEN ACCEPT; OTHERWISE, REJECT
c QRU SHOULD BUY THE NEW PATTERNING ATTACHMENT
PROBLEM 6.67
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
a SOLUTIONS USES GOAL SEEK TO DETERMINE ERR
MARR IS 10%/YEAR
10.74% 10.00%
EOY + INV CF – INV CF + LOAN CF – LOAN CF NET – CF NET + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF -CF FW OF +CF FW OF CF
0-$40,000.00 $20,000.00 -$40,000.00 $20,000.00 -$20,000.00 1.66553 1.61051 -$66,621.21 $32,210.20 -$34,411.01
1$12,000.00 -$2,000.00 -$2,000.00 $12,000.00 $10,000.00 1.50398 1.46410 -$3,007.96 $17,569.20 $14,561.24
2$12,000.00 -$2,000.00 -$8,381.45 -$10,381.45 $12,000.00 $1,618.55 1.35810 1.33100 -$14,099.04 $15,972.00 $1,872.96
3$12,000.00 -$2,000.00 -$8,381.45 -$10,381.45 $12,000.00 $1,618.55 1.22637 1.21000 -$12,731.48 $14,520.00 $1,788.52
4$12,000.00 -$2,000.00 -$8,381.45 -$10,381.45 $12,000.00 $1,618.55 1.10742 1.10000 -$11,496.58 $13,200.00 $1,703.42
5$12,000.00 -$2,000.00 1.00000 1.00000
6$12,000.00 -$2,000.00 0.90300 0.90909
7$19,500.00 -$2,000.00 -$2,000.00 $19,500.00 $17,500.00 0.81542 0.82645 -$1,630.83 $16,115.70 $14,484.87
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i‘ INTEREST RATE (THE ERR)
ERR = 10.74%
NOTE: EXCEL’S SOLVER RESULTS IN THE SAME SOLUTION (10.74%)
b IF ERR >= MARR (10%), THEN ACCEPT; OTHERWISE, REJECT
c AEROTRON ELECTRONICS SHOULD PURCHASE THE WATER FILTRATION SYSTEM
PROBLEM 6.68
NOTE: THIS PROBLEM INCLUDES TWO SOLUTIONS. ONE SOLUTION USES THE POSITIVE CASH
FLOWS (Rt) AND NEGATIVE CASH FLOWS (Ct) BEFORE NETTING. THE OTHER SOLUTION USES THE CASH FLOWS
AFTER NETTING (At = Rt – Ct). IN EACH CASE, THE NUMERIC RESULTS DIFFER BUT THE DECISIONS ARE
CONSISTENT. SUBSEQUENT PROBLEMS USE ONLY THE NETTED CASH FLOWS.
a SOLUTION USING Rt AND Ct CASH FLOWS BEFORE NETTING
MARR IS 10%/YEAR
EOY – CF + CF NET CF
0 -$1,000.00 -$1,000.00
1 -$300.00 $600.00 $300.00
2 -$300.00 $600.00 $300.00
3 -$300.00 $700.00 $400.00
4 -$300.00 $700.00 $400.00
5 -$300.00 $700.00 $400.00
DUE TO MULTIPLE NEGATIVE VALUES IN THE NET CF SERIES, EXCEL’S
RATE FUNCTION IS NOT USED TO COMPUTE ERR DIRECTLY.
DUE TO MULTIPLE NEGATIVE VALUES IN THE NET CF SERIES, EXCEL’S
MIRR FUNCTION IS NOT USED TO COMPUTE ERR DIRECTLY.
SOLUTION INTERPOLATING INTEREST TABLE RATES (WITH EXCEL’S FV FUNCTION)
i% FV
12.00% $325.86 =FV(B28,5,300,1000)+FV(10%,5,,-NPV(10%,D14:D18))
ERR $0.00
15.00% -$40.01 =FV(B30,5,300,1000)+FV(10%,5,,-NPV(10%,D14:D18))
ERR = 14.67% =B28+(B30-B28)*(C29-C28)/(C30-C28)
SOLUTION USING EXCEL’S IRR FUNCTION
EOY – CF
0 -$1,000.00
1 -$300.00
3 -$300.00
4 -$300.00
5 $3,694.06 =-300+FV(10%,5,,-NPV(10%,D14:D18))
ERR = 14.68% =IRR(C35:C40)
SOLUTION USING EXCEL’S SOLVER
i = 14.68%
FV = $0.00 =FV(C44,5,300,1000)+FV(10%,5,,-NPV(10%,D14:D18))
SOLVER PARAMETERS
SET TARGET CELL: C45
EQUAL TO: VALUE OF: 0
BY CHANGING CELL: C44
CLICK “SOLVE”
SOLUTION USING EXCEL’S GOAL SEEK
i = 14.68%
FV = $0.00 =FV(C53,5,300,1000)+FV(10%,5,,-NPV(10%,D14:D18))
SOLVER PARAMETERS
BY CHANGING CELL: C53
CLICK “SOLVE”
b
c HOME INNOVATION SHOULD PURSUE THE NEW PRODUCT
a SOLUTION USING NETTED CASH FLOWS
MARR IS 10%/YEAR
EOY – CF + CF NET CF NET – CF NET + CF
0 -$1,000.00 -$1,000.00 -$1,000.00 $0.00
1 -$300.00 $600.00 $300.00 $0.00 $300.00
2 -$300.00 $600.00 $300.00 $0.00 $300.00
3 -$300.00 $700.00 $400.00 $0.00 $400.00
4 -$300.00 $700.00 $400.00 $0.00 $400.00
5 -$300.00 $700.00 $400.00 $0.00 $400.00
SOLUTION USING EXCEL’S RATE AND FV FUNCTIONS
ERR = 16.68% =RATE(5,,-1000,FV(10%,5,,-NPV(10%,G71:G75)))
SOLUTION USING EXCEL’S MIRR FUNCTION
ERR = 16.68% =MIRR(E70:E75,,10%)
SOLUTION INTERPOLATING INTEREST TABLE RATES (WITH EXCEL’S FV FUNCTION)
i% FV
15.00% $151.17 =FV(B85,5,,1000)+FV(10%,5,,-NPV(10%,G71:G75))
ERR $0.00
18.00% -$125.23 =FV(B87,5,,1000)+FV(10%,5,,-NPV(10%,G71:G75))
ERR = 16.64% =B85+(B87-B85)*(C86-C85)/(C87-C85)
SOLUTION USING EXCEL’S SOLVER
i = 16.68%
FV = $0.00 =FV(C91,5,,1000)+FV(10%,5,,-NPV(10%,G71:G75))
SOLVER PARAMETERS
SET TARGET CELL: C92
EQUAL TO: VALUE OF: 0
BY CHANGING CELL: C91
CLICK “SOLVE”
SOLUTION USING EXCEL’S GOAL SEEK
i = 16.68%
FV = $0.00 =FV(C100,5,,1000)+FV(10%,5,,-NPV(10%,G71:G75))
SOLVER PARAMETERS
SET TARGET CELL: C101
EQUAL TO: VALUE OF: 0
BY CHANGING CELL: C100
CLICK “SOLVE”
b
IF ERR (16.68%)>= MARR (10%), THEN ACCEPT; OTHERWISE, REJECT
c HOME INNOVATION SHOULD PURSUE THE NEW PRODUCT
PROBLEM 6.69
YEAR 5 ADDITION = $5,000 COMPUTER SALVAGE VALUE
LOAN PAYMENTS = 25000*(A|P 15%,3) = 25000*(0.43798) =
a SOLUTIONS USES GOAL SEEK TO DETERMINE ERR
MARR IS 18%/YEAR
9.21% 6.00%
EOY + INV CF – INV CF + LOAN CF – LOAN CF NET – CF NET + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF -CF FW OF +CF FW OF CF
0 -$100,000.00 $25,000.00 -$100,000.00 $25,000.00 -$75,000.00 1.55353 1.33823 -$155,352.50 $33,455.64 –$121,896.86
1 $55,000.00 -$25,000.00 -$10,949.50 -$35,949.50 $55,000.00 $19,050.50 1.42251 1.26248 -$51,138.44 $69,436.23 $18,297.79
2 $55,000.00 -$25,000.00 -$10,949.50 -$35,949.50 $55,000.00 $19,050.50 1.30254 1.19102 -$46,825.65 $65,505.88 $18,680.23
3 $55,000.00 -$25,000.00 -$10,949.50 -$35,949.50 $55,000.00 $19,050.50 1.19269 1.12360 -$42,876.58 $61,798.00 $18,921.42
4 $55,000.00 -$25,000.00 -$25,000.00 $55,000.00 $30,000.00 1.09210 1.06000 -$27,302.58 $58,300.00 $30,997.42
5 $60,000.00 -$25,000.00 -$25,000.00 $60,000.00 $35,000.00 1.00000 1.00000 -$25,000.00 $60,000.00 $35,000.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i’ INTEREST RATE (THE ERR)
ERR = 9.21%
NOTE: EXCEL’S SOLVER RESULTS IN THE SAME SOLUTION (9.21%)
b IF ERR >= MARR (18%), THEN ACCEPT; OTHERWISE, REJECT
c GALVANIZED PRODUCTS SHOULD NOT PURCHASE THE NEW COMPUTER SYSTEM
PROBLEM 6.70
ERR COMPARISONS MUST BE DONE INCREMENTALLY
THIS SOLUTION ASSUMES THAT NULL IS NOT AN OPTION WHICH IS CONSISTENT WITH
THE COST ONLY PROBLEM FORMULATION
MARR IS 20%
FOR AN INCREMENTAL ANALYSIS, THE ORDER MUST BE DETERMINED.
ORDER IS GATE1, GATE2, GATE3
GATE1 BECOMES THE “CURRENT BEST” AND GATE2 THE “CHALLENGER”
NEXT, THE INCREMENTAL CASH FLOWS FOR “CHALLENGER” – “CURRENT BEST” MUST BE DETERMINED
GATE 2 – GATE 1
16.81% 20.00%
EOY – CF + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF -CF FW OF +CF FW OF CF
0 -$4,000.00 -$4,000.00 2.17436 2.48832 -$8,697.44 $0.00 -$8,697.44
1 $900.00 $900.00 1.86151 2.07360 $0.00 $1,866.24 $1,866.24
2 $900.00 $900.00 1.59367 1.72800 $0.00 $1,555.20 $1,555.20
3 $900.00 $900.00 1.36437 1.44000 $0.00 $1,296.00 $1,296.00
4 $900.00 $900.00 1.16806 1.20000 $0.00 $1,080.00 $1,080.00
5 $2,900.00 $2,900.00 1.00000 1.00000 $0.00 $2,900.00 $2,900.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i’ INTEREST RATE (THE ERR)
ERR = 16.81%
SINCE THE ERR OF THE INCREMENT IS LESS THAN MARR (20%) THE INCREMENTAL INVESTMENT IS REJECTED
GATE 1 REMAINS THE CURRENT BEST
GATE 3 BECOMES THE CHALLENGER
GATE 3 – GATE 1
21.27% 20.00%
EOY – CF + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF -CF FW OF +CF FW OF CF
0 -$9,000.00 -$9,000.00 2.62267 2.48832 -$23,604.00 $0.00 -$23,604.00
1 $2,500.00 $2,500.00 2.16269 2.07360 $0.00 $5,184.00 $5,184.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
PROBLEM 6.71
ERR COMPARISONS MUST BE DONE INCREMENTALLY
THIS SOLUTION ASSUMES THAT NULL IS NOT AN OPTION; THE PROBLEM CAN ALSO BE WORKED
ASSUMING THAT NULL IS AN OPTION.
MARR IS 10%; ALL CASH FLOWS ARE IN $K
FOR AN INCREMENTAL ANALYSIS, THE ORDER MUST BE DETERMINED.
ORDER IS FIRST SATELLITE ONLY, SECOND SATELLITE ONLY, BOTH SATELLITES
FIRST ONLY BECOMES THE “CURRENT BEST” AND SECOND ONLY THE “CHALLENGER”
NEXT, THE INCREMENTAL CASH FLOWS FOR “CHALLENGER” – “CURRENT BEST” MUST BE DETERMINED
SECOND ONLY – FIRST ONLY
EOY SECOND ONLY FIRST ONLY INCR CF
0 -$850.00 -$750.00 -$100.00
1 -$120.00 -$150.00 $30.00
2 -$120.00 -$150.00 $30.00
3 -$120.00 -$150.00 $30.00
4 -$120.00 -$150.00 $30.00
5$2,105.00 $2,075.00 $30.00
ERR = MIRR(NET_CF_COLUMN,,MARR)
ERR = 12.87%
SINCE THE ERR OF THE INCREMENT IS GREATER THAN MARR (10%) THE INCREMENTAL INVESTMENT IS ACCEPTED
SECOND ONLY BECOMES THE NEW CURRENT BEST
BOTH BECOMES THE CHALLENGER
THUS THE CASH FLOWS OF BOTH – SECOND ONLY ARE NEEDED.
BOTH – SECOND ONLY
12.56% 10.00%
EOY BOTH SECOND ONLY INCR CF – CF + CF (F|P i’,n-t) (F|P MARR,n-t) FW OF -CF FW OF +CF FW OF CF
0 -$1,750.00 -$850.00 -$900.00 -$900.00 1.80695 1.61051 –$1,626.26 $0.00 -$1,626.26
1 -$400.00 -$120.00 -$280.00 -$280.00 1.60530 1.46410 –$449.49 $0.00 -$449.49
2 -$400.00 -$120.00 -$280.00 -$280.00 1.42616 1.33100 –$399.32 $0.00 -$399.32
3 -$400.00 -$120.00 -$280.00 -$280.00 1.26701 1.21000 –$354.76 $0.00 -$354.76
4 -$400.00 -$120.00 -$280.00 -$280.00 1.12561 1.10000 –$315.17 $0.00 -$315.17
5$5,250.00 $2,105.00 $3,145.00 $3,145.00 1.00000 1.00000 $0.00 $3,145.00 $3,145.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i’ INTEREST RATE (THE ERR)
ERR = 12.56%
SINCE THE IRR OF THE INCREMENT IS GREATER THAN MARR (10%) THE INCREMENTAL INVESTMENT IS ACCEPTED
BOTH BECOMES THE NEW CURRENT BEST
THERE ARE NO MORE CHALLENGERS TO CONSIDER.
THUS CALISTO SHOULD CONTRACT FOR BOTH SATELLITES.
PROBLEM 6.72
ERR COMPARISONS MUST BE DONE INCREMENTALLY
THIS SOLUTION ASSUMES THAT NULL IS AN OPTION; THE PROBLEM CAN ALSO BE WORKED
ASSUMING THAT NULL IS NOT AN OPTION.
MARR IS 12%
FOR AN INCREMENTAL ANALYSIS, THE ORDER MUST BE DETERMINED.
ORDER IS NULL, A, B
NULL BECOMES THE “CURRENT BEST” AND A THE “CHALLENGER”
NEXT, THE INCREMENTAL CASH FLOWS FOR “CHALLENGER” – “CURRENT BEST” MUST BE DETERMINED
THUS, THE CASH FLOWS FOR A-NULL ARE NEEDED
PROJECT A
12.30% 12.00%
EOY – CF + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF –CF FW OF +CF FW OF CF
0 -$50,000.00 -$50,000.00 10.17337 9.64629 -$508,668.32 $0.00 -$508,668.32
1 -$12,500.00 $20,000.00 $7,500.00 9.05923 8.61276 -$113,240.42 $172,255.23 $59,014.81
2 -$12,500.00 $20,000.00 $7,500.00 8.06711 7.68997 -$100,838.93 $153,799.32 $52,960.38
3 -$12,500.00 $20,000.00 $7,500.00 7.18365 6.86604 -$89,795.59 $137,320.82 $47,525.23
4 -$12,500.00 $20,000.00 $7,500.00 6.39693 6.13039 -$79,961.65 $122,607.87 $42,646.22
5 -$12,500.00 $20,000.00 $7,500.00 5.69637 5.47357 -$71,204.68 $109,471.32 $38,266.64
6 -$12,500.00 $20,000.00 $7,500.00 5.07254 4.88711 -$63,406.72 $97,742.25 $34,335.52
7 -$12,500.00 $20,000.00 $7,500.00 4.51702 4.36349 -$56,462.75 $87,269.86 $30,807.11
8 -$12,500.00 $20,000.00 $7,500.00 4.02234 3.89598 -$50,279.25 $77,919.52 $27,640.27
9 -$12,500.00 $20,000.00 $7,500.00 3.58184 3.47855 -$44,772.94 $69,571.00 $24,798.06
10 -$12,500.00 $20,000.00 $7,500.00 3.18957 3.10585 -$39,869.64 $62,116.96 $22,247.32
11 -$12,500.00 $20,000.00 $7,500.00 2.84027 2.77308 -$35,503.33 $55,461.58 $19,958.24
12 -$12,500.00 $20,000.00 $7,500.00 2.52922 2.47596 -$31,615.20 $49,519.26 $17,904.07
13 -$12,500.00 $20,000.00 $7,500.00 2.25223 2.21068 -$28,152.87 $44,213.63 $16,060.76
14 -$12,500.00 $20,000.00 $7,500.00 2.00558 1.97382 -$25,069.72 $39,476.45 $14,406.74
15 -$12,500.00 $20,000.00 $7,500.00 1.78594 1.76234 -$22,324.21 $35,246.83 $12,922.62
16 -$12,500.00 $20,000.00 $7,500.00 1.59035 1.57352 -$19,879.39 $31,470.39 $11,591.00
17 -$12,500.00 $20,000.00 $7,500.00 1.41618 1.40493 -$17,702.30 $28,098.56 $10,396.26
18 -$12,500.00 $20,000.00 $7,500.00 1.26109 1.25440 -$15,763.64 $25,088.00 $9,324.36
19 -$12,500.00 $20,000.00 $7,500.00 1.12298 1.12000 -$14,037.29 $22,400.00 $8,362.71
20 -$12,500.00 $20,000.00 $7,500.00 1.00000 1.00000 -$12,500.00 $20,000.00 $7,500.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i’ INTEREST RATE (THE ERR)
ERR =12.30%
SINCE THE ERR OF THE INCREMENT IS GREATER THAN MARR(12%) THE INCREMENTAL INVESTMENT IS ACCEPTED
A BECOMES THE NEW CURRENT BEST
B BECOMES THE CHALLENGER
THUS, THE CASH FLOWS FOR B – A ARE NEEDED
PROJECT B-A
11.28% 12.00%
EOY – CF + CF NET CF (F|P i’,n-t) (F|P MARR,n-t) FW OF –CF FW OF +CF FW OF CF
0 -$25,000.00 -$25,000.00 8.47477 9.64629 -$211,869.35 $0.00 -$211,869.35
1 -$5,500.00 $8,000.00 $2,500.00 7.61591 8.61276 -$41,887.50 $68,902.09 $27,014.60
2 -$5,500.00 $8,000.00 $2,500.00 6.84408 7.68997 -$37,642.46 $61,519.73 $23,877.27
3 -$5,500.00 $8,000.00 $2,500.00 6.15048 6.86604 -$33,827.63 $54,928.33 $21,100.70
4 -$5,500.00 $8,000.00 $2,500.00 5.52717 6.13039 -$30,399.41 $49,043.15 $18,643.74
5 -$5,500.00 $8,000.00 $2,500.00 4.96702 5.47357 -$27,318.62 $43,788.53 $16,469.90
6 -$5,500.00 $8,000.00 $2,500.00 4.46365 4.88711 -$24,550.05 $39,096.90 $14,546.85
7 -$5,500.00 $8,000.00 $2,500.00 4.01128 4.36349 -$22,062.06 $34,907.94 $12,845.89
8 -$5,500.00 $8,000.00 $2,500.00 3.60476 3.89598 -$19,826.20 $31,167.81 $11,341.60
9 -$5,500.00 $8,000.00 $2,500.00 3.23944 3.47855 -$17,816.94 $27,828.40 $10,011.46
10 -$5,500.00 $8,000.00 $2,500.00 2.91115 3.10585 -$16,011.31 $24,846.79 $8,835.48
11 -$5,500.00 $8,000.00 $2,500.00 2.61612 2.77308 -$14,388.66 $22,184.63 $7,795.97
12 -$5,500.00 $8,000.00 $2,500.00 2.35099 2.47596 -$12,930.46 $19,807.71 $6,877.25
13 -$5,500.00 $8,000.00 $2,500.00 2.11273 2.21068 -$11,620.04 $17,685.45 $6,065.41
14 -$5,500.00 $8,000.00 $2,500.00 1.89862 1.97382 -$10,442.42 $15,790.58 $5,348.16
15 -$5,500.00 $8,000.00 $2,500.00 1.70621 1.76234 -$9,384.15 $14,098.73 $4,714.59
16 -$5,500.00 $8,000.00 $2,500.00 1.53329 1.57352 -$8,433.12 $12,588.15 $4,155.03
17 -$5,500.00 $8,000.00 $2,500.00 1.37790 1.40493 -$7,578.48 $11,239.42 $3,660.95
18 -$5,500.00 $8,000.00 $2,500.00 1.23826 1.25440 -$6,810.45 $10,035.20 $3,224.75
19 -$5,500.00 $8,000.00 $2,500.00 1.11277 1.12000 -$6,120.25 $8,960.00 $2,839.75
20 -$5,500.00 $8,000.00 $2,500.00 1.00000 1.00000 -$5,500.00 $8,000.00 $2,500.00
$0.00 <- FW
TO IMPLEMENT GOAL SEEK:
(1) SET CELL: FW SET THE FUTURE WORTH
(2) TO VALUE: ZERO TO A VALUE OF ZERO
(3) BY CHANGING CELL: (F|P i’,n-t) BY CHANGING THE i’ INTEREST RATE (THE ERR)
ERR = 11.28%
SINCE THE ERR OF THE INCREMENT IS LESS THAN MARR (12%) THE INCREMENTAL INVESTMENT IS REJECTED
A REMAINS THE CURRENT BEST
SINCE THERE ARE NO MORE CHALLENGERS, A IS RECOMMENDED.
PROBLEM 6.73
MARR = 12%
n = 5
EOY A B B-A
0 -$30,000.00 -$42,000.00 -$12,000.00
1 $9,300.00 $12,625.00 $3,325.00
2 $9,300.00 $12,625.00 $3,325.00
3 $9,300.00 $12,625.00 $3,325.00
4 $9,300.00 $12,625.00 $3,325.00
5 $9,300.00 $12,625.00 $3,325.00
STEP 1:
STEP 2: CALCULATE THE ERR FOR A USING EXCEL’S MIRR FUNCTION.
ERR(A) = 14.516% =MIRR(C7:C12,,C3)
STEP 3:
IS ERR(A) > MARR? IF SO, CALCULATE ERR(B-A); IF NOT, CALCULATE ERR(B-A)
ERR(B-A) = 11.974% =MIRR(E7:E12,,C3)
STEP 4: DRAW CONCLUSION.
SINCE ERR(B-A) < MARR, RECOMMEND INVESTMENT ALTERNATIVE A.
STEP 5:
SINCE A IS RECOMMENDED, THIS STEP IS NOT NEEDED. ERR(A) = 14.516%.
PROBLEM 6.74
MARR = 15%
n = 10
INVESTMENT ALTERNATIVE A B C A-C B-A
INITIAL INVESTMENT $600,000 $800,000 $470,000 $130,000 $200,000
SALVAGE VALUE $70,000 $130,000 $65,000 $5,000 $60,000
ANNUAL RECEIPTS $400,000 $600,000 $260,000 $140,000 $200,000
ANNUAL DISBURSEMENTS $130,000 $270,000 $70,000 $60,000 $140,000
NET ANNUAL REVENUE $270,000 $330,000 $190,000 $80,000 $60,000
ALT A B C FEASIBLE? WHY NOT?
1 0 0 0 YES
2 0 0 1 NO C CONTINGENT ON B
3 0 1 0 YES
4 0 1 1 NO EXCEEDS BUDGET LIMIT
5 1 0 0 YES
6 1 0 1 NO EXCEEDS BUDGET LIMIT
7 1 1 0 NO A&B MUTUALLY EXCLUSIVE
8 1 1 1 NO A&B MUTUALLY EXCLUSIVE
EOY NCF(1) NCF(3) NCF(5) NCF(5-1) NCF(3-5)
0$0 -$800,000 -$600,000 -$600,000 ########
1$0 $330,000 $270,000 $270,000 $60,000
2$0 $330,000 $270,000 $270,000 $60,000
3$0 $330,000 $270,000 $270,000 $60,000
4$0 $330,000 $270,000 $270,000 $60,000
5$0 $330,000 $270,000 $270,000 $60,000
6$0 $330,000 $270,000 $270,000 $60,000
7$0 $330,000 $270,000 $270,000 $60,000
8$0 $330,000 $270,000 $270,000 $60,000
9$0 $330,000 $270,000 $270,000 $60,000
10 $0 $460,000 $340,000 $340,000 $120,000
STEP 1:
STEP 2: SINCE ERR(1) DOES NOT EXIST, WE CALCULATE ERR(5-1) USING EXCEL’S MIRR FUNCTION.
MIRR(5-1) = 24.919% =MIRR(F24:F34,,C4)
STEP 3: IS ERR(5-1) > MARR? IF SO, CALCULATE ERR(3-5); IF NOT, CALCULATE ERR(3)
ERR(3-5) = 20.381% =MIRR(G24:G34,,C4)
STEP 4: DRAW CONCLUSION.
SINCE ERR(3-5) > MARR, RECOMMEND INVESTMENT ALTERNATIVE 3, WHICH IS PROPOSAL B.
STEP 5:
ERR(B) = 23.918% =MIRR(D24:D34,,C4)
CONSIDER THE INVESTMENT ALTERNATIVES IN INCREASING ORDER OF THE INITIAL
INVESTMENT: CONSIDER 1, THEN EITHER 5 OR 5-1, DEPENDING ON THE OUTCOME FOR 1.
THEN EITHER 3-5 OR 3, DEPENDING ON THE OUTCOME OF 5-1 OR 5.
ERR(5-1) = 24.919% > MARR = 15%. CALCULATE ERR(3-5) USING EXCEL’S MIRR
FUNCTION.
CALCULATE ERR FOR THE RECOMMENDED INVESTMENT USING EXCEL’S MIRR FUNCTION.
PROBLEM 6.75
MARR = 12%
n = 10
EOY NCF(A) NCF(B) NCF(A-B)
NCF(A-B)
0 -$40,000 -$30,000 -$10,000 -$10,000
1 $8,000 $9,000 -$1,000 -$1,000
2 $8,000 $8,500 -$500 -$500
3 $8,000 $8,000 $0 $0
4 $8,000 $7,500 $500 $0
5 $8,000 $7,000 $1,000 $0
6 $8,000 $6,500 $1,500 $0
7 $8,000 $6,000 $2,000 $0
8 $8,000 $5,500 $2,500 $0
9 $8,000 $5,000 $3,000 $0
10 $8,000 $4,500 $3,500 $17,915 =FV(C4,7,,-NPV(C4,E12:E18))
STEP 1:
STEP 2: CALCULATE ERR(B) USING EXCEL’S MIRR FUNCTION.
ERR(B) = 15.476% =MIRR(D8:D18,,C4)
STEP 3: IS ERR(B) > MARR? IF SO, CALCULATE ERR(A-B); IF NOT, CALCULATE ERR(A)
ERR(A-B) = 4.612% =IRR(F8:F18)
STEP 4: DRAW CONCLUSION.
SINCE ERR(A-B) < MARR, RECOMMEND INVESTMENT ALTERNATIVE B.
STEP 5:
ERR(B) = 15.476% =MIRR(D8:D18,,C4)
CONSIDER THE INVESTMENT ALTERNATIVES IN INCREASING ORDER OF THE INITIAL
INVESTMENT: CONSIDER B, THEN EITHER A-B OR A, DEPENDING ON THE OUTCOME FOR B.
ERR(B) = 15.476% > MARR = 12%. CALCULATE ERR(A-B). SINCE MULTIPLE
NEGATIVE-VALUED CASH FLOWS EXIST, CANNOT USE EXCEL’S MIRR
FUNCTION. CONSTRUCT A NEW CASH FLOW PROFILE HAVING ZEROES AND
NEGATIVE-VALUED CASH FLOWS IN ALL BUT THE LAST YEAR. IN THE LAST
YEAR, USE THE FW OF POSITIVE-VALUED CASH FLOWS. THEN, USE EXCEL’S IRR
FUNCTION TO SOLVE FOR THE ERR.
CALCULATE ERR FOR THE RECOMMENDED INVESTMENT USING EXCEL’S MIRR FUNCTION.
PROBLEM 6.76
NCF(A) NCF(B) NCF(C) NCF(D) MARR = 10%
Initial investment $400,000 $400,000 $600,000 $300,000 LIFE = 10
Planning horizon 10 years 10 years 10 years 10 years
Annual receipts $205,000 $215,000 $260,000 $230,000
Annual disbursements $110,000 $125,000 $120,000 $150,000
Net annual revenues $95,000 $90,000 $140,000 $80,000
Salvage value $50,000 $50,000 $100,000 $50,000
ALT A B C D FEASIBLE? WHY NOT?
1 0 0 0 0 YES
2 0 0 0 1 NO D CONTINGENT ON B
3 0 0 1 0 YES
4 0 0 1 1 NO D CONTINGENT ON B
5 0 1 0 0 YES
6 0 1 0 1 YES
7 0 1 1 0 NO EXCEEDS BUDGET LIMIT
8 0 1 1 1 NO EXCEEDS BUDGET LIMIT
9 1 0 0 0 YES
10 1 0 0 1 NO D CONTINGENT ON B
11 1 0 1 0 NO A&C MUTUALLY EXCLUSIVE
12 1 0 1 1 NO A&C MUTUALLY EXCLUSIVE
13 1 1 0 0 NO EXCEEDS BUDGET LIMIT
14 1 1 0 1 NO EXCEEDS BUDGET LIMIT
15 1 1 1 0 NO EXCEEDS BUDGET LIMIT
16 1 1 1 1 NO EXCEEDS BUDGET LIMIT
EOY NCF(1) NCF(3) NCF(5) NCR(6) NCF(9) NCF(9-1) NCF(3-9) NCF(6-3)
0$0 -$600,000 -$400,000 -$700,000 -$400,000 -$400,000 -$200,000 -$100,000
1$0 $140,000 $90,000 $170,000 $95,000 $95,000 $45,000 $30,000
6$0 $140,000 $90,000 $170,000 $95,000 $95,000 $45,000 $30,000
7$0 $140,000 $90,000 $170,000 $95,000 $95,000 $45,000 $30,000
8$0 $140,000 $90,000 $170,000 $95,000 $95,000 $45,000 $30,000
9$0 $140,000 $90,000 $170,000 $95,000 $95,000 $45,000 $30,000
STEP 1:
STEP 2: CALCULATE ERR(9-1)
ERR(9-1) =
ERR(3-9) =
14.390% =MIRR(I29:I39,,I3)
ERR(6-3) =
ERR(3-9) = 14.390% > MARR = 10%. CALCULATE ERR(6-3).
STEP 5: DRAW CONCLUSION.
STEP 6:
ERR(6) = 14.908% =MIRR(F29:D39,,I3)
FURTHER CONSIDERATION.
CONSIDER THE INVESTMENT ALTERNATIVES IN INCREASING ORDER OF THE INITIAL INVESTMENT: 1, 9,
3, 6. SINCE ALT 1 DOES NOT HAVE AN IRR, CONSIDER, FIRST, 9-1.
ERR(9-1) = 14.609% > MARR = 10%. CALCULATE ERR(3-9).
SINCE ERR(6-3) > MARR, RECOMMEND INVESTMENT ALTERNATIVE 6, WHICH IS THE COMBINATION OF
PROPOSALS B AND D.
CALCULATE ERR FOR THE RECOMMENDED INVESTMENT.