Question

Calculate the benefit-cost ratio, IRR, Annual Percentage Rate and Payback Period for the following project. Consider an inicial investment of 3,000,000 dollars:

Year	Income	Costs
1	200,000	140,000
2	200,000	140,000
3	200,000	140,000
4	200,000	140,000
5	200,000	140,000
6	200,000	140,000
7	200,000	140,000

An investor requires a 15% minimum acceptable rate of return. Is that possible?. Justify your answers.

Answer 1

А В C Е Present Value of Income Present Value of Cost Cost/1.15 year) 1 Year (Income/1.15Ayear) Income Cost 3,000,000 2 0 3,000,000 3 200,000 173,913 140,000 121,739 4 2 200,000 151,229 140,000 105,860 140,000 5 3 200,000 131,503 92,052 6 4 200,000 114,351 140,000 80,045 7 99,435 200,000 140,000 69,605 8 200,000 86,466 140,000 60,526 75,187 832,084 9 7 200,000 140,000 52,631 Total 10 3,582,459 11

Benefit -Cost Ratio= Present Value of Benefit/Present Value of Cost= 832084/3582459=0.2322

The Net Cash flow and IRR calculation is given below:

А В C D Cash flow 1 Year (Income-Cost) Income Cost (3,000,000) 3,000,000 2 0 3 1 200,000 140,000 60,000 200,000 60,000 2 140,000 5 3 200,000 140,000 60,000 200,000 6 4 140,000 60,000 200,000 140,000 60,000 7 8 200,000 140,000 60,000 9 7 200,000 140,000 60,000 IRR(D2 :D9) IRR 10 11

В C Cash flow 1 Year (Income-Cost) Income Cost (3,000,000) 60,000 2 0 3,000,000 200,000 140,000 1 4 2 200,000 140,000 60,000 200,000 60,000 140,000 200,000 140,000 60,000 60,000 7 5 200,000 140,000 8 6 200,000 140,000 60,000 200,000 140,000 9 7 60,000 -33.766% 10 IRR 11

Hence, IRR is -33.766%

To calculate annual rate: PV=-3000000, nper=7, PMT=60000, rate=?

G Н J К (3,000,000) PV 7 nper РMT 60000 =RATE(H2,H3,H1,0,0)| rate RATE (nper, pmt, pv, [fv], [type], [guess])

G Н (3,000,000) PV 7 nper РMT 60000 -33.77% rate

Hence, Annual rate is -33.77%

As, the initial investment is $3 million and total net profit we get from this project is 60k*7=$420K. Hence,we do not recover our money in the investment.

As, the investor require 15% accpetable rate of return and we get -33.77% return, we should not invest in this project.