Question

13. Project Evaluation [LO1] Your firm is contemplating the purchase of a new R925,000 computer-based order entry system. The system will be depreciated using the 20 per cent reducing-balance method over its five-year life. It will be worth R90,000 at the end of that time. You will save R360,000 before taxes per year in order-processing costs, and you will be able to reduce working capital by R125,000 (this is a onetime reduction). If the tax rate is 28 per cent, what is the IRR for this project? Show how IRR is calculated in Excel.

Answer 1

Particulars	0	1	2	3	4	5
Cash outflows: (Non Taxable)
Initial Investment
Purchase of new computer based order system	(925,000)
Reduction in working capital at the inception of investment (Cash Inflow)	125,000
Increase in working capital at the end of life of investment ( Cash Outflow)						(125,000)
Total cash outflows (a)	(800,000)					(125,000)
Cash Inflows: (Taxable)
Yearly savings in order processing costs		360,000	360,000	360,000	360,000	360,000
Salvage Value						90,000
Less:
Depreciation @ 20% in Reducing balance method		185,000	148,000	118,400	94,720	75,776
Total Savings		175,000	212,000	241,600	265,280	374,224
Less: Tax @ 28%		49,000	59,360	67,648	74,278	104,783
Savings after tax		126,000	152,640	173,952	191,002	269,441
Add:
Depreciation		185,000	148,000	118,400	94,720	75,776
Cash Inflows after tax (b)		311,000	300,640	292,352	285,722	345,217
Net Cash Inflow /(Outflow) [a+b]	(800,000)	311,000	300,640	292,352	285,722	220,217
Internal Rate of Return	23.73%

The above rate has been arrived by using direct IRR function in excel

Calculation of IRR in excel: (by using trail and error method)

At IRR, NPV = 0,

Particulars	0	1	2	3	4	5	NPV
Net Cash Inflow /(Outflow)	(800,000.00)	311,000.00	300,640.00	292,352.00	285,721.60	220,217.28
Present value factors @ 10%	1	0.9091	0.8264	0.7513	0.6830	0.6209
Present value of cash flows	(800,000.00)	282,727.27	248,462.81	219,648.38	195,151.70	136,737.60	282,727.77
Present value factors @ 20%	1	0.8333	0.6944	0.5787	0.4823	0.4019
Present value of cash flows	(800,000.00)	259,166.67	208,777.78	169,185.19	137,790.12	88,500.39	63,420.14
Present value factors @ 30%	1	0.7692	0.5917	0.4552	0.3501	0.2693
Present value of cash flows	(800,000.00)	239,230.77	177,893.49	133,068.73	100,039.07	59,310.92	(90,457.02)

From above table it can be observed that, NPV changes from positive to negative in between 20% and 30%

Therefore, IRR also exists in between 20% and 30%

IRR Calculation:

Total change in NPV between 20% and 30% = 63,420 - (-90,457) = 153,877

To make NPV to zero from 20% rate the % of change required in total change = (63420/153877)*100 = 41%

since the change is for only 10% (i.e., from 20% to 10%), Net change = 41 * 10% = 4.1%

Therefore IRR = 20% + 4.1% = 24.1% (Approx.)

Note: By using trial and error method there would be slight change in the values arrived.

Thank you.

If you have any doubts or any wrong calculations kindly comment, if you are satisfied with answer kindly provide a like.