Is there any way to calculate the median or internal rate of return of a dataset?

Is there any way to calculate the median or internal rate of return of a dataset?

- 3 people found this helpful
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)).

- 5 people found this helpful
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)

- 3 people found this helpful
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".

- 4 people found this helpful
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:

- arithmetic and geometric means
- variance and standard deviation
- sum, product, log sum, sum of squared values
- minimum, maximum, median, and percentiles
- skewness and kurtosis
- first, second, third and fourth moments

Of course there are many others for hypothesis testing and related, including:## ALL FREE.

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**https://community.filemaker.com/docs/DOC-8871

**The Simplest Micro-Service! (Python and Flask)** - 1 person found this helpful
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.

Done.

---

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.**## Here's Terminal in Mac using the same exact service:

Answer, of course, is the same:

----

**Check out my two micro-service tutorials in the App Innovations Area:****Create Micro-Services Using Java and the Spark Java Framework**https://community.filemaker.com/docs/DOC-8871

**The Simplest Micro-Service! (Python and Flask)**

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)).