Examples from Notes: Capital Budgeting I

Net Present Value

NPV Function: = NPV(Rate, Value1, Value2,...)

Calculates the net present value of an investment by using a discount rate and a series of future pa

Rate Required input. The rate of discount over the length of one period
Value1, Value2, ... Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments a

Items to take note:

(a) Value1, Value2, ... must be equally spaced in time and occur at the end of each period.
(b) NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income va
(c) NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows t
at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the in

Formula:

If n is the number of cash flows in the list of values, the formula for NPV is:

Example:
Description Year Data
Annual discount rate (r) 8.00%
Initial cost of investment CF0 $ (40,000.00)
Return from first year CF1 $ 8,000.00
Return from second year CF2 $ 9,200.00
Return from third year CF3 $ 10,000.00
Return from fourth year CF4 $ 12,000.00
Return from fifth year CF5 $ 14,500.00

Net Present Value = PV of Future Cash Flows - Initial Investment

= NPV(C21, C23:C27)+C22 (we added initial investment here as the initial investment is a n
= $ 1,922.06
nt rate and a series of future payments (negative values) and income (positive values).

nts representing the payments and income.

ter your payment and income values in the correct sequence.

NPV is that PV allows cash flows to begin either at the end or
ust be constant throughout the investment.

re as the initial investment is a negative number already)

Examples from Notes: Capital Budgeting I
Internal Rate of Return

IRR Function: = IRR(values, [guess])

Returns the internal rate of return for a series of cash flows represented by the numbers in values. T
not have to be even, as they would be for an annuity. However, the cash flows must occur at regula
monthly or annually. The internal rate of return is the interest rate received for an investment consis
(negative values) and income (positive values) that occur at regular periods.

values Required input. An array or a reference to cells that contain numbers for which you want to calcula
[guess] Optional. A number that you guess is close to the result of IRR.

Items to take note:

(a) Values must contain at least one positive value and one negative value to calculate the internal rate of return.
(b) IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the
(c) If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
(d) Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result
If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.
(e) In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 perce
(f) If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for gue
(g) IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresp

Example:
Description Year Data
Initial cost of investment CF0 $ (70,000.00)
Return from first year CF1 $ 12,000.00
Return from second year CF2 $ 15,000.00
Return from third year CF3 $ 18,000.00
Return from fourth year CF4 $ 21,000.00
Return from fifth year CF5 $ 26,000.00

nternal Rate of Return = IRR(C22:C27)

= 8.66%

IRR after 4 years = IRR(C22:C26)

= -2.12%

IRR after 2 years = IRR(C22:C24,-10%)

= -44.35%
nted by the numbers in values. These cash flows do
cash flows must occur at regular intervals, such as
eceived for an investment consisting of payments
periods.

ers for which you want to calculate the internal rate of return.

ternal rate of return.

yment and income values in the sequence you want.
are ignored.
h the calculation until the result is accurate within 0.00001 percent.

it is assumed to be 0.1 (10 percent).

ain with a different value for guess.
by IRR is the interest rate corresponding to a 0 (zero) net present value.
Examples from Notes: Capital Budgeting I
Page 13 [NPV Profiles]
r 12.00%
Year CFs NPV $12,627.41
0 $ (165,000.00)
1 $ 63,120.00
2 $ 70,800.00
3 $ 91,080.00

Page 13 [NPV Profiles]

CF0 $ (165,000.00)
CF1 $ 63,120.00 NI1 $ 13,620.00
CF2 $ 70,800.00 NI2 $ 3,300.00
CF3 $ 91,080.00 NI3 $ 29,100.00
Avg BV $ 72,000.00

Required rate 10.00% 15.00% 20.00% 16.13%

NPV $ 19,323.97 $ 3,308.51 $ (10,525.00) $ (0.00)

Page 18 [Payback Period]

Year 0 $ 165,000.00
Amount to Recover
Year 1 $ 101,880.00
Year 2 $ 31,080.00 > 0 ==> Project does not pay back within 2 years
Year 3 $ (60,000.00) Project pays back in Year 3

Page 19 [Cutoff Period]

Year A B C D E
0 $ (100.00) $ (200.00) $ (200.00) $ (200.00) $ (50.00)
1 $ 30.00 $ 40.00 $ 40.00 $ 100.00 $ 100.00
2 $ 40.00 $ 20.00 $ 20.00 $ 100.00 $ (50,000,000.00)
3 $ 50.00 $ 10.00 $ 10.00 $ (200.00)
4 $ 60.00 $ 130.00 $ 200.00
Cutoff Period 2.6 years Never 4 years 2 and 4 years 6 months
Rapid payback but NOT
a good investment
Page 20 [Cutoff Period]
Year A B
0 $ (250.00) $ (250.00)
1 $ 100.00 $ 100.00
 2 $ 100.00 $ 200.00
3 $ 100.00 $ -
4 $ 100.00 $ -
Cutoff Period 2.5 years 1.75 years
BUT r 15.00%
NPV $ 35.50 $ (11.81)

Page 26 [Discounted Payback Period]

r 12.00%
Year CFs PV Recovery
0 $ 165,000.00
1 $ 63,120.00 $ 108,642.86
2 $ 70,800.00 $ 52,201.53 > 0 ==> Project does not pay back within 2 years
3 $ 91,080.00 $ (12,627.41) Project pays back in Year 3

Page 31 [Average Accounting Return]

R 25.00% <-- Required Avg. Accounting Return
Year NIs
0 $ 165,000.00
1 $ 13,620.00
2 $ 3,300.00
3 $ 29,100.00
Avg BV $ 72,000.00

Avg. Net Inc. $ 15,340.00

AAR 21.31% < 25% ==> Reject Project
= Average Net Income / Average Book Value

Page 40 [IRR for Non-conventional CFs]

r 15.00%
Year CFs
0 $ (90,000.00)
1 $ 132,000.00
2 $ 100,000.00
3 $ (150,000.00)

NPV $1,769.54 > 0 ==> Accept

IRR 10.11% < 15% ==> Reject

= IRR(B80:B83)

IRR 42.66% > 15% ==> Accept

= IRR(B80:B83,45%)

Page 43 [IRR for Mutually Exclusive Projects (Scale Problem)]

R 15.00%
Period Project A Project B Project C
0 $ (100,000.00) $ (1,000,000.00) $ (100.00)
1 $ 140,000.00 $ 1,250,000.00 $ 150.00
IRR 40.00% 25.00% 50.00%
NPV $ 21,739.13 $ 86,956.52 $ 30.43
Highest NPV Highest IRR

Page 44 [IRR for Mutually Exclusive Projects (Timing Problem)]

R 17.00%
Period Project A Project B Project (A-B)
0 $ (1,000,000.00) $ (1,000,000.00) $ -
1 $ 800,000.00 $ 100,000.00 $ 700,000.00
2 $ 300,000.00 $ 400,000.00 $ (100,000.00)
3 $ 200,000.00 $ 500,000.00 $ (300,000.00)
4 $ 100,000.00 $ 800,000.00 $ (700,000.00)
IRR 22.99% 21.46% 19.77%
NPV $ 81,153.87 $ 116,780.82
Highest IRR Highest NPV

Page 50 [Profitability Index: Mutually Exclusive]

Year Project A Project B
Cost $ 5.00 $ 100.00
PV $ 10.00 $ 150.00
PI 2.00 1.50 = PV of Future CFs / Initial Investment

If A & B are mutually exclusive, then Project B is still better even though it has a lower PI
 Rate NPV
0.00% $ 60,000.00 NPV Profile
5.00% $ 38,010.30
$80,000.00
10.00% $ 19,323.97
15.00% $ 3,308.51 $60,000.00
20.00% $ (10,525.00)
16.13%
25.00% $ (22,559.04) $40,000.00
30.00% $ (33,096.13)
35.00% $ (42,377.96) $20,000.00
NPV

$-
0.00% 5.00% 10.00% 15.00% 20.00% 25.00% 30.00% 35.00%
$(20,000.00)

$(40,000.00)

$(60,000.00)

Required Rate

back but NOT

Rate NPV
0.00% $ (8,000.00) NPV Profile
5.00% $ (3,158.41)
$4,000.00
10.00% $ (52.59) 10.11% 42.66%
15.00% $ 1,769.54 $2,000.00
20.00% $ 2,638.89
25.00% $ 2,800.00 $-
30.00% $ 2,435.14 0.00 5.00 10.00 15.00 20.00 25.00 30.00 35.00 40.00 45.00 50.00 55.00
% % % % % % % % % % % %
35.00% $ 1,681.15 $(2,000.00)
NPV

40.00% $ 641.40
$(4,000.00)
45.00% $ (605.60)
50.00% $ (2,000.00)
$(6,000.00)
55.00% $ (3,496.02)
$(8,000.00)

$(10,000.00)
 N
$(4,000.00)

$(6,000.00)

$(8,000.00)

$(10,000.00)

Required Rate

Rate NPV(A) NPV(B)

0.00% $ 400,000.00 $ 800,000.00 Crossover Rate
5.00% $ 289,051.37 $ 548,130.67 $1,000,000.00
10.00% $ 193,770.92 $ 343,555.77
15.00% $ 111,173.85 $ 175,574.70 $800,000.00
20.00% $ 38,966.05 $ 36,265.43
25.00% $ (24,640.00) $ (80,320.00) $600,000.00

30.00% $ (81,054.58) $ (178,705.23)

$400,000.00
35.00% $ (131,403.11) $ (262,371.55) 19.77% NPV(A)
40.00% $ (176,593.09) $ (334,027.49) NPV(B)
$200,000.00
45.00% $ (217,363.40) $ (395,801.67)
50.00% $ (254,320.99) $ (449,382.72) $-
55.00% $ (287,968.55) $ (496,121.91) % % % % % % % % % % % %
00 00 00 00 00 00 00 00 00 00 00 00
19.77% $ 42,031.52 $ 42,031.52 0. 5. 10. 15. 20. 25. 30. 35. 40. 45. 50. 55.
$(200,000.00)
Diff $ (0.00)
$(400,000.00)

$(600,000.00)
00% 35.00%
0 50.00 55.00
% %
 NPV(A)
NPV(B)

% %
00 .00
55
Sample Quiz Questions from Notes: Capital Budgeting I
(5th, Q8.3) [Payback]
Payback Cutoff 3 years
Year CF (A) Amt to Recover (A) CF (B) Amt to Recover (B)
0 $ (45,000.00) $ (90,000.00)
1 $ 17,000.00 $ 28,000.00 $ 20,000.00 $ 70,000.00
2 $ 20,000.00 $ 8,000.00 $ 25,000.00 $ 45,000.00
3 $ 18,000.00 $ (10,000.00) $ 30,000.00 $ 15,000.00
4 $ 9,000.00 $ (19,000.00) $ 250,000.00 $ (235,000.00)
Payback 2.44 3.06

In year 3, Project A is fully recovered within the required payback cutoff period, it should be accepted.
However, Project B is not fully recovered within the required payback cutoff period and should be rejected.

(5th, Q8.4) [AAR] Calculation of Average Book Value:

Year Net Income Year Depreciation
0 $ (14,000,000.00) 0 $ -
1 $ 1,315,000.00 1 $ 3,500,000.00
2 $ 1,846,000.00 2 $ 3,500,000.00
3 $ 1,523,000.00 3 $ 3,500,000.00
4 $ 1,308,000.00 4 $ 3,500,000.00
Average
Average NI $ 1,498,000.00 For straight-line depreciation to zero, the Average Boo
Avg BV $ 7,000,000.00
AAR 21.40%

(5th, Q8.5-6) [IRR and NPV]

Year CFs
0 $ (100,000.00)
1 $ 45,000.00
2 $ 52,000.00
3 $ 43,000.00

(a) Find IRR

Required Return 18.00%
IRR 19.03% > 18% ==> Accept

(b) Find NPV

Required Return 11.00%
NPV $14,186.14 > 0 ==> Accept

Required Return 23.00%

NPV ($5,936.05) < 0 ==> Reject

(5th, Q8.7) [IRR and NPV]

 Year CFs
0 $ (5,200.00)
1 $ 1,200.00
2 $ 1,200.00
3 $ 1,200.00
4 $ 1,200.00
5 $ 1,200.00
6 $ 1,200.00
7 $ 1,200.00
8 $ 1,200.00
9 $ 1,200.00

Required Return 8.00%

NPV $2,296.27 > 0 ==> Accept

Required Return 24.00%

NPV ($921.40) < 0 ==> Reject

IRR 17.79% Indifferent (as NPV = 0)

(5th, Q8.10) [IRR and NPV: Mutually Exclusive Projects]

Period CF (A) CF (B) CF (A-B)
0 $ (30,000.00) $ (30,000.00) $ -
1 $ 16,000.00 $ 6,000.00 $ 10,000.00
2 $ 13,000.00 $ 11,000.00 $ 2,000.00
3 $ 8,000.00 $ 12,000.00 $ (4,000.00)
4 $ 5,000.00 $ 19,000.00 $ (14,000.00)
IRR 18.72% 18.13% 16.82%
Required Return 11.00% 11.00%
NPV $ 4,108.69 $ 5,623.44

(a) Based on IRR Rule, select Project A as it gives a higher IRR

This is not necessarily correct given that the IRR criterion has a ranking problem for mutually exclusive projects.

(b) As Project B has a higher NPV, it should be selected over Project A

(c) As the crossover rate is 16.82%, we would be indifferent between Project A and B if the discount rate is 16.82%
As shown by the crossover rate chart, if the discount rate is below 16.82%, select Project B.
Also, if the discount rate is between 16.82% and 18.72%, then select Project A.

(5th, Q8.14) [Profitability Index]

Period CF (A) CF (B)
0 $ (35,000.00) $ (5,500.00)
1 $ 12,000.00 $ 2,800.00
2 $ 16,000.00 $ 2,600.00
3 $ 19,000.00 $ 2,400.00
 Required Return 11.00% 11.00%
PV $ 37,689.41 $ 6,387.60
PI 1.077 1.161 = PV of Future CFs / Initial Investment
NPV $ 2,689.41 $ 887.60

(a) As the profitability index is higher for Project B, it should be selected over Project A.

(b) Based on the NPV decision rule, Project A should be selected as it has a higher NPV.

(c) When the magnitude of the cash flows for the two projects are different in scale, using the
profitability index for comparision can be ambinguous.
As given, Project A's cash flows are roughly 3x larger than those for Project B and it produces
a larger NPV, but under the profitability index criteria, Project B is still selected.

(5th, Q8.15) [Comparing Investment Criterias]

Required Return 15.00%
Period CF (A) Amt to Recover (A) CF (B) Amt to Recover (B)
0 $ (252,000.00) $ (24,000.00)
1 $ 18,000.00 $ 234,000.00 $ 14,400.00 $ 9,600.00
2 $ 36,000.00 $ 198,000.00 $ 12,600.00 $ (3,000.00)
3 $ 38,000.00 $ 160,000.00 $ 11,400.00 $ (14,400.00)
4 $ 510,000.00 $ (350,000.00) $ 9,800.00 $ (24,200.00)
Payback 3.31 1.76
NPV $ 107,453.12 $ 11,148.02
IRR 26.87% 38.27%
PV $ 359,453.12 $ 35,148.02
PI 1.426 1.465

(a) Payback Criterion: Choose Project B as it has a shorter time required for payback

(b) NPV Criterion: Choose Project A as it has a higher NPV

(c) IRR Criterion: Choose Project B as it gives a higher IRR

(d) Profitability Index Criterion: Choose Project B as it gives a higher PI value

(e) In this case, Project A should be selected based on the NPV criterion.
Although the other criterias all suggest the selection of Project B, the final decision should be based
on the NPV since it does not have the ranking problem associated with the other capital budgeting
techniques.

(5th, Q8.18) [Crossover Rate]

Period CF (A) CF (B) CF (A-B)
0 $ (120,000.00) $ (120,000.00) $ -
1 $ 50,000.00 $ 43,000.00 $ 7,000.00
2 $ 48,100.00 $ 46,000.00 $ 2,100.00
 3 $ 46,000.00 $ 49,000.00 $ (3,000.00)
4 $ 44,000.00 $ 52,000.00 $ (8,000.00)
IRR 21.30% 20.51% 7.89%

The crossover rate is 7.89% and at higher discount rates, Project A becomes more valuable.
 Book Value
$ 14,000,000.00
$ 10,500,000.00
$ 7,000,000.00
$ 3,500,000.00
$ -
$ 7,000,000.00
to zero, the Average Book Value is 1/2 of the initial investment.
 Rate NPV(A) NPV(B)
0.00% $ 12,000.00 $ 18,000.00
2.00% $ 10,339.27 $ 15,316.14 $20,000.00
4.00% $ 8,789.84 $ 12,848.59
6.00% $ 7,341.72 $ 10,575.55
$15,000.00
8.00% $ 5,986.03 $ 8,477.84
10.00% $ 4,714.84 $ 6,538.49
12.00% $ 3,521.07 $ 4,742.48 $10,000.00
14.00% $ 2,398.34 $ 3,076.48
16.00% $ 1,340.94 $ 1,528.63
$5,000.00
18.00% $ 343.71 $ 88.33
exclusive projects. 20.00% $ (597.99) $ (1,253.86)
22.00% $ (1,488.38) $ (2,506.42) $-
24.00% $ (2,331.27) $ (3,676.94) 00
%
00
%
00
%
0. 4. 8. 12
26.00% $ (3,130.12) $ (4,772.26)
$(5,000.00)
unt rate is 16.82% 28.00% $ (3,888.09) $ (5,798.54)
30.00% $ (4,608.03) $ (6,761.32)
32.00% $ (5,292.58) $ (7,665.63) $(10,000.00)
Rate NPV(A) NPV(B)
0.00% $ 68,100.00 $ 70,000.00
2.00% $ 59,247.85 $ 60,584.38 $80,000.00
4.00% $ 51,053.29 $ 51,886.38

$60,000.00

$40,000.00
 $80,000.00

6.00% $ 43,453.25 $ 43,836.09

$60,000.00
8.00% $ 36,391.89 $ 36,371.73
10.00% $ 29,819.68 $ 29,438.56
12.00% $ 23,692.57 $ 22,987.95 $40,000.00
14.00% $ 17,971.26 $ 16,976.58
16.00% $ 12,620.65 $ 11,365.82
$20,000.00
18.00% $ 7,609.28 $ 6,121.10
20.00% $ 2,908.95 $ 1,211.42
22.00% $ (1,505.74) $ (3,391.04) $-
24.00% $ (5,657.65) $ (7,711.32) 00
%
00
%
00
%
0. 4. 8. 12
26.00% $ (9,567.44) $ (11,772.03)
$(20,000.00)
28.00% $ (13,253.80) $ (15,593.55)
30.00% $ (16,733.66) $ (19,194.36)
32.00% $ (20,022.38) $ (22,591.19) $(40,000.00)
 Calculation using Financial Calculator: Uneven
(1) Clear the worksheet: Press [2nd] [CE | C] to
Crossover Rate (2) Go to the Cash Flow Worksheet: Press [CF]
$20,000.00 (3) CF0: Press [3] [0] [0] [0] [0] [+ | -] then pres
(4) CO1: Press [1] [6] [0] [0] [0] then press [ENT
(5) CO2: Press [1] [3] [0] [0] [0] then press [ENT
$15,000.00
(6) CO3: Press [8] [0] [0] [0] then press [ENTER
(7) CO4: Press [5] [0] [0] [0] then press [ENTER
$10,000.00
16.82 NPV(A) (8) Go to the Internal Rate of Return workshee
% NPV(B) (9) Compute IRR: Press [CPT] which should dis
$5,000.00

(10) Go to the Net Present Value worksheet: Pr

$- (11) Enter the interest rate (I): Press [1] [1] [EN
00
%
00
%
00
% 0% 00% 00% 00% 00% 00% (12) Compute NPV: Press [CPT] which should d
0. 4. 8. 2.0 . . . . .
1 16 20 24 28 32
$(5,000.00)

$(10,000.00)
 Crossover Rate
$80,000.00

7.89
$60,000.00 %

$40,000.00
 Crossover Rate
$80,000.00

7.89
$60,000.00 %

$40,000.00
NPV(A)
NPV(B)
$20,000.00

$-

00
%
00
%
00
% 0% 00% 00% 00% 00% 00%
0. 4. 8. .0 . . . . .
12 16 20 24 28 32
$(20,000.00)

$(40,000.00)
nancial Calculator: Uneven Cash Flows
eet: Press [2nd] [CE | C] to clear the worksheet.
ow Worksheet: Press [CF]
[0] [0] [0] [+ | -] then press [ENTER] [↓]
[0] [0] [0] then press [ENTER] [↓] [↓]
[0] [0] [0] then press [ENTER] [↓] [↓]
[0] [0] then press [ENTER] [↓] [↓]
[0] [0] then press [ENTER] [↓] [↓]

l Rate of Return worksheet: Press [IRR]

ess [CPT] which should display 18.72%

esent Value worksheet: Press [NPV]

st rate (I): Press [1] [1] [ENTER] [↓]
Press [CPT] which should display $4,108.69