Question

Homework help with excel

5. Consider the following projects with the following sequences of cash flows:

Period                                                  Net Cash Flow
n     A B C D
0 -$7,700    -$13,100 -$25,000 $23,100
1                             -$7,350 $8,500 $5,750     -$5,000
2                              $7,400 -$9,450     $8,750    -$6,700
3                              $7,500   $7,500        $6,850    -$8,900
         4                              $5,700   $8,800       $5,000    -$5,200
         5 $6,100         $8,850         $5,400         -$8,700

a)      Which of the projects represent simple investments?
b)      Which of the projects represent non-simple investments?
c)      Compute the i* for each project.
d)     Which of the projects has no rate of return? Why?

Answer 1

Cashflows provided are tabulated in Excel as follows:

n	A	B	C	D
0	-7700	-13100	-25000	-23100
1	-7350	8500	5750	-5000
2	7400	-9450	8750	-6700
3	7500	7500	6850	-8900
4	5700	8800	5000	-5200
5	6100	8850	5400	-8700

In Simple Investments initial cashflows are negative and sign change occur once while in non simple investments there are multiple reversal inn cashflows.

In A only one sign change occurs in year 2 hence it is a Simple Investment

In B multiple sign change occurs in year 1,2,3 and 4 hence it is a non simple investment

In C there is only one sign change in year 1 hence it is a simple investment

In D there is no change hence it doesnot fall under any category

Answer a

A & C are simple Investments

Answer b

B is a non simple investment

Answer c

I* is calculated in Excel using IRR function as shown in row 10. When we type the formula shown in row 10 in row 11 we get results as shown below:

2 -13100 23100 -7350 5750 7400 2 8750 8900 5700 5200 6100 8700 10 IRR(C3:C8) IRR(D3:D8) IRR(E3:E8) IRR(F3:F8) 19% 23% #NUM! 12 13

Hence IRR for Project A is 23%, B is 19%, C is 9% and Excel is unable to calculate IRR for D because all cashflows are negative.

Answer d

Project D has no rate of return because all cashflows are negative. For a rate return there has to be a series of negative and positive cashflows in a Project