Question
Please paste Excel spreadsheet into answer Project A has a cost of $500 and expected incremental cash flows are $100 per year for 7 years.
Please paste Excel spreadsheet into answer
Project A has a cost of $500 and expected incremental cash flows are $100 per year for 7 years. The cost of capital is 7%. Your organization has a maximum payback period of 6 years. a. What is the projects payback period? -Start by entering the data in the spreadsheet. Enter -500 in B3 and 100 in B4 through B10. - Next calculate the cumulative cash flow. In C3 write =B3 and in C4 write =C3+B4. Copy the equation down to C11. - Now, look for what year the project turns positive. It should be exactly year 5. b. What is the projects discounted payback period? -You need to work out discounted cash flows. In D3 enter the present value equation =1/(1+0.07)^A3. This equation will make a PVIF for each year at 7%. Drag the equation down the columns. Note that you can skip this step and the next step if you use the present value function in Excel instead (see Time Value of Money).
-Now make a discounted cash flow column. In E3 write =D3*B3. Drag the equation down to create discounted cash flows. -Next make a discounted cumulative cash flow column. Start by writing =E3 in F3. Then, in F4 write =F3+E4. Drag the equation down the cells. -Now look for where the project turns positive. It should turn positive after 6 years (6.4 to be exact).
What is the projects Net Present value? i. There are three ways to find this: 1. Look in cell F10 2. Sum E4 through E10 and subtract $500 3. In I8 write =B3+NPV(.07,B4:B10). This is the NPV formula, lets break it down: When you type =NPV you will get a dialogue box saying (rate,value1,[value2]...). For rate you put the discount rate. For value1 you can either select a single value or highlight a range of values. It is important to know that the equation assumes each payment happens at the end of the period. So, we could just highlight the initial payment and the subsequent cash flows, but the equation will assume that we are paying for the project after a year: it will apply our discount rate to that payment and it will bump all the other cash flows down one year. If you are working from the assumption that the project payment will be today, then you need to exclude the initial payment from your calculation. Instead, you will just add the payment (if it is a negative value, otherwise subtract it) to your npv. Compare the answer you got to this equation =NPV(.07,B3:B10). You should get something slightly different reflecting different present values. d. What is the projects Internal Rate of Return? i. In cell I9 write: =IRR(B3:B10). You should get 9.2% (check the formatting of the cells if you dont, a decimal point is important here). Note that you can check this value by inputting 9.2% into the NPV equation. It should come out to zero, or very close. e. Would you accept the project? Why? 2. Project B has a cost of $800 and expected incremental cash flows are $175 per year for 7 years. The cost of capital is 7%. Your organization has a maximum payback period of 6 years. a. What is the projects payback period? b. What is the projects discounted payback period? c. What is the projects Net Present value? d. What is the projects Internal Rate of Return? e. Would you accept the project? Why?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started