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 project’s 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 project’s 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 project’s 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, let’s 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 project’s 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 don’t, 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 project’s payback period?
b. What is the project’s discounted payback period?
c. What is the project’s Net Present value?
d. What is the project’s Internal Rate of Return?
e. Would you accept the project? Why?