Your division is considering two investment projects, each of which requires an

Question

Your division is considering two investment projects, each of which requires an up-front expenditure of $15 million. You estimate that the investments will produce the following net cash flows:

Year Project A Project B

1 $5,000,000 $20,000,000

2 10,000,000 10,000,000

3 20,000,000 6,000,000

A) What are the two projects' net present values, assuming the cost of capital is 5%? 10%? 15%? (Please include formulas, or the Excel function used to calculate)

B) What are the two project's IRRs at the same cost of capital (Please include formulas, or the Excel function used to calculate)

Explanation / Answer

Part A)

NPV is the difference between the present value of cash inflows and cash outflows. The NPV can be calculated with the use of following formula:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+Discount Rate)^1 + Cash Flow Year 2/(1+Discount Rate)^2 + Cash Flow Year 2/(1+Discount Rate)^3

__________

Project A

NPV (5%) = -15,000,000 + 5,000,000/(1+5%)^1 + 10,000,000/(1+5%)^2 + 20,000,000/(1+5%)^3 = $16,108,951.52

NPV (10%) = -15,000,000 + 5,000,000/(1+10%)^1 + 10,000,000/(1+10%)^2 + 20,000,000/(1+10%)^3 = $12,836,213.37

NPV (15%) = -15,000,000 + 5,000,000/(1+15%)^1 + 10,000,000/(1+15%)^2 + 20,000,000/(1+15%)^3 = $10,059,587.41

__________

Project B

NPV (5%) = -15,000,000 + 20,000,000/(1+5%)^1 + 10,000,000/(1+5%)^2 + 6,000,000/(1+5%)^3 = $18,300,939.42

NPV (10%) = -15,000,000 + 20,000,000/(1+10%)^1 + 10,000,000/(1+10%)^2 + 6,000,000/(1+10%)^3 = $15,954,169.80

NPV (15%) = -15,000,000 + 20,000,000/(1+15%)^1 + 10,000,000/(1+15%)^2 + 6,000,000/(1+15%)^3 = $13,897,838.42

__________

Part B)

IRR is the minimum acceptable rate of return at which NPV is equal. The IRR can be calculated with the use of IRR function of EXCEL. The basic formula for calculating IRR is as follows:

NPV = 0 = Cash Flow Year 0 + Cash Flow Year 1/(1+IRR)^1 + Cash Flow Year 2/(1+IRR)^2 + Cash Flow Year 2/(1+IRR)^3

__________

IRR has been calculated with the use of EXCEL as follows:

Project A:

IRR = 43.97%

__________

Project B:

IRR = 82.03%

Year Cash Flow 0 -15,000,000 1 5,000,000 2 10,000,000 3 20,000,000 IRR =IRR(-15000000,5000000,10000000,20000000) = 43.97%

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.