Your company received an investment proposal which requires an initial investmen
ID: 1171740 • Letter: Y
Question
Your company received an investment proposal which requires an initial investment of $500,000 now. The project will last for 7 years. You also have the following information about this project:
a) If you receive the above cash flows at the end of each year, calculate the NPV using both spreadsheet method and excel NPV function, and clearly mention if you will invest in the project or not based on your result of NPV.
b) Compute the IRR of the above project using the IRR function in excel.
Years 1 2 3 4 5 6 7 CF 100,000 120,000 130,000 140,000 100,000 100,000 50,000 Discount Rate 10% 10% 10% 10% 10% 10% 10%Explanation / Answer
CASH FLOW FROM THE PROJECT PVF of $ 1 = 10% Years Cash Flows PVF of $ 1 at 10% Present Value 0 -$5,00,000 1.00000 -$5,00,000.00 1 $1,00,000 0.90909 $90,909.09 2 $1,20,000 0.82645 $99,173.55 3 $1,30,000 0.75131 $97,670.92 4 $1,40,000 0.68301 $95,621.88 5 $1,00,000 0.62092 $62,092.13 6 $1,00,000 0.56447 $56,447.39 7 $50,000 0.51316 $25,657.91 Total $27,573 $27,572.88 NPV with Excel Function =NPV(E4,D8:D14) + D7 E4 = 10% D7 = - $ 500,000 D8:D14 = Cash inflows IRR with Excel = $0.1176 Formula =(IRR(D7:D14)) Simple use the foruma with irr function with all cash flows inclusive negative sign. IRR : IRR Means with a particular Percentage rate , At that point the present value become the zero CALCULATION OF THE IRR OF THE PROJECT First we calculate randomly present value with @ 11% discounting rate Years Cash Flows PVF @11% Present Value 0 -$5,00,000 1 -$5,00,000.00 1 $1,00,000 0.9009 $90,090.09 2 $1,20,000 0.8116 $97,394.69 3 $1,30,000 0.7312 $95,054.88 4 $1,40,000 0.6587 $92,222.34 5 $1,00,000 0.5935 $59,345.13 6 $1,00,000 0.5346 $53,464.08 7 $50,000 0.4817 $24,082.92 Net Present Value = $11,654.13 With PVF of 11% we are getting positive = $11,654.13 Secondly we calculate randomly present value @ 12 % discounting rate Years Cash Flows PVF @ 12% Present Value 0 -$5,00,000 1 -$5,00,000.00 1 $1,00,000 0.8929 $89,285.71 2 $1,20,000 0.7972 $95,663.27 3 $1,30,000 0.7118 $92,531.43 4 $1,40,000 0.6355 $88,972.53 5 $1,00,000 0.5674 $56,742.69 6 $1,00,000 0.5066 $50,663.11 7 $50,000 0.4523 $22,617.46 Net Present Value = -$3,523.80 With PVF of 12 % we are getting negative = -3,523.80 In the given case the pv with 11% is coming to postive means the present value is more then 11 % but with 12 % Present value cash flow become negative so the prese-nt value is between 11% and 12 % So the differecne in both % net present value is = $11,654.13 - -$3,523.80 Total is become = $15,177.93 So , the difference % = $11,654.13 "/"By $15,177.93 So , the difference % = 0.77 So, the IRR = 11.77% Answer 1 = IRR = 11.77%
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.