IRR calculation - 99999, whats going on?

[wonka1234]

Hi,

I am trying to calculate IRR.

Here are my numbers, I Add  Max_EIR and Sum_EIR into one so I can use the summarize IRR tool.

But I am expecting 

                                          0.002953

Does anyone know what I am doing wrong?

[IraWatt]

Hey @wonka1234,

Doing IRR on 4541291.81 in Excel gets an error, not sure how your getting 0.002953. Can you give an example file?

Thanks,

Ira

[wonka1234]

@IraWatt 

doing an IRR calculation on these:

(46,757,187)
1,085,437
1,070,874
1,056,464
1,042,244
1,028,152
1,014,197
1,000,439
986,826
973,378
960,072
946,943
933,949
921,091
908,391
895,824
883,426
871,132
858,941
846,953
835,089
823,374
811,781
800,345
789,029
777,831
766,776
755,834
745,043
734,347
723,737
713,308
702,986
692,798
682,714
672,770
662,934
653,199
643,591
634,082
624,705
615,418
606,199
597,143
588,179
579,335
570,582
561,951
553,416
544,969
536,636
528,387
520,256
512,212
504,236
496,388
488,618
480,957
473,372
465,896
458,507
451,192
443,979
436,839
429,802
422,844
415,924
409,138
402,419
395,797
389,240
382,780
376,398
370,077
363,850
357,683
351,610
345,611
339,606

[IraWatt]

@wonka1234 I put this into Excel and still got error:

Ive attached the workbook can you show how you got 0.002953

[RolandSchubert]

Hi @IraWatt , @wonka1234 

it seems to work in both Excel and Alteryx.

In Excel, a formula correction (include payment at the beginning of the time series)  solves the problem:

If needed,  increase number of iterations in Excel Options:

But: It also works in Alteryx (version 2022.1) using the Excel file @IraWatt created.

Best,

Roland

[wonka1234]

@RolandSchubert  do i need my values in a column in alteryx to get correct IRR? getting 99999. 

Im guessing I need a time series? 

I am taking the IRR of EIR Re-Calc

[RolandSchubert]

Hi @wonka1234 ,

both is true, you need a time series, but your values in one column, starting with a payment, subsequent rows contain the returns .

The values have to be ordered ascending by time, an additional assumption is, that there are constant time intervals. In general, the Excel file @IraWatt created, represents a suitable format.

Best,

Roland

[wonka1234]

@RolandSchubert 

Thanks.

So I have one column with the time series but I need to append the right columns value ontop of it to do a proper IRR.

Would you know how to do this?

[wonka1234]

@Qiu  Please help me Qiu!

[RolandSchubert]

Hi @wonka1234 ,

didn't receive community messages for a few days, so I wasn't aware the problem still exists ...

Currently, you have two columns, first containing the returns, second column seems to hold the initial payment. As one series is needed, you have to place the value from column 2 on top of column 1. I "split" the data into two streams and  use SELECT tools to keep only one of the fields in each stream. The payments repeats in each record, so I keep only the first entry (SAMPLE tool) and put this cell (one row with one column) on top of the returns time series using a UNION tool. You have to be careful with order  and field names, but this is possible using manual configuration in the UNION tool.

Last step is done by the SUMMARIZE tool, result seems to meet your expectation and the Excel result.

Let me know if it works for you.

Best,

Roland