IRR Calculation with KNIME

Helle everyone,

I try to calculate the IRR of projects with KNIME. I did this manually but it kills much performance.
Do you know an extension, node or workflow to calculate IRRs (similar to IKV formular in Excel).

Thanks a lot.
Tobi

Hello @TobiLanga , and welcome to the KNIME community

What do you mean by manually?

Are you R programmer? as it is an easy task to deploy in R with Scripting nodes.

Otherwise you can make a workflow to iterate over PV as a goal search (this may not be very efficient). But lets see if there arise some more ideas from other KNIME users’ experiences.

Regards

1 Like

Hello @gonhaddock ,

I set up an iterate (see Screenshot). But as you said this is not very efficient.
I have some knowledge in R but very limited (more visualization etc.) and I don’t know how to implement the R script into KNIME. (I know the Node R Snipped and the R function irr(), but now how to implement it into my workflow).

I had hoped that there is some extension or node to calculate this important figure.

Regards

1 Like

Hello @TobiLanga,

and welcome to KNIME Community!

Same was discussed here so take a look:

Br,
Ivan

1 Like

Thanks. I found it but I hoped that maybe during the last year a node for this was implemented :slight_smile:
Thanks for the help!

I see. No, there wasn’t. At least not that I’m aware of it.
Ivan

Hello @TobiLanga

I asked you about R programming to guess about if you have the R CRAN already installed… don’t forget to install “jrvFinance” R package

install.packages("jrvFinance")

Meanwhile someone develops an economics extension for KNIME. I’ve coded an R Snippet, that can handle multi project IRR calculation from Cash Flows.

20211027_IRR_calculation.knwf (13.5 KB)

I didn’t put to much attention on error handling, so consider it as a DRAFT

I hope you find it helpful. BR

3 Likes

Hello @TobiLanga ,
I’ve debugged the code and added a new functionality, this is ‘calculate NPVs at any desired number of rates’, based in ‘jrvFinance’ R package algorithm. The code is not sensitive to column names in this version.

I’m thinking to upgrade this job to a new workflow with the XNPV and XIRR functions, working with dates formatted in the input.

image

[…]
This is the shape of the new output:

[…]
Sorry for taking the chance of your post to test my brand new KNIME Hub space. Then, for any comment or bug related to the workflow, it can be captured in the hub space.

BR

3 Likes

Nice one @gonhaddock :+1:
Ivan

1 Like

Thanks man! You really helped me out with this one. I will check it out and give you feedback

2 Likes

Hi guys,

I was also missing the IRR calculation in KNIME and built a component solution with parameter optimization loop, as many don’t have setup their scripting environments within KNIME.
Feel free to check it out. Just click the component and add date columns + cashflow to calculate the IRR. Output is, as in excel, the IRR of a stream of cashflows.

Cheers,
Stiefel

2 Likes

Hi @Residentstiefel
First of all, thanks for share. I’ve checked your component and I have a couple of comments:

  1. The first is the name and labels in your workflow. You should remark that you are in fact applying a calculation for the XIRR function and not for the IRR ; as in ‘well known’ spreadsheets these functions are not transferible (neither in your component).
    The IRR operates for a regularly spaced cash flows and XIRR is for irregular spaced ones. Then the IRR doesn’t need a vector time as an input, and the handling of the t vector is different for the two calculations.

  2. The second observation is the precision for the estimation, as it is two decimals in fraction (equivalent to zero decimals of percent). This rounding of the output is not enough for any financial analysis and doesn’t allow to QC the results vs spreadsheet results.

Therefore you should in fact deliver two separate components for this function, and label them in relation to the performed estimates.

BR

2 Likes

Hi @gonhaddock,

Thanks for the feedback. Yes, indeed the component reflects the XIRR function in an Excel world. I chose it, as it is the general formula for calculating IRRs, which can be also applied to evenly distanced time series. I like the idea of having one component, rather than two components. Though, I thought about variabilizing the component even more, so it accepts non-date format timeseries where it applies Excels’ IRR function, rather than Excel’s XIRR.

About your second point: The number is just displayed with a decimal cutoff, however, the number has a very high decimal precision. The result can be further optimized by adjusting the step size in the parameter optimization loop (goes hand in hand with longer computation).

Best regards,
Stiefel

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.