Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Globalization Gating And Risk Finance

Authors: Unurjargal Nyambuu, Charles S. Tapiero

1st Edition

1119252652, 978-1119252658

More Books

Students also viewed these Finance questions

Question

What role do ethics play in club marketing? AppendixLO1

Answered: 1 week ago

Question

2. Are my sources up to date?

Answered: 1 week ago