Question

A company is considering a new project. The project costs $430,000 and has a 5-year life. During the Year 1, it will produce a cash flow of $114,000, which is expected to grow at 6.70% per annum from Year 2 to 5. The appropriate discount rate is 13.60% per annum. Please answer the following questions:

(a) Work out the Year 2 to 5 cash flows from this investment (with no added terminal value) (1 mark).

(b) Use a financial function to work out the Net Present Value (NPV) and the Internal Rate of Return (IRR) of the project (1 mark).

(c) Use a logical function to indicate whether the NPV and IRR suggest that this project should proceed (1 mark).

(d) Add a Data Table that displays how the net present value changes as the discount rate and growth rate vary between 12%-17% and 6%-11% respectively, as shown in the table below (2 marks - no marks for entering individual formulae into the table).

Answer 1

(a): Year 2 to 5 cash flows are shown below:

Year	CF
2	121,638.00
3	129,787.75
4	138,483.52
5	147,761.92

Explanation: Year 2 cash flow = 114,000*1.067 = $121,638. In year 3 = 121638*1.067 = 129787.75. In year 4 = 129787.75*1.067 = 138483.52 and in year 5 = 138483.52*1.067 = 147761.92

(b): The functions of "NPV" and "IRR" in excel can be used here.

The syntax for NPV is: NPV (13.6%, 114000, 121638, 129787.75, 138483.52, 147761.92) + (-430000). This will give a value of $14,398.45

IRR can be computed using the "IRR" function in excel. The syntax will be: IRR (-430000:147761.92, 0). This will give a value of 14.91%

Thus NPV is $14,398.45 and IRR is 14.91%

(c): Logical function: If NPV > 0 then project should be accepted. If IRR > discount rate then the project should be accepted. Here NPV of 14,398.45 > 0 and IRR of 14.91% > discount rate of 13.6%. Hence the project should be accepted.

(d): For 12%-17%:

Rate	NPV
12%	32,988.00
13%	21,228.56
14%	9,935.80
15%	-914.02
16%	-11,343.18
17%	-21,372.65

For 6%-11%:

Rate	NPV
6%	114,885.08
7%	99,731.48
8%	85,224.04
9%	71,327.71
10%	58,009.73
11%	45,239.41