Question

​Payback, NPV, and IRR   Rieger International is evaluating the feasibility of investing $95,000 in a piece of equipment that has a 5-year life. The firm has estimated the cash inflows associated with the proposal as shown in the following​ table:

1 $40,000

2 $20,000

3 $35,000

4 $30,000

5 $30,000

. The firm has a

9%

cost of capital.

a.  Calculate the payback period for the proposed investment.

b.  Calculate the net present value​ (NPV) for the proposed investment.

c.  Calculate the internal rate of return

​(IRR)​,

rounded to the nearest whole​ percent, for the proposed investment.

d.  Evaluate the acceptability of the proposed investment using NPV and IRR. What recommendation would you make relative to implementation of the​ project?

Please show me how to do in Excel

Answer 1

Solution a:

Computation of Cumulative Cash flows
Year	Cash inflows	Cumulative Cash Inflows
1	$40,000.00	$40,000.00
2	$20,000.00	$60,000.00
3	$35,000.00	$95,000.00
4	$30,000.00	$125,000.00
5	$30,000.00	$155,000.00

As investment of $95,000 is recovered at the end of 3 years, therefore Payback period = 3 years

Solution b:

Computation of NPV
Particulars	Period	PV Factor	Amount	Present Value
Cash outflows:
Initial investment	0	1	$95,000	$95,000
Present Value of Cash outflows (A)				$95,000
Cash Inflows
Year 1	1	0.917431	$40,000.00	$36,697
Year 2	2	0.841680	$20,000.00	$16,834
Year 3	3	0.772183	$35,000.00	$27,026
Year 4	4	0.708425	$30,000.00	$21,253
Year 5	5	0.649931	$30,000.00	$19,498
Present Value of Cash Inflows (B)				$121,308
Net Present Value (NPV) (B-A)				$26,308

Solution c:

For computation of IRR, lets calculate NPV at 19% and 20% discount rate:

Computation of NPV
Particulars	Period	Amount	Discount rate 19%		Discount rate 20%
			PV Factor	Present Value	PV Factor	Present Value
Cash outflows:
Initial investment	0	$95,000	1	$95,000	1	$95,000
Present Value of Cash outflows (A)				$95,000		$95,000
Cash Inflows
Year 1	1	$40,000.00	0.8403361	$33,613	0.8333333	$33,333
Year 2	2	$20,000.00	0.7061648	$14,123	0.6944444	$13,889
Year 3	3	$35,000.00	0.5934158	$20,770	0.5787037	$20,255
Year 4	4	$30,000.00	0.4986688	$14,960	0.4822531	$14,468
Year 5	5	$30,000.00	0.4190494	$12,571	0.4018776	$12,056
Present Value of Cash Inflows (B)				$96,038		$94,001
Net Present Value (NPV) (B-A)				$1,038		-$999

IRR = 19% + (NPV at 19% - NPV at IRR) / (NPV at 19% - NPV at 20%)

= 19% + ($1,038 - 0) / ($1,038 + $999) = 19.51% = 20% (Rounded off)

Solution d:

As NPV is postive, further IRR is also above cost of capital of the company, therefore proposed investment should be accepted.