XIRR with moveable start dates....

[MOXY1975]

I am trying to automate calculation of IRR for a range of scenarios.

I have a bunch of problems with this:

1. On a full cycle basis, the project start dates are not equal which means that in certain scenarios I get 0% return as the first cash flow is zero.
2. On a point forward basis, negative cash flows may start after T0 (i.e. now)

I have circumvented this on NPV basis by using a Sumproduct of individual cashflows, discount factors (full cycle cashflows are inflated to T0) and a proportional period factor (where T<T0, factor is 0)

How can I get the XIRR to start calculating from the first non-zero cashflow without having to manually move the start of the date and cashflow ranges?

I attach a example spreadsheet on following link:
https://drive.google.com/file/d/0BxEx2hFMDmEhZHktdEZNN2JlLUE/edit?usp=sharing

Problems occur in Scenario's 3 & 4:


-- removed inline image ---


Any assistance would be appreciated.

 

[MOXY1975]

I see that the post did not include the image:

re-attached and linked:
https://www.asuswebstorage.com/navigate/s/935C6A09C0FD49F5AE2C2A7136E7D2B84