Forum Stats

  • 3,875,138 Users
  • 2,266,810 Discussions
  • 7,912,093 Comments

Discussions

@CalcMgrExcelXIRR with @XRANGE

User_LAVQS
User_LAVQS Member Posts: 2 Green Ribbon

Hi,

I ma trying to implement excel's XIRR function using @CalcMgrExcelXIRR. WHen I run this function within the year (using XRange) it works fine. For Example:

@CalcMgrExcelXIRR(

@REMOVE(@XRANGE("FY21" -> "[YearTotal].[Jul]" -> "FinDev_DevIRR_TotalCF", "FY21" -> "[YearTotal].[Jun]" -> "FinDev_DevIRR_TotalCF"), "FY21" -> "BegBalance" -> "FinDev_DevIRR_TotalCF"), 

@REMOVE(@XRANGE("FY21" -> "[YearTotal].[Jul]" -> "FinDev_PeriodEndDate_Excel", "FY21" -> "[YearTotal].[Jun]" -> "FinDev_PeriodEndDate_Excel"), "FY21" -> "BegBalance" -> "FinDev_PeriodEndDate_Excel")

, 0.2

);


but it does not work and return 0 when I cross over the year in XRange function.


@CalcMgrExcelXIRR(

@REMOVE(

@REMOVE(@XRANGE("FY21" -> "[YearTotal].[Jul]" -> "FinDev_DevIRR_TotalCF", "FY22" -> "[YearTotal].[Jun]" -> "FinDev_DevIRR_TotalCF"), 

"FY21" -> "BegBalance" -> "FinDev_DevIRR_TotalCF" ), "FY22" -> "BegBalance" -> "FinDev_DevIRR_TotalCF"), 


@REMOVE(

@REMOVE(@XRANGE("FY21" -> "[YearTotal].[Jul]" -> "FinDev_PeriodEndDate_Excel", "FY22" -> "[YearTotal].[Jun]" -> "FinDev_PeriodEndDate_Excel"), 

"FY21" -> "BegBalance" -> "FinDev_PeriodEndDate_Excel"),"FY22" -> "BegBalance" -> "FinDev_PeriodEndDate_Excel")

, 0.2);


In 2nd code example, I removed "YearTotal" as well but no luck. I have tried 2nd code with only 1 Remove instead of 2 but still no luck.

Along with removing BegBalabce, I have also tried removing "YearTotal" (using @List) but still no luck.


Any help appreciated.


Thanks,

Sid

Tagged: