IRR with first 2 years negative

Question on IRR FOLKS.

In the first 2 years of the cashflow i have negative numbers (the investment of 1000 is spread equally over 2 years, NOT in 1 shot in year zero as normal).

The following 3 years are all positive. Im trying to compute the IRR, and the NPV

a1 = year 1 (-500)
b1 = year 2 (-500)
c1 = year 3 (100)
d1 = year 4 (100)
e1 = year 5 (1500)


Q1. Is the IRR the correct function to use ? and is it "A1:E1" ?

Q2. is the npv computed also as "A1:E1" ?

thankyou in advance

im pretty sure this isnt correct because

1. in my actual example (not this simple example) i have an unleveraged irr of 12.71 % using discount rate of 12.54%. NPV is 224,000. 10 year c/f

2. when i add leverage of 50% LTV and loan cost of 8% pa. The IRR ONLY goes up from 12.71% to 13.76%. Same discount rate applied of 12.54% . NPV is 1,239,000 .

Any ideas folks ?

Originally Posted by alive555

a1 = year 1 (-500)
b1 = year 2 (-500)
c1 = year 3 (100)
d1 = year 4 (100)
e1 = year 5 (1500)
Q1. Is the IRR the correct function to use ? and is it "A1:E1" ?
Q2. is the npv computed also as "A1:E1" ?

For Q1: =IRR(A1:E1), which is about 17.3266242755233%.

For Q2: No. It is =A1+NPV(Q1,B1:E1), which is very close to zero (2.69665179075673E-10), where Q1 is the IRR formula above.

We cannot use NPV(Q1,A1:E1) because Excel NPV discounts the first term in the range. That is, Excel NPV would calculate A1/(1+Q1) + A2/(1+Q1)^2 + .... Instead, we want A1 + A2/(1+Q1) + A3/(1+Q1)^2 + ....

Originally Posted by alive555

1. in my actual example (not this simple example) i have an unleveraged irr of 12.71% using discount rate of 12.54%. NPV is 224,000. 10 year c/f

2. when i add leverage of 50% LTV and loan cost of 8% pa. The IRR ONLY goes up from 12.71% to 13.76%. Same discount rate applied of 12.54% . NPV is 1,239,000.

Without the actual cash flows, we cannot vet your numbers.

Perhaps the explanation above about how to use Excel NPV will resolve whatever contradiction you perceive.

If not, please provide the cash flow details for #1 and #2 of your second posting.

Here is the file

What i need to know is

1. with 2 years of negative cf after year zero can i use irr ? if so how ?
2. the after leverage IRR is way too low given the fact the cost of leverage is much lower then the return, ditto the NPV

somethings not right !

https://drive.google.com/file/d/0BxH...ew?usp=sharing

thanks in advance