topic JMP internal rate of return function in Discussions
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29395#M19408
<P>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?</P>Sat, 19 Nov 2016 16:56:41 GMTdale_lehman2016-11-19T16:56:41ZJMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29395#M19408
<P>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?</P>Sat, 19 Nov 2016 16:56:41 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29395#M19408dale_lehman2016-11-19T16:56:41ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29397#M19409
<P>This is not an answer to your question but without the DVD in the formula the results look correct.</P>
<P>I thought it could be a formatting issue with the columns but changing them didn't solve the problem.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Untitled.png" style="width: 999px;"><img src="https://kvoqx44227.i.lithium.com/t5/image/serverpage/image-id/4094iA2EC2D8AFC1B61D9/image-size/large?v=1.0&px=999" title="Untitled.png" alt="Untitled.png" /></span></P>Sat, 19 Nov 2016 21:56:22 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29397#M19409ron_horne2016-11-19T21:56:22ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29399#M19410
<P>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.).</P>Sat, 19 Nov 2016 22:17:10 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29399#M19410dale_lehman2016-11-19T22:17:10ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29400#M19411
<P>I changed the second argument to a Sum Function, and it appeared to work.</P>
<P><BR />Internal rate of return(.1,-:real budget,Sum(:real boxoffice revenues,::real dvd revenues))</P>Sat, 19 Nov 2016 23:05:13 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29400#M19411txnelson2016-11-19T23:05:13ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29401#M19412
<P>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.</P>Sun, 20 Nov 2016 00:09:32 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29401#M19412dale_lehman2016-11-20T00:09:32ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29402#M19413
<P>I thing the time has come to go directly to JMP</P>
<P> support@jmp.com</P>Sun, 20 Nov 2016 02:55:00 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29402#M19413txnelson2016-11-20T02:55:00ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29407#M19414
<P>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, <EM>5</EM> instead of <EM>0.1</EM> works for most rows, but not all.</P>
<P>This formula seems to work for all rows in the sample table:</P>
<PRE><CODE class=" language-jsl">Internal Rate of Return(
(:real box office revenues + :real dvd earnings) / :real budget,
-:real budget,
:real box office revenues,
:real dvd earnings
);</CODE> </PRE>
<P> </P>Sun, 20 Nov 2016 22:35:41 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29407#M19414ms2016-11-20T22:35:41ZRe: JMP internal rate of return function
https://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29412#M19418
<P>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.</P><P>Thanks.</P>Mon, 21 Nov 2016 14:51:15 GMThttps://community.jmp.com/t5/Discussions/JMP-internal-rate-of-return-function/m-p/29412#M19418dale_lehman2016-11-21T14:51:15Z