Question

Case 2: Evaluate a project with a $25,000 startup cost and annual ongoing costs of $2,500. Cash flows in the first year are estimated to be $1,500 in the first year, $5,500 in the second year, $6,700 in the third year, $9,300 in the fourth year, and $11,500 in the fifth and final year. There is also equipment that is estimated to have a $20,000 salvage value. Assume that the final cash flows and the equipment salvage happen in the same period.

1. Use the NPV function to help calculate the Net Present Value of the project in Case 2 (NPV plus the startup cost[a negative number]) Use 12% as your required return/cost of capital for Case 2

2. Calculate the present value of each cash flow and add the values together. Did the answer match your answer in Q6?

3. Use the XIRR function to calculate the Internal Rate of Return for the project in Case 2.  Use today's date as the start date T0, and the same date a year later for T1 and so on.

4. What required rate/cost of capital would make you indifferent to the project in Case 1 and Case 2? (What rate makes the Net Present Value equal?

5. What is the Discounted Payback Period for Case 2?

6: Using the base required return/cost of capital for cases 1 and 2, which project do you prefer and why?

Answer 1

NPV = Present value of Gross Earnings – Net Cash Investment NPV can be found out from the following formula:

NPV= A1/(1+ r)^1 + A2/ (1 + r)^2 + A3 (1 + r)^3 + …..An/ (1+r)^n – C

Where A1, A2, A3 etc. are the cash inflows at the end of first, second and third year respectively

n = Expected life of investment proposals;

r = Rate of discount which is equal to the cost of capital;

С = Present value of costs.

Initial Investment	$ (25,000.00)
Annual Costs	$   (2,500.00)
Cash Flow in 1st year	$      1,500.00
Cash Flow in 2nd year	$      5,500.00
Cash Flow in 3rd year	$      6,700.00
Cash Flow in 4th year	$      9,300.00
Cash Flow in 5th year	$   11,500.00
Equipment Salvage Value	$   20,000.00
Cost of Capital	12%

	t0	t1	t2	t3	t4	t5
Cash Flows	$ (25,000.00)	$ (2,500.00)	$ (2,500.00)	$ (2,500.00)	$ (2,500.00)	$ (2,500.00)
		$   1,500.00	$   5,500.00	$   6,700.00	$   9,300.00	$ 11,500.00
						$ 20,000.00
Net Cash Flow in the year	$ (25,000.00)	$ (1,000.00)	$   3,000.00	$   4,200.00	$   6,800.00	$ 29,000.00

1.

So NPV = NPV= -1000/(1+ 12%)^1 + 3000/ (1 + 12%)^2 + 4200 (1 + 12%)^3 + 6800 (1+12%)^4 + 29000 (1+12%)^5 – 25000

NPV = $265.10

2.

PV of Cash Flows = -1000/(1+ 12%)^1 + 3000/ (1 + 12%)^2 + 4200 (1 + 12%)^3 + 6800 (1+12%)^4 + 29000 (1+12%)^5

PV of Cash Flows = $ (892.86) + $2,391.58 + $2,989.48 + $4,321.52 + $16,455.38 = $25,265.10

3.

IRR can be measured as:

A₁/ (1+ r) ¹ + A₂/ (1 + r) ² + A₃ (1 + r) ³ + …..A_n/ (1+r) ⁿ-C=0

Where, A₁, A₂ A₃, etc. are the cash inflows at the end of the first, second and third year respectively. We have to measure the Rate r with this formula.

So in our problem:

-1000/(1+ r)^1 + 3000/ (1 + r)^2 + 4200 (1 + r)^3 + 6800 (1 + r)^4 + 29000 (1 + r)^5 – 25000 = 0

Using the XIRR function in excel we get 12%

	t0	t1	t2	t3	t4	t5
Cash Flows	$ (25,000.00)	$ (2,500.00)	$ (2,500.00)	$ (2,500.00)	$ (2,500.00)	$ (2,500.00)
		$   1,500.00	$   5,500.00	$   6,700.00	$   9,300.00	$ 11,500.00
						$ 20,000.00
Net Cash Flow in the year	$ (25,000.00)	$ (1,000.00)	$   3,000.00	$   4,200.00	$   6,800.00	$ 29,000.00
Discounted Cash Flows		$     (892.86)	$   2,391.58	$   2,989.48	$   4,321.52	$ 16,455.38
Date of cash flows assumed	1/1/2010	1/1/2011	1/1/2012	1/1/2013	1/1/2014	1/1/2015
PV of Cash Flows	$25,265.10
IRR	12%

5.

Discounted Payback Period helps us to understand when the initial investment cost will be recovered. Here we use the discounted cash flows to check the same.

Seeing the data in the above table we can see that by the 4th year-end Cash Flows amount to $8809.72. So the Payback Period lies somewhere between the 4th and 5th years.