Question

4. You have just invented a new type of paper clips and you consider producing them on a large scale. This project requires an initial investment of $175,500 in year 0 and is expected to last for 5 years. You plan to spend $10,000 for advertising in year 0 and $2,000 in each of the following years. You also have other expenses of $10,000 per year (excluding year 0). The price per paper clips box is $2 in the first year and is expected to increase by 3% each subsequent year. You expect to sell 30000 boxes every year starting with year 1. (a) Construct a table containing the outflows, inflows and net cashflows for each of the years 0-5. (b) If the discount rate is 0, what is the NPV at year 0? [NPV=$73,048.15] (c) If the discount rate is 5% should you undertake this project? [Yes, NPV is positive] (d) What is the maximum discount rate for which the project should be undertaken? (IRR, 11.87%] (e) If the uncertainty component of your project is 3%, the inflation rate is expected to be 2.5% per year and the real rate of return on other investment projects is 4.4% should you undertake this project? [Yes, NPV positive and DIR

can you show how to do this on excel

Answer 1

a) Cash inflows from Y1-Y5:

Particulars	Y1	Y2	Y3	Y4	Y5
Sales in units	30,000	30,000	30,000	30,000	30,000
Price per unit	$            2.00	$            2.06	$            2.12	$            2.19	$                     2.25
Total sales value	$        60,000	$        61,800	$        63,654	$        65,564	67,531
Advertisement expenses	$        (2,000)	$        (2,000)	$        (2,000)	$        (2,000)	$                 (2,000)
Other expenses	$      (10,000)	$      (10,000)	$      (10,000)	$      (10,000)	$               (10,000)
EBDIT	$        48,000	$        49,800	$        51,654	$        53,564	55,531
Depreciation	$      (35,100)	$      (35,100)	$      (35,100)	$      (35,100)	$               (35,100)
Profit	12,900	14,700	16,554	18,464	20,431
Net Operating cash inflows = Profit + Depreciation	48,000	49,800	51,654	53,564	55,531

Cash outflows in Y0= $175,000(Cost of investment)+$10,000(Advertisement expenses) = $185,000

No further cash outflows in Y1-Y5 except for the expenses as detailed in the above table

b) NPV :
NPV = PV of Cash Inflows - PV of Cash Outflows
If NPV > 0 , Project can be accepted
NPV = 0 , Indifference point. Project can be accepted/ Rejected.
NPV < 0 , Project will be rejected.

In the question, it mentions that the discount rate is 0 and hence the cash flows should not be discounted and to be taken straight

Disc Rate	0%
Year	Cash Flows
0	$      -1,85,000
1	$           48,000
2	$           49,800
3	$           51,654
4	$           53,564
5	$           55,531

How to calculate NPV using Excel?
=NPV(Disc Rate,Range of Cashflows)
NPV = $73,549

c)

Disc Rate	5%
Year	Cash Flows
0	$      -1,85,000
1	$           48,000
2	$           49,800
3	$           51,654
4	$           53,564
5	$           55,531

How to calculate NPV using Excel?
=NPV(Disc Rate,Range of Cashflows)
$36,268.81

Yes, NPV is positive and project can be undertaken

d)

Year	CF	PVF @11 %	Disc CF	PVF @12 %	Disc CF
0	$ -1,85,000.00	1.0000	$ -1,85,000.00	1.0000	$ -1,85,000.00
1	$     48,000.00	0.9009	$     43,243.24	0.8929	$     42,857.14
2	$     49,800.00	0.8116	$     40,418.80	0.7972	$     39,700.26
3	$     51,654.00	0.7312	$     37,768.96	0.7118	$     36,766.30
4	$     53,564.00	0.6587	$     35,284.27	0.6355	$     34,040.89
5	$     55,531.00	0.5935	$     32,954.95	0.5674	$     31,509.78
NPV			$       4,670.21		$         -125.63

IRR = Rate at which least +ve NPV + [ NPV at that rate / Change in NPV due to Inc of 1% in Int Rate ] * 1%
= 11 % + [ 4670.21 / ( 4670.21 - ( -125.63) ) ] * 1 %
= 11 % + [ 4670.21 / ( 4795.84) ] * 1 %
= 11 % + [ 0.97 ] * 1 %
= 11 % + 0.97 %
= 11.97 % ~ 12%

IRR :
IRR is the Rate at which PV of Cash Inflows are equal to PV of Cash Outflows.

If IRR > Cost of Capital - Project can be accepted
IRR = Cost of Capital - Indifferebce Point - Project will be accepted / Rejected
IRR < Cost of Capital - Project will be rejected

It assumes the Cash flows are reinvested at IRR

e)

Nominal rate
Particulars	Values
Real Rate	4.40%
Inflation rate	2.50%

Nominal rate = [ [ 1 + Real rate ] * [ 1 + Inflation rate ] ] - 1
= [ [ 1 + 0.044 ] * [ 1 + 0.025 ] ] - 1
= [ [ 1.044 ] * [ 1.025 ] ] - 1
= [ 1.0701 ] - 1
= 0.0701
i.e, Nominal rate is 7.01 %

Year	CF	CF @97%	PVF @7 %	Disc CF
0	$ -1,85,000.00	$ -1,85,000.00	1.0000	$ -1,85,000.00
1	$     48,000.00	$      46,560.00	0.9346	$     43,514.02
2	$     49,800.00	$      48,306.00	0.8734	$     42,192.33
3	$     51,654.00	$      50,104.38	0.8163	$     40,900.10
4	$     53,564.00	$      51,957.08	0.7629	$     39,637.81
5	$     55,531.00	$      53,865.07	0.7130	$     38,405.05
NPV				$     19,649.31

As the NPV is positive, project can be undertaken