XIRR question

[AW]

i am running a trial of your the software. its very cool!   I have a function where i am calculating the IRR on a rolling basis on a long stream of cash flows. So I am calculation an IRR in every month, over 40 years. 

=IFERROR(IF(ABS(XIRR($K306:K306,$K$8:K$8))>1000%,-1,XIRR($K306:K306,$K$8:K$8)),-1)

It tells me that this is using up 89% of the sheet's calc time.  This is useful to know, but the tool doesn't tell me what to do about it. Is there a way to see suggested alternatives or solutions to speed it up?  or see what part of the formula is the issue?    

Thank you

[Charles Williams]

XIRR is an expensive function because it it uses an iterative method to find the answer. It will also tend to be slower the longer the time series you give it.

Some speedup alternatives are:

• wrap the first IRR in the formula in a FastExcel SETMEM function to store the XIRR result and replace the second XIRR call with a GETMEM
• If you have Excel 365  and the new LET function use LET instead of SETMEM and GETMEM
• Put the XIRR in a helper column and your IFERROR(...) formula in a column alongside that refers to the helper XIRR column
• Only do the XIRR every other month or less frequently

Time your alternative formula using the Calc Range button to compare the timings.

[AW]

Thank you.  More broadly, is there any functionality within the program itself to recommend improvements / alternatives to identified problems?  

[Charles Williams]

At the moment there is no within program functionality to suggest improvements. I could add some general how-to-optimise-formula-speed documentation: any other suggestions would be welcome.

[AW]

Thanks Charles. Perhaps a simple way would be if it detects a function like xirr, it would link to a section/page of that documentation.