In: Finance
After the problem has been set up, the calculator should be used to compute NPV and IRR. Show calculator inputs.
Chunky Monkey Ice Cream has a 12% cost of capital, and has generated the following information on two mutually exclusive projects. Project A will require an investment of $100,000 while the initial outlay for Project B is 2 ½ times greater:
Year |
Project A |
Project B |
1 |
30,000 |
75,000 |
2 |
30,000 |
75,000 |
3 |
30,000 |
75,000 |
4 |
30,000 |
75,000 |
5 |
50,000 |
100,000 |
a) Payback period is calcualted as follows:
Project A
Year | Opening Balance | CF | Closing Balance |
1 | $ 1,00,000.00 | $ 30,000.00 | $ 70,000.00 |
2 | $ 70,000.00 | $ 30,000.00 | $ 40,000.00 |
3 | $ 40,000.00 | $ 30,000.00 | $ 10,000.00 |
4 | $ 10,000.00 | $ 30,000.00 | $ -20,000.00 |
5 | $ -20,000.00 | $ 50,000.00 | $ -70,000.00 |
Opening balance of year 1= Cost
Opening balance = previous year's closing balance for all years
after year 1
Closing balance = Opening balance - CF
The closing balance of year 3 was 10000 and the CF for year 4 was was 30000 so the portion of year during which the 10000 is recovered is 10000/30000 x 12 = 4 months
Total time taken to recover initial investment of 100000
is 3 years 4 months
Project B
Year | Opening Balance | CF | Closing Balance |
1 | $ 2,50,000.00 | $ 75,000.00 | $ 1,75,000.00 |
2 | $ 1,75,000.00 | $ 75,000.00 | $ 1,00,000.00 |
3 | $ 1,00,000.00 | $ 75,000.00 | $ 25,000.00 |
4 | $ 25,000.00 | $ 75,000.00 | $ -50,000.00 |
5 | $ -50,000.00 | $ 1,00,000.00 | $ -1,50,000.00 |
The closing balance of year 3 was 25000 and the CF for year 4 was was 75000 so the portion of year during which the 10000 is recovered is 25000/75000x 12 = 4 months
Total time taken to recover initial investment of 250000
is 3 years 4 months
b) NPV of project A is as follows:
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -1,00,000.00 | 1/(1+0.12)^0= | 1 | 1*-100000= | $ -1,00,000.00 |
1 | $ 30,000.00 | 1/(1+0.12)^1= | 0.892857143 | 0.892857142857143*30000= | $ 26,785.71 |
2 | $ 30,000.00 | 1/(1+0.12)^2= | 0.797193878 | 0.79719387755102*30000= | $ 23,915.82 |
3 | $ 30,000.00 | 1/(1+0.12)^3= | 0.711780248 | 0.711780247813411*30000= | $ 21,353.41 |
4 | $ 30,000.00 | 1/(1+0.12)^4= | 0.635518078 | 0.635518078404831*30000= | $ 19,065.54 |
5 | $ 50,000.00 | 1/(1+0.12)^5= | 0.567426856 | 0.567426855718599*50000= | $ 28,371.34 |
NPV = Sum of all Discounted CF | $ 19,491.82 |
Calculator inputs would be: CF0 = -100000, CF1 = 30000 with frequency = 4 CF2 = 50000 frequency 1 then we compute the NPV at i = 12 or we can even input CF0 = -100000, CF1 = 30000 , CF2 = 30000, CF3 = 30000, CF4=30000, CF5 = 50000 each with frequency 1
NPV of project B is as follows:
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -2,50,000.00 | 1/(1+0.12)^0= | 1 | 1*-250000= | -2,50,000.00 |
1 | $ 75,000.00 | 1/(1+0.12)^1= | 0.892857143 | 0.892857142857143*75000= | 66,964.29 |
2 | $ 75,000.00 | 1/(1+0.12)^2= | 0.797193878 | 0.79719387755102*75000= | 59,789.54 |
3 | $ 75,000.00 | 1/(1+0.12)^3= | 0.711780248 | 0.711780247813411*75000= | 53,383.52 |
4 | $ 75,000.00 | 1/(1+0.12)^4= | 0.635518078 | 0.635518078404831*75000= | 47,663.86 |
5 | $ 1,00,000.00 | 1/(1+0.12)^5= | 0.567426856 | 0.567426855718599*100000= | 56,742.69 |
NPV = Sum of all Discounted CF | 34,543.89 |
Calculator inputs would be: CF0 = -250000, CF1 = 75000 with frequency = 4 CF2 = 100000 frequency 1 then we compute the NPV at i = 12 or we can even input CF0 = -250000, CF1 = 75000, CF2 = 75000, CF3 = 75000, CF4=75000, CF5 = 100000 each with frequency 1
c) IRR is calculated for both using the financial calculator:
Project A: IRR is the rate at which the NPV = 0
Calculator inputs would be: CF0 = -100000, CF1 = 30000 with frequency = 4 CF2 = 50000 frequency 1 then we compute the IRR = 19.05% rounded to 2 decimal places or we can even input CF0 = -100000, CF1 = 30000 , CF2 = 30000, CF3 = 30000, CF4=30000, CF5 = 50000 each with frequency 1
Verifying scheduleis as follows:
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -1,00,000.00 | 1/(1+0.190458899148882)^0= | 1 | 1*-100000= | $ -1,00,000.00 |
1 | $ 30,000.00 | 1/(1+0.190458899148882)^1= | 0.840012201 | 0.840012200937764*30000= | $ 25,200.37 |
2 | $ 30,000.00 | 1/(1+0.190458899148882)^2= | 0.705620498 | 0.705620497724306*30000= | $ 21,168.61 |
3 | $ 30,000.00 | 1/(1+0.190458899148882)^3= | 0.592729827 | 0.592729827320195*30000= | $ 17,781.89 |
4 | $ 30,000.00 | 1/(1+0.190458899148882)^4= | 0.497900287 | 0.497900286808698*30000= | $ 14,937.01 |
5 | $ 50,000.00 | 1/(1+0.190458899148882)^5= | 0.418242316 | 0.418242315769718*50000= | $ 20,912.12 |
NPV = Sum of all Discounted CF | $ 0.00 |
Project B:
Calculator inputs would be: CF0 = -250000, CF1 = 75000 with frequency = 4 CF2 = 100000 frequency 1 then we compute the IRR = 17.23% rounded to 2 decimal places or we can even input CF0 = -250000, CF1 = 75000, CF2 = 75000, CF3 = 75000, CF4=75000, CF5 = 100000 each with frequency 1
Year | CF | Discount Factor | Discounted CF | ||
0 | $ -2,50,000.00 | 1/(1+0.172265141933036)^0= | 1 | 1*-250000= | -2,50,000.00 |
1 | $ 75,000.00 | 1/(1+0.172265141933036)^1= | 0.853049335 | 0.853049335196494*75000= | 63,978.70 |
2 | $ 75,000.00 | 1/(1+0.172265141933036)^2= | 0.727693168 | 0.727693168279181*75000= | 54,576.99 |
3 | $ 75,000.00 | 1/(1+0.172265141933036)^3= | 0.620758173 | 0.620758173427586*75000= | 46,556.86 |
4 | $ 75,000.00 | 1/(1+0.172265141933036)^4= | 0.529537347 | 0.529537347160192*75000= | 39,715.30 |
5 | $ 1,00,000.00 | 1/(1+0.172265141933036)^5= | 0.451721482 | 0.451721481956717*100000= | 45,172.15 |
NPV = Sum of all Discounted CF | 0.00 |
d) He should opt for project B as it has a higher NPV. IRR rule says the project with higher IRR should be selected but in case of conflict between NPV and IRR rules, NPV decision is superios and should be gone with.