You are a financial analyst for the Hittle Company. The director of capital budg
ID: 2614840 • Letter: Y
Question
You are a financial analyst for the Hittle Company. The director of capital budgeting
has asked you to analyze two proposed capital investments, Projects X and Y. Each
project has a cost of $10,000, and the cost of capital for each is 12%. The projects’
expected net cash flows are as follows:
Expected Net Cash Flows
Year Project X Project Y
0 ?$10,000 ?$10,000
1 6,500 3,500
2 3,000 3,500
3 3,000 3,500
4 1,000 3,500
a. Calculate each project’s payback period, net present value (NPV), internal rate
of return (IRR), modified internal rate of return (MIRR), and profitability
index (PI).
b. Which project or projects should be accepted if they are independent?
c. Which project should be accepted if they are mutually exclusive?
d. How might a change in the cost of capital produce a conflict between the NPV
and IRR rankings of these two projects? Would this conflict exist if r were 5%?
(Hint: Plot the NPV profiles.)
e. Why does the conflict exist
Explanation / Answer
A B C D E F G H I J 2 3 a) 4 Payback Period, NPV, IRR and MIRR Calculation for Project X: 5 6 Cash Flow for Project X is as follows: 7 Year 0 1 2 3 4 8 Cash Flow ($10,000) $6,500 $3,000 $3,000 $1,000 9 10 Calculation of Payback period: 11 Payback period is the period when investment amount is recovered. 12 Year 0 1 2 3 4 13 Free Cash Flow ($10,000) $6,500 $3,000 $3,000 $1,000 14 Cumulative cash flow ($10,000) ($3,500) ($500) $2,500 $3,500 15 16 Payback period is when cumulative free cash flow becomes zero. 17 It can be seen from above that cumulative cash flow becomes zero between year 2 and year 3. 18 19 To estimate the exact payback period cumulative free cash flow can be proprated over the years as follows: 20 Payback period 2.17 =F12+(0-F14)/(G14-F14) 21 22 Hence Payback period is 2.17 Years 23 24 25 26 Given the following cash flow and WACC, NPV for the project can be calculated as follows: 27 Year 0 1 2 3 4 28 Incremental Cash Flow ($10,000) $6,500 $3,000 $3,000 $1,000 29 MARR (i) 12% 30 (P/F,i,n) for each year 0.89 0.80 0.71 0.64 31 Present Value of cash flows = FCF*(P/F,i,n) $5,803.57 $2,391.58 $2,135.34 $635.52 32 Present value if future cash flows $10,966.01 =SUM(E31:H31) 33 34 NPV for Project =Present value fo future cash flows - Initial investment 35 $966.01 =D32+D28 36 37 Hence NPV for Project $966.01 38 39 Calculation of IRR: 40 IRR is the rate at which NPV of the project will be zero. 41 Given the following cash flow IRR can be calculated as below: 42 43 Year 0 1 2 3 4 44 Incremental Cash Flow ($10,000) $6,500 $3,000 $3,000 $1,000 45 46 NPV=-10000+6500/(1+IRR)^1 +3000/(1+IRR)^2+3000/(1+IRR)^3+1000/(1+IRR)^4 47 0=-10000+6500/(1+IRR)^1 +3000/(1+IRR)^2+3000/(1+IRR)^3+1000/(1+IRR)^4 48 IRR can be found using hit and trial method for above equation. 49 50 IRR can also be found using IRR function in excel as follows: 51 Year 0 1 2 3 4 52 Incremental Cash Flow ($10,000) $6,500 $3,000 $3,000 $1,000 53 IRR 18.03% =IRR(D52:H52) 54 55 Inremental IRR of the project 18.03% 56 57 Calculation of MIRR: 58 MIRR is the rate at which PV of cash outflows is equal to the PV of FV of cash inflows. 59 Project term 4 years 60 Incremental cash flows: 61 Year 0 1 2 3 4 62 Incremental Cash Flow ($10,000) $6,500 $3,000 $3,000 $1,000 63 64 PV of cash outflow $10,000 65 66 Calculation of Future Value of cash inflows 67 Year 0 1 2 3 4 68 Incremental Cash Flow $6,500 $3,000 $3,000 $1,000 69 WACC 12% 70 Future Value of cash inflows $9,132 $3,763 $3,360 $1,000 71 Total FV of cash inflows $17,255 72 73 Let r be the MIRR then, 74 PV of cash outflow*(1+r)4=FV of cash inflow 75 10000*(1+r)4=17255 76 77 Solving the above equation: 78 r = 14.61% 79 80 Hence MIRR is 14.61% 81 82 Calculation of Profitability Index: 83 84 Profitability index is given by following formula: 85 Profitability index = Present value of future cash flows / Initial Investment 86 or 87 Profitability index = (NPV+Initial Investment) / Initial Investment 88 89 Using the following data: 90 NPV $966.01 91 Initial Investment $10,000 92 93 Profitability index = (NPV+Initial Investment) / Initial Investment 94 1.10 =(D90+D91)/D91 95 96 Hence profitability index of project X is 1.10 97 98 Payback Period, NPV, IRR and MIRR Calculation for Project Y 99 100 Cash Flow for Project Y is as follows: 101 Year 0 1 2 3 4 102 Cash Flow ($10,000) $3,500 $3,500 $3,500 $3,500 103 104 Calculation of Payback period: 105 Payback period is the period when investment amount is recovered. 106 Year 0 1 2 3 4 107 Free Cash Flow ($10,000) $3,500 $3,500 $3,500 $3,500 108 Cumulative cash flow ($10,000) ($6,500) ($3,000) $500 $4,000 109 110 Payback period is when cumulative free cash flow becomes zero. 111 It can be seen from above that cumulative cash flow becomes zero between year 2 and year 3. 112 113 To estimate the exact payback period cumulative free cash flow can be proprated over the years as follows: 114 Payback period 2.86 =F106+(0-F108)/(G108-F108) 115 116 Hence Payback period is 2.86 Years 117 118 Given the following cash flow and WACC, NPV for the project can be calculated as follows: 119 Year 0 1 2 3 4 120 Incremental Cash Flow ($10,000) $3,500 $3,500 $3,500 $3,500 121 MARR (i) 12% 122 (P/F,i,n) for each year 0.89 0.80 0.71 0.64 123 Present Value of cash flows = FCF*(P/F,i,n) $3,125.00 $2,790.18 $2,491.23 $2,224.31 124 Present value if future cash flows $10,630.72 =SUM(E123:H123) 125 126 NPV for Project =Present value fo future cash flows - Initial investment 127 $630.72 =D124+D120 128 129 Hence NPV for Project $630.72 130 131 Calculation of IRR: 132 IRR is the rate at which NPV of the project will be zero. 133 Given the following cash flow IRR can be calculated as below: 134 135 Year 0 1 2 3 4 136 Incremental Cash Flow ($10,000) $3,500 $3,500 $3,500 $3,500 137 138 NPV=-10000+3500/(1+IRR)^1 +3500/(1+IRR)^2+3500/(1+IRR)^3+3500/(1+IRR)^4 139 0=-10000+3500/(1+IRR)^1 +3500/(1+IRR)^2+3500/(1+IRR)^3+3500/(1+IRR)^4 140 IRR can be found using hit and trial method for above equation. 141 142 IRR can also be found using IRR function in excel as follows: 143 Year 0 1 2 3 4 144 Incremental Cash Flow ($10,000) $3,500 $3,500 $3,500 $3,500 145 IRR 14.96% =IRR(D144:H144) 146 147 Inremental IRR of the project 14.96% 148 149 Calculation of MIRR: 150 MIRR is the rate at which PV of cash outflows is equal to the PV of FV of cash inflows. 151 Project term 4 years 152 Incremental cash flows: 153 Year 0 1 2 3 4 154 Incremental Cash Flow ($10,000) $3,500 $3,500 $3,500 $3,500 155 156 PV of cash outflow $10,000 157 158 Calculation of Future Value of cash inflows 159 Year 0 1 2 3 4 160 Incremental Cash Flow $3,500 $3,500 $3,500 $3,500 161 WACC 12% 162 Future Value of cash inflows $4,917 $4,390 $3,920 $3,500 163 Total FV of cash inflows $16,728 =SUM(E162:H162) 164 165 Let r be the MIRR then, 166 PV of cash outflow*(1+r)4=FV of cash inflow 167 10000*(1+r)4=16728 168 169 Solving the above equation: 170 r = 13.73% =((D163/D156)^(1/D151))-1 171 172 Hence MIRR of Project Y is 13.73% 173 174 175 Calculation of Profitability Index: 176 177 Profitability index is given by following formula: 178 Profitability index = Present value of future cash flows / Initial Investment 179 or 180 Profitability index = (NPV+Initial Investment) / Initial Investment 181 182 Using the following data: 183 NPV $630.72 184 Initial Investment $10,000 185 186 Profitability index = (NPV+Initial Investment) / Initial Investment 187 1.06 =(D183+D184)/D184 188 189 Hence profitability index of project Y is 1.06 190 191 b) 192 193 Since NPV of project X and Y both are positive and independent therefore both projects can be accepted. 194 195 c) 196 197 Since both project are mutually exlusive, therefore only one project can be selected. 198 As profitability index shows the profitability of the project and it is higher for project X, 199 therefore project X should be selected. 200
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.