How to calculate IRR in Knime

I want to calculate IRR within Knime but I do not know how to install the R packages and how to script. I downloaded workflows from other Knime topics but they won’t work as I don’t have the packages installed.

Can someone help me with a video or something as this will make my life easier instead of exporting the data from Knime, calculating IRR in Excel and them loading the data into Knime again.

I can provide sample data if this would help.

Thank you.

Hi @JaySmith123 , I’m not familiar with IRR, but from what I’m reading about it, it’s some aggregation that you need to apply.

You can check the Moving Aggregation node

You may need to apply some calculation first, and this can be done via the Math Formula node

If you can explain how IRR is calculated, I am sure a lot of people can help translate the formula in Knime

It would also help if you have some sample data and also show what the expected results should be for the sample data

EDIT: There seems to be a few topics about it on the Forum. I saw the one that uses the R Script, but there’s also a component from @Residentstiefel

Also, it look like Python has libraries for IRR (via numpy), so that could be another alternative to R Script.

1 Like

Hi thanks for replying.

I think in the first instance it may help if I knew how to install the required packages. When I run a workflow with the IRR I am looking for it keeps returning this error.

image_2022-03-21_105348

Regards,
Jordan

Hello @JaySmith123
As @bruno29a commented, @Residentstiefel delivered a component that can do the job using only KNIME nodes (no scripting):

Here you have the link to the post, where it was mentioned:

I hope that you can find this helpful.
BR

1 Like

And here’s what I’d call “The Poor Man’s IRR Calculator” 20220321_IRR – KNIME Hub

1 Like

IRR Calculation Sample Data.knwf (6.3 KB)

Hi I have uploaded some sample data from my workflow along with the IRR calculated from Excel. Hope this helps with a solution.

Hi @gonhaddock thanks for the response. I’ve had a look at that same workflow and that only seems to output just one IRR whereas I need mine to output an IRR for each row in my data.

See attached
IRR Calculation Sample Data.knwf (6.3 KB)

I think I just need help understanding how to install the packages correctly and maybe write some code for it to work.

I’ve tried with my (painfully slow) Poor Man’s IRR Calculator and this is the result

Excel PMIC
0,186 0,186
0,197 0,197
0,208 0,208
0,17 0,171
0,175 0,176
0,17 0,170
0,094 0,095
0,094 0,094
0,135 0,135
0,046 0,046
0,134 0,133
0,045 0,046
0,033 0,032
0,179 0,179
0,08 0,079
0,102 0,102
0,119 0,119
0,114 0,114
0,08 0,079
0,076 0,075
1 Like

Thats perfect!. Thank you. Are you able to put that in a workflow and I can just find a way to incorporate it into mine please?

Here’s my (edited) workflow 20220321_IRR – KNIME Hub
Hope it helps, but I must warn you: it’s more of an exercise. It takes nearly 4 minutes to process your 20 rows You may probably speed it up.

Thanks for this much appreciated. I agree it does take some time considering I have to around 3000 rows to go through but it solves my problem for now until I can get this R package installed. Is there a way I can speed this up by any chance? If not, then thats fine

Unfortunately I have no idea at the moment. I’ll keep you posted if I can think of any good solutions.
I suggest you feed the workflow with many small files, just to limit the risk of malfunctioning.
I’m sure that the best solution would be a script in Python (I can write that) or in R.
“Best” in terms of speed, I mean

I’ll definitely watch out for that thanks.

@JaySmith123
Here they’re the links about how to integrate R and KNIME.

https://docs.knime.com/2021-12/r_installation_guide/index.html#_introduction

https://docs.knime.com/2021-12/r_installation_guide/index.html#r_installation

https://docs.knime.com/2021-12/r_installation_guide/index.html#r_packages_installation

Then you can install all your packages from your R CRAN console. Starting with your ‘Rserve’ as suggested in the Guide.

image

image

and in the same way, all the necessary packages (tibble is integrated in tidyverse):

install.packages('Rserve')
install.packages("rJava") 
install.packages("tidyverse") 
install.packages("dplyr") 
install.packages("jrvFinance")
...

BR

Hello @JaySmith123

From the picture that you are sharing, the workflow that you downloaded is an early prototype.

You can find the latest and debugged version at the following post in the forum. Some of the details on the accuracy are commented here. Unfortunately some version updates broke the related links, and they cannot be edited back.

BR

Thank you! I finally installed it and managed to get that sample IRR workflow to work on my machine. Now all I need to do is get help with how to actually create code to get the same outputs for my workflow

This comment was referred to the old discussion. In the shared post the links work as expected.

[…]

I will have a look into it. Meanwhile, you just have to transpose your data, as the time series are assumed to be a column in the workflow… and your data ‘Property Ref’ rows, are the projects id that are expected to be the column headers.

Then if your rows are as many as you mentioned, you will have to run it in tranches (!)

@JaySmith123

As you can see, no changes needed in the core calculation nodes, just the right input and collect the outputs.

20220322_R_IRR_apply.knwf (30.0 KB)

BR

3 Likes

All of you are amazing. I’ve finally implemented in my workflow and it worked in like 1 minute for 3500 rows of data. Thank you!

@gadjaoute @duristef

3 Likes