nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JMP internal rate of return function

dale_lehman

I have been trying to use the JMP internal rate of return (one of the Finance functions) function but have run into trouble. I can create a new data table and it works fine. But on the attached data (I am attaching a small subset of a larger file), it does not appear to work at all. I have the IRR as calculated by Excel (which has its own issues - but is working correctly here - note that the numbers will differ a bit from what you would get in excel since I assumed the dvd revenues were spread over two years). The last column is what JMP give me. The syntax for the formula is exactly what I used in my test on a new data table - which worked - but the numbers make not sense here. Clearly the $0 values are causing issues, but all of the calculations are negative when they should all be positive. And the values do not make sense. Any ideas?

sample.jmp

Accepted answers

It appears that JMP requires a better "guess" than the Excel counterpart. It seems to work fine with a guess closer to the "true" IRR. For example, 5 instead of 0.1 works for most rows, but not all.

This formula seems to work for all rows in the sample table:

 Internal Rate of Return(
    (:real box office revenues + :real dvd earnings) / :real budget,
    -:real budget,
    :real box office revenues,
    :real dvd earnings
);

All comments

ron_horne

This is not an answer to your question but without the DVD in the formula the results look correct.

I thought it could be a formatting issue with the columns but changing them didn't solve the problem.

dale_lehman

Changing the format to Best does appear to work - but only without the DVD column in the formula. With that column it still doesn't work. Strange for IRR to only work without currency formats but I still don't see why the DVD column causes problems (probably due to the zeros, but I don't see why that would matter - I assume the values are assumed to be cash flows at times t=1,2,3, etc.).

txnelson

I changed the second argument to a Sum Function, and it appeared to work.

Internal rate of return(.1,-:real budget,Sum(:real boxoffice revenues,::real dvd revenues))

dale_lehman

Sum appears to be fine for the syntax, but it does not give the correct answers. It essentially reduces a sequence of timed cash flows to a single flow one year in the future (indicated by the sum). I experimented with a few hypothetical simple cases and confirmed that Sum is not correct. I was also able to confirm that the IRR formula works with zero values. So, it is still a mystery as to why it is not working - the currency format appears to be the chief culprit, but it still does not work on the file I posted if you change the format and include the DVD column.

This formula seems to work for all rows in the sample table:

 Internal Rate of Return(
    (:real box office revenues + :real dvd earnings) / :real budget,
    -:real budget,
    :real box office revenues,
    :real dvd earnings
);

dale_lehman

This works perfectly - so the formatting is not an issue, but only the initial guess. I would add that the guess is also an issue in Excel - the IRR calculation is wildly off if some estimated internal rates of return are negative and some are positive. To get around that in Excel, you need to make the initial guess either +0.1 or -0.1, depending on whether it will be a negative or positive (you can guess that by using the sum of the cash flows - whether these are positive or negative). It does appear that JMP is more finicky - the guess has to be closer to the right value, not just the right sign. Your use of the revenues/cost works quite well.

Thanks.