Q38PGB
ExpertverifiedHamilton Company is considering two capital investments. Both investments have an initial cost of $7,000,000 and total net cash inflows of $16,000,000 over 10 years. Hamilton requires a 20% rate of return on this type of investment. Expected net cash inflows are as follows:
Year  Plan Alpha  Plan Beta 
1  $1,600,000  $1,600,000 
2  $1,600,000  2,200,000 
3  $1,600,000  2,800,000 
4  $1,600,000  2,200,000 
5  $1,600,000  1,600,000 
6  $1,600,000  1,500,000 
7  $1,600,000  1,300,000 
8  $1,600,000  1,100,000 
9  $1,600,000  900,000 
10  $1,600,000  800,000 
Total  $16,000,000  $16,000,000 
Requirements
1. Use Excel to compute the NPV and IRR of the two plans. Which plan, if any, should the company pursue?
2. Explain the relationship between NPV and IRR. Based on this relationship and the company’s required rate of return, are your answers as expected in Requirement 1? Why or why not?
3. After further negotiating, the company can now invest with an initial cost of $6,500,000. Recalculate the NPV and IRR. Which plan, if any, should the company pursue?
1. The company should pursue plan Beta with a positive NPV.
 Plan Alpha  Plan Beta 
NPV  ($292,044.66)  $610,050.83 
IRR  18.76%  22.89% 
2. NPV and IRR have direct relation.
3. When the initial investment declines to $6,500,000, plan beta must be selected because of higher NPV and IRR.
 Plan Alpha  Plan Beta 
NPV  $207,955.34  $1,110,050.83 
IRR  20.94%  25.59% 
The internal rate of return is the metric used in capital budgeting to determine the project’s profitability. IRR is calculated using the same formula as used for NPV. Under calculation of IRR net present value is considered as 0.
Project  Plan Alpha 
 Plan Beta 
Useful life  10 
 10 
Discount rate  0.2 
 0.2 
Initial investment  7000000 
 7000000 
1  1600000 
 1600000 
2  1600000 
 2200000 
3  1600000 
 2800000 
4  1600000 
 2200000 
5  1600000 
 1600000 
6  1600000 
 1500000 
7  1600000 
 1300000 
8  1600000 
 1100000 
9  1600000 
 900000 
10  1600000 
 800000 
Total  16,000,000 
 16000000 




Outputs 



NPV  ($292,044.66) 
 $610,050.83 
IRR  18.76% 
 22.89% 
Excel formula for NPV: =NPV(Discount rate, Cash flow from 1^{st} year to 10^{th} year)+Initial investment
Excel formula for IRR: =IRR(All cash flows from year 1^{st} year to 10^{th} year including initial investment)
It can be said that the IRR and NPV have a direct relationship with each other. So, the higher the NPV higher the IRR, and the lower the NPV, the lower the IRR. The results of requirement 1 are not as expected because the net present value of plan A is negative, and its IRR is still higher than plan B. It is so because the negative value of the NPV is higher than the positive value of the NPV.
Project  Plan Alpha 

 Plan Beta 
Useful life  10 

 10 
Discount rate  0.2 

 0.2 
Initial investment  6500000 

 6500000 
1  1600000 

 1600000 
2  1600000 

 2200000 
3  1600000 

 2800000 
4  1600000 

 2200000 
5  1600000 

 1600000 
6  1600000 

 1500000 
7  1600000 

 1300000 
8  1600000 

 1100000 
9  1600000 

 900000 
10  1600000 

 800000 
Total  16,000,000 

 16000000 





Outputs 




NPV  $207,955.34 

 $1,110,050.83 
IRR  20.94% 

 25.59% 
Excel formula for NPV: =NPV(Discount rate, Cash flow from 1^{st} year to 10^{th} year)+Initial investment
Excel formula for IRR: =IRR(All cash flows from year 1^{st} year to 10^{th} year including initial investment)
94% of StudySmarter users get better grades.
Sign up for free