Help with XIRR formula

[#1]

Hi Folks

I'm having trouble making an XIRR function work, I have the following formula:

=XIRR (
 ( { $ < DT_TransDate = { '<=$(=Only(DT_ReportMonthEnd))' } > } _rCashflow_Drawdown + _rCashflow_Distribution )
 +
 ( { $ < DT_TransDate = {'$(=Only(DT_ReportMonthEnd))' } > } _rCashflow_FMV )
 , MonthEnd ( DT_CashflowDate )
 )

For information, the individual formulas for drawdown+distribution and for FMV work as expected, giving the correct values against the correct dates (FMV Being only against the last month end). But when I try the above in an XIRR expression, it will always return NULL.

I couldn't find anything on the forum related to XIRR so I'm not sure if anyone else out there is even using this function.

Any help appreciated as usual,

[stantrolav]

You have mistake in set analysis/ When you use range of values you should use double quote mark " and your formula would look like:

=XIRR (
( { $ < DT_TransDate = { "<=$(=Only(DT_ReportMonthEnd))" } > } _rCashflow_Drawdown + _rCashflow_Distribution )
+
( { $ < DT_TransDate = {"$(=Only(DT_ReportMonthEnd))'" } > } _rCashflow_FMV )
, MonthEnd ( DT_CashflowDate )
)

What is this? Why this is a part of your formula? You cant use it like that...

=XIRR ( ( { $ < DT_TransDate = { ' } _rCashflow_Drawdown + _rCashflow_Distribution ) + ( { $ < DT_TransDate = {"$(=Only(DT_ReportMonthEnd)" } > } _rCashflow_FMV ) , MonthEnd ( DT_CashflowDate ) )

Delete it.