I need help my financial homework ASAP

Question

A planned 5-year project in company ABC has an expected annual inflow of

20,000 $ in year 1

30,000 $ in year 2

28,000 $ in year 3

20,000 $ in year 4

8,000 $ in year 5

The project will require an initial start investment of 55,000$ and additionally for every year an annual investment in fixed costs of 7,000$. The required rate of return for investments of the company ABC is 12%.

a) Conduct a payback analysis for the project. What is the payback period of the project? What is the return rate of the project? Describe your way of calculation and your results in a transparent way.

b) Conduct a NPV analysis for the project. What is the NPV value for the project? What does this NPV value mean for the decision of the company ABC regarding this project proposal? Is the project likely to generate money or to loose money for the company ABC?

Answer 1

a) Payback Period and Return Rate

The initial cost is $55,000. To calculate the payback period, we need to list the cumulative cash inflow for each year. Before we can do that, we need to subtract the fixed costs from the annual cash flows.

Annual Adjusted Inflows

Year 1: ($20,000 - $7,000) = $13,000

Year 2: ($30,000 - $7,000) = $23,000

Year 3: ($28,000 - $7,000) = $21,000

Year 4: ($20,000 - $7,000) = $13,000

Year 5: ($8,000 - $7,000) = $1,000

Cumulative Inflows

Year 1: $13,000

Year 2: $36,000

Year 3: $57,000

Year 4: $70,000

Year 5: $71,000

We can see that the initial investment is paid back sometime during year 3 as its cumulative inflow is greater than $55,000. At the beginning of year 3, there is $19,000 ($55,000 - $36,000) of the initial cost left to pay back and $21,000 is generated over the entire year.

($19,000/$21,000) = .905

Thus, the payback period is 3.905 years.

To calculate the IRR (Internal Rate of Return), you can use a financial calculator or Excel via the IRR function as shown below:

Leaving cell A1 blank, apply the following labels to cells B1, C1, D1, E1, F1, and G1, respectively:

B1 - "Time 0"

C1 - "Year 1"

D1 - "Year 2"

E1 - "Year 3"

F1 - "Year 4"

G1 - "Year 5"

Label cell A2 as "Cash Flow"

Apply the following values to cells B2, C2, D2, E2, F2, and G2:

B2 - ($55,000)

C2 - $13,000

D2 - $23,000

E2 - $21,000

F2 - $13,000

G2 - $1,000

Next, in any empty cell, enter the formula =IRR(B2:G2). This will give the IRR, which is 10.91%. This is less than the company's required 12%, which means they will not pursue the project.

b) NPV Analysis

While the question lists the company's required rate of return (12%), it does not list the discount rate, which is necessary to calculate the NPV. Let's conduct the exercise for discount rates equal to 6%, 10%, and 12%. We'll use Excel in the same we did to calculate the IRR. After following the directions above, add another column labeling it "Discount Rate" in cell I1. In cells I2, I3, and I4, enter 6%, 10%, and 12%, respectively. Next, enter the formula =NPV(I2,B$2:G$2) and drag it down by two cells.

For a discount rate of 6%, the NPV is $6,047.69, for 10% it is $1,003.78, and for 12% it is ($1,143.62).

If the NPV is greater than 0 (and greater than competing project prospects), than the company will opt to pursue the project.