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

Answer 1

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.