In: Finance
You have just landed your dream summer internship, and your boss asks you to analyze a project that has an investment cost of 3,500,000, to be paid today (t = 0), and will generate a cash-flow of 350,000 in the first year (t = 1). The cash-flow will then grow at 12% per year for the next eight years (the last time the cash-flow grows at 12% is from t = 8 to t = 9). Afterwards, as competition increases, cash-flow growth is expected to be only 4% per year in perpetuity. (The cash-flow grows at 4% from t = 9 to t = 10 and forever thereafter.) The discount rate is 18%.
a. (3 points) Start by drawing the timeline, showing the cash flows at t = 0, 1, 2, 3, 8, 9, 10, 11 (the amount and how you calculated it).
b. (10 points) What is the NPV of this project? Please show how to do by hand and excel ( i have got totally different numbers so please show both)
c. (3 points) Do you recommend accepting or rejecting the project? Why? (Three sentences at most.)
d. (4 points) Write down the equation for the IRR of this project. (You don’t have to solve for the IRR, just write the equation whose solution is the IRR.) Please show how to do by hand
cash flow in year 1 = 350,000
growth rate = 12% for next 8 years
cash flow of next year = cash flow of previous year(1 + growth rate)
so, cash flow in year 2 = 350000(1 + 12%) = 392,000
cash flow in year 3 = 392000(1 + 12%) = 439040
cash flow in year 4 = 439040(1 + 12%) = 491724.80
cash flow in year 5 = 491724.80(1 + 12%) = 550731.78
cash flow in year 6 = 550731.78(1 + 12%) = 616819.59
cash flow in year 7 = 616819.59(1 + 12%) = 690837.94
cash flow in year 8 = 690837.94(1 + 12%) = 773738.49
cash flow in year 9 = 773738.49(1 + 12%) = 866587.11
cash flow in year 10 = 866587.11 (1 + 4%) = 901250.60
cash flow in year 11 = 901250.60 (1 + 4% ) = 937300.62
timeline:
b)
continuous value of perpetuity = cash flow in year 9(1 +4%) / K - g
where K = discount rate = 18%
g = growth rate = 4%
= 866587.11(1.04) /18% - 4%
= 6,437,504.26
now we have to find out present values of above cash flows using discount rate of 18%
present value = cash flow / (1 + discount rate)^n
where n = year of cash flow
present values of future cash flows:
= [350,000 / 1.18] +[392000 / (1.18)^2] + [439040 / (1.18)^3] + [491724.80 / (1.18)^4] + [550731.78 / (1.18)^5] + [616819.59 / (1.18)^6] + [ 690837.94 / (1.18)^7] + [ 773738.49 / (1.18)^8] + [ 866587.11 / (1.18)^9] +
[6437504.26 / (1.18)^9]
= 3,637,664.34
NPV = present value of future cash flows - initial cash outflow
= 3,637,664.34 - 3,500,000
= 137,664.34 (rounded to two decimals)
npv using excel:
c)
it is recommended to accept the project because it has positive NPV.
d)
IRR is the rate where NPV of the project is 0
so , 0 = -3500000 +[350,000 / (1 + IRR)] +[392000 / (1 + IRR)^2] +[439040 / (1 + IRR)^3] +[491724.80 / (1 + IRR)^4]
+[550731.78 / (1 + IRR)^5] + [616819.59 / (1 + IRR)^6] + [ 690837.94 / (1 + IRR)^7] +
[ 773738.49 / (1 + IRR)^8] + [ 866587.11 / (1 + IRR)^9] + [6437504.26 / (1+IRR)^9]