In: Finance
4. Three are three projects listed below. The firm’s required rate of return is 13%.
Year Project AB Project LM Project UV
0 $ (90,000) $ (100,000) $ (96,500)
1 39,000 0 (55,000)
2 39,000 0 100,000
3 39,000 147,500 100,000
a) Compute net present value and internal rate of return of each project
Project AB LM UV
NPV
IRR
b) If three projects are mutually exclusive, which one should be chosen?
c) What is the discount rate when NPVAB equals NPVUV (i.e., crossover rate)?
ΔCF0= ΔCF1= ΔCF2= ΔCF3=
IRR=
d) Compute the traditional payback period for each project.
e) Please follow the steps below to compute modified IRR (MIRR) of Project UV.
1) PV of cash outflows:
2) FV of cash inflows:
3) MIRR
Part A: NPV
Project AB
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -90,000.00 | 1/(1+0.13)^0= | 1 | 1*-90000= | $ -90,000.00 |
1 | $ 39,000.00 | 1/(1+0.13)^1= | 0.884955752 | 0.884955752212389*39000= | $ 34,513.27 |
2 | $ 39,000.00 | 1/(1+0.13)^2= | 0.783146683 | 0.783146683373796*39000= | $ 30,542.72 |
3 | $ 39,000.00 | 1/(1+0.13)^3= | 0.693050162 | 0.693050162277696*39000= | $ 27,028.96 |
NPV = Sum of all Discounted CF | $ 2,084.95 |
Project LM
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -1,00,000.00 | 1/(1+0.13)^0= | 1 | 1*-100000= | $ -1,00,000.00 |
1 | $ - | 1/(1+0.13)^1= | 0.884955752 | 0.884955752212389*0= | $ - |
2 | $ - | 1/(1+0.13)^2= | 0.783146683 | 0.783146683373796*0= | $ - |
3 | $ 1,47,500.00 | 1/(1+0.13)^3= | 0.693050162 | 0.693050162277696*147500= | $ 1,02,224.90 |
NPV = Sum of all Discounted CF | $ 2,224.90 |
Project UV
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -96,500.00 | 1/(1+0.13)^0= | 1 | 1*-96500= | $ -96,500.00 |
1 | $ -55,000.00 | 1/(1+0.13)^1= | 0.884955752 | 0.884955752212389*-55000= | $ -48,672.57 |
2 | $ 1,00,000.00 | 1/(1+0.13)^2= | 0.783146683 | 0.783146683373796*100000= | $ 78,314.67 |
3 | $ 1,00,000.00 | 1/(1+0.13)^3= | 0.693050162 | 0.693050162277696*100000= | $ 69,305.02 |
NPV = Sum of all Discounted CF | $ 2,447.12 |
IRR is the discount rate at which the NPV = 0It can be calculated by hit and trial or using a financial calculator or Excel's goal seek function:
Project AB: The IRR = 14.36% rounded to 2 decimal places
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -90,000.00 | 1/(1+0.143596677060947)^0= | 1 | 1*-90000= | $ -90,000.00 |
1 | $ 39,000.00 | 1/(1+0.143596677060947)^1= | 0.87443416 | 0.874434160275814*39000= | $ 34,102.93 |
2 | $ 39,000.00 | 1/(1+0.143596677060947)^2= | 0.764635101 | 0.764635100657268*39000= | $ 29,820.77 |
3 | $ 39,000.00 | 1/(1+0.143596677060947)^3= | 0.668623052 | 0.66862305216065*39000= | $ 26,076.30 |
NPV = Sum of all Discounted CF | $ 0.00 |
Project LM: The IRR = 13.83% rounded to 2 decimal places
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -1,00,000.00 | 1/(1+0.138319057496398)^0= | 1 | 1*-100000= | $ -1,00,000.00 |
1 | $ - | 1/(1+0.138319057496398)^1= | 0.878488323 | 0.878488323123909*0= | $ - |
2 | $ - | 1/(1+0.138319057496398)^2= | 0.771741734 | 0.771741733865058*0= | $ - |
3 | $ 1,47,500.00 | 1/(1+0.138319057496398)^3= | 0.677966102 | 0.677966101667853*147500= | $ 1,00,000.00 |
NPV = Sum of all Discounted CF | $ -0.00 |
Project UV : The IRR = 13.89% rounded to 2 decimal places
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -96,500.00 | 1/(1+0.138885866227248)^0= | 1 | 1*-96500= | $ -96,500.00 |
1 | $ -55,000.00 | 1/(1+0.138885866227248)^1= | 0.878051111 | 0.878051110874411*-55000= | $ -48,292.81 |
2 | $ 1,00,000.00 | 1/(1+0.138885866227248)^2= | 0.770973753 | 0.770973753307787*100000= | $ 77,097.38 |
3 | $ 1,00,000.00 | 1/(1+0.138885866227248)^3= | 0.676954361 | 0.676954360546916*100000= | $ 67,695.44 |
NPV = Sum of all Discounted CF | $ 0.00 |
Part B: The project with highest NPV should be chosen, and that is project UV, No matter what the IRR method is suggesting.
Part C: We again use excel's goalseek to arrive at the required discount rate which comes to 13.30% rounded to 2 decimal places.
Project AB:
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -90,000.00 | 1/(1+0.132957670519204)^0= | 1 | 1*-90000= | $ -90,000.00 |
1 | $ 39,000.00 | 1/(1+0.132957670519204)^1= | 0.882645509 | 0.882645509202235*39000= | $ 34,423.17 |
2 | $ 39,000.00 | 1/(1+0.132957670519204)^2= | 0.779063095 | 0.779063094914872*39000= | $ 30,383.46 |
3 | $ 39,000.00 | 1/(1+0.132957670519204)^3= | 0.687636542 | 0.687636542111806*39000= | $ 26,817.83 |
NPV = Sum of all Discounted CF | $ 1,624.46 |
Project UV:
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -96,500.00 | 1/(1+0.132957670519204)^0= | 1 | 1*-96500= | $ -96,500.00 |
1 | $ -55,000.00 | 1/(1+0.132957670519204)^1= | 0.882645509 | 0.882645509202235*-55000= | $ -48,545.50 |
2 | $ 1,00,000.00 | 1/(1+0.132957670519204)^2= | 0.779063095 | 0.779063094914872*100000= | $ 77,906.31 |
3 | $ 1,00,000.00 | 1/(1+0.132957670519204)^3= | 0.687636542 | 0.687636542111806*100000= | $ 68,763.65 |
NPV = Sum of all Discounted CF | $ 1,624.46 |
We can even look at this graphically by plotting the NPV of the two projects for different discount rates:
Rate | AB | UV |
5% | 16206.67 | 28205.76 |
10% | 6987.23 | 11276.11 |
13.2957671% | 1624.46 | 1624.46 |
15% | -954.22 | -2960.1 |
20% | -7847.22 | -15018.52 |
Part D: Payback period
Project AB
Year | Opening Balance | Investment | CF | Closing Balance |
0 | $ 90,000.00 | $ 90,000.00 | ||
1 | $ 90,000.00 | $ 39,000.00 | $ 51,000.00 | |
2 | $ 51,000.00 | $ 39,000.00 | $ 12,000.00 | |
3 | $ 12,000.00 | $ 39,000.00 | $ -27,000.00 |
Opening balance = previous year's closing balance
Closing balance = opening balance + investment-CF
We see that at the end of year 2 the closing balance was 12000 and in year 3 the CF = 39000 so the portion of year 3 in which the 12000 was recovered = 12000/39000 = 0.31 so the payback period = 2.31 years
Project LM
Year | Opening Balance | Investment | Principal repayment | Closing Balance |
0 | $ 1,00,000.00 | $ 1,00,000.00 | ||
1 | $ 1,00,000.00 | $ - | $ 1,00,000.00 | |
2 | $ 1,00,000.00 | $ - | $ 1,00,000.00 | |
3 | $ 1,00,000.00 | $ 1,47,500.00 | $ -47,500.00 |
We see that at the end of year 2 the closing balance was 100000 and in year 3 the CF = 147500 so the portion of year 3 in which the 100000 was recovered = 100000/147500= 0.68 so the payback period = 2.68 years
Project UV
Year | Opening Balance | Investment | Principal repayment | Closing Balance |
0 | $ 96,500.00 | $ 96,500.00 | ||
1 | $ 96,500.00 | $ -55,000.00 | $ 1,51,500.00 | |
2 | $ 1,51,500.00 | $ 1,00,000.00 | $ 51,500.00 | |
3 | $ 51,500.00 | $ 1,00,000.00 | $ -48,500.00 |
We see that at the end of year 2 the closing balance was 51500 and in year 3 the CF = 100000 so the portion of year 3 in which the 51500 was recovered = 51500/100000 =0.52 so the payback period = 2.52 years
So according to this method project AB with shortest payback period should be selected, however, this doesn't take into account the time value of money and therefore, this is not as comprehensive as the NPV method