turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- JMP internal rate of return function

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 19, 2016 8:56 AM
(3288 views)

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 20, 2016 2:33 PM
(6381 views)

Solution

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
);
```

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 19, 2016 1:29 PM
(3279 views)

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 19, 2016 2:17 PM
(3271 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 19, 2016 2:54 PM
(3258 views)

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

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 19, 2016 4:09 PM
(3245 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 19, 2016 6:55 PM
(3237 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 20, 2016 2:33 PM
(6382 views)

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
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 21, 2016 6:51 AM
(3171 views)

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.