XNPV with negative discount rate

[Exceldummy]

Hi All,
I am new to Excel and need your help.
I am calculating quarterly, rolling yearly and year to date IRR for various long term investments. IRR for some of the quarters, rolling IRR is negative.
I am trying to use the XNPV function using the calculated IRR to check that the calculations are correct. However, when the discount rate (IRR) is negative, the formula returns a NUM! error. Uploaded is a simplified file of sample data.
I can build a data table with discount factors to check the correctness of IRR calculations.
My question: Is there a formula which I can use to check the calculations without building the discount factors or how can I use the XNPV function with negative discount rates.
Please help.

Thanks
Himanshu

 

[Faseeh]

Hi ExcelDummy,

I visited Amazon Books and found that there is some information available on limitations on XIRR and XNPV computations capabilities. The title of book is Financial Modeling by Simon Benninga. Link is below:

http://www.amazon.com/Financial-Modeling-Simon-Benninga/dp/0262027283

The author has described on page 44 the limitations and have mentioned a modified function NXNPV and NXIRR for the purpose. All these functions are copyright products, but i found a link on Chinese website that have documented them had have a declarative statement of "using it freely" Please see the below link:

http://www.docin.com/p-441934016.html

.. on page 5.

Hope that helps.

 

[Exceldummy]

Thanks Faseeh for your help.
I do not know any VBA programming or Macros, but you have tempted me to try and learn this. I will keep you posted if it works or does not work.