Question

You are required to use a financial calculator or spreadsheet (Excel) to solve the following capital budgeting problem (sample questions and solutions are provided for guidance): Windrunner Corp. is considering a new machine that requires an initial investment of $800,000 installed, and has a useful life of 10 years. The expected annual after-tax cash flows for the machine are $120,000 during the first 5 years, $150,000 during years 6 through 8 and $180,000 during the last two years.

(i) Develop the timeline (linear representation of the timing of cash flows)

(ii) Calculate the Internal Rate of Return (IRR)

(iii) Calculate the Net Present Value (NPV) at the following required rates of return:

(a) 9% (b) 10% (c) 11% (d) 12%

(iv) Using IRR and NPV criterion, comment if the project should be accepted or rejected at the following required rates of return:

(a) 9% (b) 10% (c) 11% (d) 12%

(v) Plot the Net Present Value profile (NPV on Y axis and rates of return on X-axis)

Answer 1

First list down the information provided in question:

Initial Investment (CF₀) : $800,000

Useful life : 10 years

Expected annual cash flows (after tax)

Year 1-5 : $ 120,000

Year 6-8 : $ 150,000

Year 9-10 : $ 180,000

i) Timeline of cash flows

ii) Calculate the Internal Rate of Return (IRR)

As per definition, IRR is the discount rate that makes the present value of future after tax cash flows equal to the investment outlay.

Therefore at IRR - CF₀ = PV of future after tax cash flows

The following steps needs to be followed for calculating IRR:

Step no. 1: First prepare the following table to list down all year wise cash flows:

Years	Cash Flows
0	$    -8,00,000
1	$     1,20,000
2	$     1,20,000
3	$     1,20,000
4	$     1,20,000
5	$     1,20,000
6	$     1,50,000
7	$     1,50,000
8	$     1,50,000
9	$     1,80,000
10	$     1,80,000

Step No. 2: For calculating IRR, either use trial and error method or alternatively in excel, formulae of 'IRR' can be used'

For doing, trail and error calculate NPV at various discount rates. IRR falls between the discount rates, where NPV is converting from negative to positive and arriving close to zero.

NPV is the present value of future after tax cash flows minus the investment outlay.

Step No. 3: Calculating NPV at various discount rates viz. 9%, 10%, 11%, 12%

PVF@9%	PV of future cash flows		PVF@10%	PV of future cash flows		PVF@11%	PV of future cash flows		PVF@12%	PV of future cash flows
1	$    -8,00,000		1	$    -8,00,000		1	$    -8,00,000		1	$      -8,00,000
0.917431	$      1,10,092		0.9090909	$      1,09,091		0.9009009	$      1,08,108		0.8928571	$       1,07,143
0.84168	$      1,01,002		0.8264463	$         99,174		0.8116224	$         97,395		0.7971939	$          95,663
0.772183	$         92,662		0.7513148	$         90,158		0.7311914	$         87,743		0.7117802	$          85,414
0.708425	$         85,011		0.6830135	$         81,962		0.658731	$         79,048		0.6355181	$          76,262
0.649931	$         77,992		0.6209213	$         74,511		0.5934513	$         71,214		0.5674269	$          68,091
0.596267	$         89,440		0.5644739	$         84,671		0.5346408	$         80,196		0.5066311	$          75,995
0.547034	$         82,055		0.5131581	$         76,974		0.4816584	$         72,249		0.4523492	$          67,852
0.501866	$         75,280		0.4665074	$         69,976		0.4339265	$         65,089		0.4038832	$          60,582
0.460428	$         82,877		0.4240976	$         76,338		0.3909248	$         70,366		0.36061	$          64,910
0.422411	$         76,034		0.3855433	$         69,398		0.3521845	$         63,393		0.3219732	$          57,955
NPV@9%	$         72,444		NPV@10%	$         32,251		NPV@11%	$          -5,199		NPV@12%	$         -40,132

Basis, above results, we can see that NPV is converting from positive value @ 10% to negative value @ 11%, therefore IRR falls between 10% to 11%. Further, apply the same steps and calculate NPV @ 10.65%, 10.75%, 10.85% & 10.95%.

Conclusion: Thus, based upon trail and error, the IRR is 10.8570%

Tip: To cross check your answer, please calculate NPV at the arrived IRR, it should come either zero or close to zero

iii) Calculate the Net Present Value (NPV)

Kindly refer step no. 2 in above mentioned reply to find out the NPV calculation.

iv) Using IRR and NPV criterion, comment if the project should be accepted or rejected at the following required rates of return

Criteria to invest based upon IRR is as follows:

Invest if IRR > r

Do not invest if IRR < r

r : required rate of return (in the given question, r is 9%, 10%, 11%, 12%)

In the said example, IRR is 10.8570% which is higher than 9% & 10% required rate of return. Therefore investment should be made at 9% and 10%. Whereas investment is not feasible at 11% & 12%.

Criteria to invest based upon NPV is as follows:

Invest if NPV > 0

Do not invest if NPV < 0

r : required rate of return (in the given question, r is 9%, 10%, 11%, 12%)

In the said example, NPV is positive at  9% & 10% and negative at 11% & 12%. Therefore investment should be made at 9% and 10%. Whereas investment is not feasible at 11% & 12%.

v) Plot the Net Present Value profile (NPV on Y axis and rates of return on X-axis)

The same is as follows using chart function of MS Excel:

Trust the same will serve your purpose.

Should you need any clarifications, please feel free to comment.