Consider the following alternative projects in the table below. Each project wou
ID: 2329783 • Letter: C
Question
Consider the following alternative projects in the table below. Each project would last for five years.
1) Find the NPV for each project at a 10% discount rate.
2) Find the profitability index for each project at a 10% discount rate.
3) What discount rate would make the NPV=0 (i.e. breakeven in time) for each project?
Use excel to populate the table below. Show all cell references, show all formulas used, and show all calculations clearly.
Project A
Project B
Initial investment
$80,000
$60,000
Annual net cash inflows
20,000
16,000
$5,000 in maintenance
Year 2
Year 3
Salvage value
10,000
8,000
Project A
Project B
Initial investment
$80,000
$60,000
Annual net cash inflows
20,000
16,000
$5,000 in maintenance
Year 2
Year 3
Salvage value
10,000
8,000
Explanation / Answer
Project A
Year
Cash Outflow
Cash Inflow
Net Cash Flow
Discounting factor at 10%
Discounted Cash flow
1
$ 20,000.00
$ 20,000.00
0.909090909
$ 18,181.82
2
$ (5,000.00)
$ 20,000.00
$ 15,000.00
0.826446281
$ 12,396.69
3
$ 20,000.00
$ 20,000.00
0.751314801
$ 15,026.30
4
$ 20,000.00
$ 20,000.00
0.683013455
$ 13,660.27
5
$ 20,000.00
$ 20,000.00
0.620921323
$ 12,418.43
5
$ 10,000.00
$ 10,000.00
0.620921323
$ 6,209.21
Present value of Cash Flows
$ 77,892.72
Less: Initial Investment
$ 80,000.00
Net Present Value
$ (2,107.28)
Profitability Index (77892.72/80000)
0.97
Project B
Year
Cash Outflow
Cash Inflow
Net Cash Flow
Discounting factor
Discounted Cash flow
1
$ 16,000.00
$ 16,000.00
0.909090909
$ 14,545.45
2
$ 16,000.00
$ 16,000.00
0.826446281
$ 13,223.14
3
$ (5,000.00)
$ 16,000.00
$ 11,000.00
0.751314801
$ 8,264.46
4
$ 16,000.00
$ 16,000.00
0.683013455
$ 10,928.22
5
$ 16,000.00
$ 16,000.00
0.620921323
$ 9,934.74
5
$ 8,000.00
$ 8,000.00
0.620921323
$ 4,967.37
Present value of Cash Flows
$ 61,863.38
Less: Initial Investment
$ 60,000.00
Net Present Value
$ 1,863.38
Profitability Index (61863.38/60000)
1.03
Requirement
Project A
Project B
NPV
$ (2,107.28)
$ 1,863.38
NPV of project A is negative hence it is advisable to go with Project B
Requirement 2
Project A
Project B
Profitability Index
$ 0.97
$ 1.03
Profitability index of more than is considered beneficial hence Project B should be accepted.
Requirement 3
A rate at which NPV is equal to zero is IRR or Internal rate of Return.
Discount rate at which NPV is Zero.
Project A 9.04% Approx
Project B 11.14% Approx
Project A
Year
Cash Outflow
Cash Inflow
Net Cash Flow
Discounting factor at 109.04%
Discounted Cash flow
1
$ 20,000.00
$ 20,000.00
0.917094644
$ 18,341.89
2
$ (5,000.00)
$ 20,000.00
$ 15,000.00
0.841062586
$ 12,615.94
3
$ 20,000.00
$ 20,000.00
0.771333993
$ 15,426.68
4
$ 20,000.00
$ 20,000.00
0.707386274
$ 14,147.73
5
$ 20,000.00
$ 20,000.00
0.648740163
$ 12,974.80
5
$ 10,000.00
$ 10,000.00
0.648740163
$ 6,487.40
Present value of Cash Flows
$ 79,994.44
Less: Initial Investment
$ 80,000.00
Net Present Value
$ (5.56)*
NPV of $ 5.56 is due to Round off.
Project B
Year
Cash Outflow
Cash Inflow
Net Cash Flow
Discounting factor
Discounted Cash flow
1
$ 16,000.00
$ 16,000.00
0.899766061
$ 14,396.26
2
$ 16,000.00
$ 16,000.00
0.809578964
$ 12,953.26
3
$ (5,000.00)
$ 16,000.00
$ 11,000.00
0.728431676
$ 8,012.75
4
$ 16,000.00
$ 16,000.00
0.655418099
$ 10,486.69
5
$ 16,000.00
$ 16,000.00
0.589722961
$ 9,435.57
5
$ 8,000.00
$ 8,000.00
0.589722961
$ 4,717.78
Present value of Cash Flows
$ 60,002.31
Less: Initial Investment
$ 60,000.00
Net Present Value
$ 2.31
Profitability Index (61863.38/60000)
1.00**
NPV of $ 1 is due to Round off.
Project A
Year
Cash Outflow
Cash Inflow
Net Cash Flow
Discounting factor at 10%
Discounted Cash flow
1
$ 20,000.00
$ 20,000.00
0.909090909
$ 18,181.82
2
$ (5,000.00)
$ 20,000.00
$ 15,000.00
0.826446281
$ 12,396.69
3
$ 20,000.00
$ 20,000.00
0.751314801
$ 15,026.30
4
$ 20,000.00
$ 20,000.00
0.683013455
$ 13,660.27
5
$ 20,000.00
$ 20,000.00
0.620921323
$ 12,418.43
5
$ 10,000.00
$ 10,000.00
0.620921323
$ 6,209.21
Present value of Cash Flows
$ 77,892.72
Less: Initial Investment
$ 80,000.00
Net Present Value
$ (2,107.28)
Profitability Index (77892.72/80000)
0.97
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.