Description
The spreadsheet about model will help you to calculate NPV and IRR with ease.
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
A B C NPV RULE FOR CAPITAL BUDGETING
D
E
F
G
H
Choose a project if it costs less than the PV of its cash flows. More generally: take a project if its Net Present Value is positive.
EXAMPLE
Interest rate Year Cash flow PV factor PV of cash flow Cumulative PV Net Present Value
10% 0 (600) 100% (600) (600) 123 1 200 91% 182 (418) 2 200 83% 165 (253) 3 500 75% 376 123
Investors would have to invest 123 more (a total of 723) to get the cash flows of 200, 200, and 500 at an interest rate of 10%. Therefore the project has a value of 123 for investors. The interest rate is called the cost of capital, because it is the opportunity cost of funds - the rate investors can earn on alternative investments.
Page 1
NPV AND IRR RULES
A 1 2 3 4 5 6 7 8 9 10
IRR RULE
B
C
D
E
F
G
For a standard project, IRR Rule:
NPV > 0
if and only if if and only if
IRR > Cost of Capital IRR > Cost of Capital
Choose a project
Standard means - cash outflows occur in early years and cash inflows in later years. - the alternative to the project is the status quo.
Page 2
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
A B C D E F G NONSTANDARD PROJECTS MAY HAVE MORE THAN ONE INTERNAL RATE OF RETURN
Cost of capital
12%
Year Net cash flow PV factor PV of net cash flow Cumulative PV Net present value IRR (Internal Rate of Return)
0 (400,000) 100% (400,000) (400,000) 1,148 10%
1
2
960,000 (572,000) 89% 80% 857,143 (455,995) 457,143 1,148
For this project, varying the initial guess in the IRR function can cause the IRR to change. This is a good project (positive NPV), but you can't tell it from the IRR function. The following chart shows that there are two break-even costs of capital or IRR's. The NPV is positive at the actual cost of capital (12%), so it is a good project.
Page 3
NPV AND IRR RULES
A B 1 Year 0 2 Net cash flow (400,000) 3 4 Discount Rate NPV 5 6 2% (8,612) 7 4% (5,769) 8 6% (3,418) 9 8% (1,509) 10 10% 11 12% 1,148 12 14% 1,970 13 16% 2,497 14 18% 2,758 15 20% 2,778 16 22% 2,580 17 24% 2,185 18 26% 1,612 19 28% 879 20 30% 21 32% (1,010) 22 34% (2,139) 23 36% (3,374) 24 38% (4,705) 25 40% (6,122)
D 1 2 960,000 (572,000)
C
E
F
G
H
4,000 2,000 Net Present Value 0% (2,000) (4,000) (6,000) (8,000) (10,000) Discount Rate 20% 40% 60%
Page 4
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
A B C D AN EXAMPLE OF MUTUALLY EXCLUSIVE PROJECTS Cost of capital 10% Year Project A Cash flow PV factor PV of cash flow NPV IRR Cash flow PV factor PV of cash flow NPV IRR 0 (10,000) 100% (10,000) 8,182 100% (20,000) 100% (20,000) 11,818 75%
E
1 20,000 91% 18,182
Project B
35,000 91% 31,818
Project B is best, even though its IRR is lower.
Page 5
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
A B C D E PROJECTS CAN BE VALUED ON AN INCREMENTAL BASIS
F
G
H
Cost of capital
10% Year 0 (10,000) 100% (10,000) 8,182 (10,000) 100% (10,000) 3,636 1 20,000 91% 18,182
Project A
Cash flow PV factor PV of cash flow NPV Cash flow PV factor PV of cash flow NPV
Project B-A
15,000 91% 13,636
Project B has a positive NPV relative to A (on an incremental basis) so should be taken.
Page 6
doc_266219131.xls
The spreadsheet about model will help you to calculate NPV and IRR with ease.
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
A B C NPV RULE FOR CAPITAL BUDGETING
D
E
F
G
H
Choose a project if it costs less than the PV of its cash flows. More generally: take a project if its Net Present Value is positive.
EXAMPLE
Interest rate Year Cash flow PV factor PV of cash flow Cumulative PV Net Present Value
10% 0 (600) 100% (600) (600) 123 1 200 91% 182 (418) 2 200 83% 165 (253) 3 500 75% 376 123
Investors would have to invest 123 more (a total of 723) to get the cash flows of 200, 200, and 500 at an interest rate of 10%. Therefore the project has a value of 123 for investors. The interest rate is called the cost of capital, because it is the opportunity cost of funds - the rate investors can earn on alternative investments.
Page 1
NPV AND IRR RULES
A 1 2 3 4 5 6 7 8 9 10
IRR RULE
B
C
D
E
F
G
For a standard project, IRR Rule:
NPV > 0
if and only if if and only if
IRR > Cost of Capital IRR > Cost of Capital
Choose a project
Standard means - cash outflows occur in early years and cash inflows in later years. - the alternative to the project is the status quo.
Page 2
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
A B C D E F G NONSTANDARD PROJECTS MAY HAVE MORE THAN ONE INTERNAL RATE OF RETURN
Cost of capital
12%
Year Net cash flow PV factor PV of net cash flow Cumulative PV Net present value IRR (Internal Rate of Return)
0 (400,000) 100% (400,000) (400,000) 1,148 10%
1
2
960,000 (572,000) 89% 80% 857,143 (455,995) 457,143 1,148
For this project, varying the initial guess in the IRR function can cause the IRR to change. This is a good project (positive NPV), but you can't tell it from the IRR function. The following chart shows that there are two break-even costs of capital or IRR's. The NPV is positive at the actual cost of capital (12%), so it is a good project.
Page 3
NPV AND IRR RULES
A B 1 Year 0 2 Net cash flow (400,000) 3 4 Discount Rate NPV 5 6 2% (8,612) 7 4% (5,769) 8 6% (3,418) 9 8% (1,509) 10 10% 11 12% 1,148 12 14% 1,970 13 16% 2,497 14 18% 2,758 15 20% 2,778 16 22% 2,580 17 24% 2,185 18 26% 1,612 19 28% 879 20 30% 21 32% (1,010) 22 34% (2,139) 23 36% (3,374) 24 38% (4,705) 25 40% (6,122)
D 1 2 960,000 (572,000)
C
E
F
G
H
4,000 2,000 Net Present Value 0% (2,000) (4,000) (6,000) (8,000) (10,000) Discount Rate 20% 40% 60%
Page 4
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
A B C D AN EXAMPLE OF MUTUALLY EXCLUSIVE PROJECTS Cost of capital 10% Year Project A Cash flow PV factor PV of cash flow NPV IRR Cash flow PV factor PV of cash flow NPV IRR 0 (10,000) 100% (10,000) 8,182 100% (20,000) 100% (20,000) 11,818 75%
E
1 20,000 91% 18,182
Project B
35,000 91% 31,818
Project B is best, even though its IRR is lower.
Page 5
NPV AND IRR RULES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
A B C D E PROJECTS CAN BE VALUED ON AN INCREMENTAL BASIS
F
G
H
Cost of capital
10% Year 0 (10,000) 100% (10,000) 8,182 (10,000) 100% (10,000) 3,636 1 20,000 91% 18,182
Project A
Cash flow PV factor PV of cash flow NPV Cash flow PV factor PV of cash flow NPV
Project B-A
15,000 91% 13,636
Project B has a positive NPV relative to A (on an incremental basis) so should be taken.
Page 6
doc_266219131.xls