Question

3.2 Discounted Cash Flow

For the project below, please answer the following questions:

(a) What are the NPVs for 11%, 13% and 15% respectively?

(b) Based on your results above; what is the approximate IRR (and why)?

(c) Calculate the exact IRR with Excel (to 1 decimal place)

Year (n)	0	1	2	3	4
Undiscounted cash flow	-$600,000	$200,000	$200,000	$200,000	$200,000
DCF @ 11%
∑DCF @11%
DCF @ 13%
∑DCF @13%
DCF @ 15%
∑DCF @15%

Notes:

• DCF = Discounted Cash Flow
• ∑DCF = Cumulative Discounted Cash Flow

Answer 1

(a) NPV calculations are as shown:

So, all cash flows are discounted using the Present value formula which is

where

• C is the cash flow
• r is the interest rate
• n is the period in which a particular cash flow happens

The formulas used in excel are:

So, the NPVs at 11%, 13% and 15% are $20,489.1, -$5,105.7 and -$29,004.3.

(b)

IRR is the rate at which NPV is zero. Looking at the above answers, it can be inferred that IRR must lie between 11% and 13% because at 11%, NPV is positive whereas at 13%, it is negative. So, it must be zero somewhere between the two. A possible guess could be 12%.

(c)

In excel IRR can be calculated using the IRR function. Its implementation is as shown:

And so the IRR of the given series of cash flows is 12.6%.