Question

Buckner Company is considering two capital investments. Both investments have an initial cost of $9,000,000 and total net cash inflows of $17,000,000 over 10 years. Buckner requires a 16 % rate of return on this type investment. Expected net cash inflows are follows: 

Requirement

 1. Use Excel to compute the NPV and IRR of the two plans. Which plan, if any, should the company pursue? 

The NPV (net present value) of Plan Alpha is $ 

The NPV (net present value) of Plan Beta is $ 

Answer 1

1.

Year	Plan Alpha	Plan Beta
0	$            (9,000,000.00)	$             (9,000,000.00)
1	$               1,700,000.00	$                1,700,000.00
2	$               1,700,000.00	$                2,500,000.00
3	$               1,700,000.00	$                3,300,000.00
4	$               1,700,000.00	$                2,500,000.00
5	$               1,700,000.00	$                1,700,000.00
6	$               1,700,000.00	$                1,100,000.00
7	$               1,700,000.00	$                   800,000.00
8	$               1,700,000.00	$                   500,000.00
9	$               1,700,000.00	$                   200,000.00
10	$               1,700,000.00	$                2,700,000.00
NPV	($675,442.49)	$154,667.11
Formula	=NPV(0.16,C91:C101)	=NPV(0.16,D91:D101)
IRR	13.62%	16.63%
Formula	=IRR(C91:C101,0.1)	=IRR(D91:D101,0.1)

Company should pursue Plan Beta.

2. IRR is internal rate of return of investment, it is compared with company's required rate of return whereas NPV is calculated with required rate of return of company. If Net present value is calculated with IRR then it comes out to be zero.

3.

Year	Plan Alpha	Plan Beta
0	$            (7,800,000.00)	$             (7,800,000.00)
1	$               1,700,000.00	$                1,700,000.00
2	$               1,700,000.00	$                2,500,000.00
3	$               1,700,000.00	$                3,300,000.00
4	$               1,700,000.00	$                2,500,000.00
5	$               1,700,000.00	$                1,700,000.00
6	$               1,700,000.00	$                1,100,000.00
7	$               1,700,000.00	$                   800,000.00
8	$               1,700,000.00	$                   500,000.00
9	$               1,700,000.00	$                   200,000.00
10	$               1,700,000.00	$                2,700,000.00
NPV	$359,040.27	$1,189,149.87
Formula	=NPV(0.16,C91:C101)	=NPV(0.16,D91:D101)
IRR	17.42%	21.47%
Formula	=IRR(C91:C101,0.1)	=IRR(D91:D101,0.1)

Company should still pursue Plan Beta as it has higher NPV and IRR.