Is there any way to calculate the median or internal rate of return of a dataset?
There isn't a function to calculate the median.
I've done it in the past by following the steps in a script:
1. Find my set of records
2.5. sort from highest to lowest.
2. count the # of them
3. Find the middle value (or two middle values if the count is even, then find the average of those two (that's okay since it is just the two numbers)).
Jeremy Brown #mathskills
If You have FileMaker Pro Advanced, there are custom functions available, ie
FileMaker Custom Function:Median ( valueList )
You need to get the values into a list - via executeSQL or via list(TO::Your field)
Regarding the IRR, you might ask too much from FileMaker natively. IRR can be done but you'll have to write it and at least design a specific table to capture the Net Present Value at the frequency that fits your needs, Once you have NPV values, you will be able to find IRR. Moreover, it is important to note that IRR cannot be calculated analytically (as far as I know), it is trial on error or you'll have to come up with you own "logic".
Although FMP never ceases to amaze me how useful it is, yes, FMP is short of functions in general (string handling, dates, SQL functions, ....), but IRR is financial so I wouldn't expect that to be included.
Regarding the median and VERY basic descriptive statistics, I believe you'd be well served to spend a little time seting up a basic micro-service. Then, you can use freely available libraries, like those from Apache, with thousands (tens of thousands) of widely tested functions....which you can call from FMP with one line of code!
Basic Apache Commons descriptive statistics include:
Of course there are many others for hypothesis testing and related, including:
Covariance and correlation
Once you have that micro-service bridge from FMP, setting up a new function (method) is simple!
It's all up to you. FMP gives you the tools to do things however you want.
I usually opt for the micro-service approach since the libraries are written in C or at least compiled Java and run EXTREMELY fast.
Check out my two micro-service tutorials in the App Innovations Area:
Create Micro-Services Using Java and the Spark Java Framework
The Simplest Micro-Service! (Python and Flask)
True, IRR is usually used as a financial function, but in this case I want to use it to determine the positive or negative growth rate of a set of values over time. I will check out your suggestion.
Let's focus on getting results!
The free Apache POI (read the license) has not only Office capabilities, but it implements over 100 Excel functions, like IRR.
>>> DO NOT RE-INVENT THE WHEEL <<<<
So, in just ONE LINE of standard library code (not re-invented-the-wheel), you can compute the IRR for these payments: -7500., 3000, 5000, 1200, 4000
In FileMaker, IRR and median are beyond simple using only three lines of code in the micro-service (totally abstracted, hidden from the FMP developer). Thus with a single INSERT FROM URL command in FMP, passing a comma-separated list of payments and a 10% guess, we get the answer at right top below.
Note the same approach applied to get the median, where we can even specify the median percentile we want, to the sorted comma-separated list in the second example below.
(Many, many, many other descriptive, and more complex stat functions available the same way using a single INSERT FROM URL from FMP!)
(Hint: use ExecuteSQL to generate the comma-delimited list)
Total time to implement using free and standard Apache library: 10 minutes.
So, now, you can have one FMP client compute IRR, or hundreds, all for free.
You can also compute IRR right from Terminal, the browser, and any other program that can issue HTTP verbs.
Answer, of course, is the same:
Retrieving data ...