msrdnr

Q: No XIRR function with today's update?

I am amazed that Apple did not extend this simple functionality to Numbers. Without it, Numbers cannot compete with Excel - and I cannot remove Office from my Mac. Very disappointing.

MacBook, Mac OS X (10.7.3)

Posted on Jul 25, 2012 5:43 PM

Close

Q: No XIRR function with today's update?

  • All replies
  • Helpful answers

first Previous Page 3 of 3
  • by msrdnr,

    msrdnr msrdnr Sep 23, 2016 7:14 AM in response to msrdnr
    Level 1 (4 points)
    Apple Music
    Sep 23, 2016 7:14 AM in response to msrdnr

    Four years later, still no XIRR function. Weird.

  • by Barry,

    Barry Barry Sep 23, 2016 10:59 AM in response to msrdnr
    Level 7 (32,697 points)
    iWork
    Sep 23, 2016 10:59 AM in response to msrdnr

    Or consistent.

  • by SGIII,

    SGIII SGIII Sep 23, 2016 11:25 AM in response to msrdnr
    Level 6 (10,796 points)
    Mac OS X
    Sep 23, 2016 11:25 AM in response to msrdnr

    msrdnr wrote:

     

    Four years later, still no XIRR function. Weird.

     

    Four years later, still posting in a stale thread. Weird.

     

    XIRR could be really convenient for a small subset of users of a "personal" spreadsheet like Numbers who want to conduct sophisticated financial analyses.  Meanwhile, simulating XIRR in Numbers is quite easy:

     

    Screen Shot 2016-09-23 at 2.06.08 PM.png

     

    List your dates and cash flow in a 'CF Summary' table just as you would do in Excel.

     

    Add a daily cash flow table (I've named mine "CF Daily') that lists each day from start date to end date.  In the amount column use:

     

       =SUMIF($Date,A2,$Amount)

     

    All this does is list the cash flows for each date.  Rows can be hidden as in my screenshot example.

     

    Then anywhere in your document you can calculate XIRR with this formula:

     

        =(IRR(CF Daily::$B,(1+'Guess (%)')^(1/Days in Year)−1)+1)^Days in Year−1

     

    With 'Use header names as labels' turned off in Numbers preferences the formula looks like this:

     

         =(IRR(CF Daily::$B,(1+Inputs::3:3)^(1/Inputs::4:4)−1)+1)^Inputs::4:4−1

     

    Not as concise as in Excel, but it does exactly the same thing.

     

    SG

  • by msrdnr,

    msrdnr msrdnr Sep 23, 2016 11:57 AM in response to msrdnr
    Level 1 (4 points)
    Apple Music
    Sep 23, 2016 11:57 AM in response to msrdnr

    Look at the other financial functions Apple has added to Numbers. All kinds of esoteric stuff. These are for a much smaller subset of people than would use the XIRR function. The XIRR function is used by anyone in the real estate industry, for example. Even the basic personal financial management software includes a version of XIRR! That's why it's so perplexing. By omitting this one function, Apple has consciously chosen to keep Numbers out of the hands of lots of people.

     

    It's not as frustrating anymore, because Microsoft has written a version of Excel for iOS that is absolutely fantastic. And Google's spreadsheet also includes XIRR. It's just strange.

     

    Yes, you can simulate XIRR in Numbers, but if you're talking about irregular cash flows over a period of, say, 10 years, it's very bulky. For that matter, you could simulate all the financial functions in Numbers, which just begs the question as to why Apple has chosen to leave this very popular and widely-used function out while putting all the others in.

  • by SGIII,

    SGIII SGIII Sep 23, 2016 12:36 PM in response to msrdnr
    Level 6 (10,796 points)
    Mac OS X
    Sep 23, 2016 12:36 PM in response to msrdnr

    If you're interested in getting some financial analysis done in Numbers using XIRR I've provided a way. It is not hard.  Nor is it really that "bulky".  Maybe you didn't actually try it?  You simply hide the rows you don't want to see; one click.

     

    If you're interested in commenting about Apple's decisions about tradeoffs (keeping the app lightweight and responsive to appeal to most users while also providing a lot of power under the hood) perhaps you should try Numbers > Provide Numbers Feedback in your menu.

     

    Meanwhile, I'm going to get work done now that I know how easy it is to do XIRR in Numbers.

     

    SG

     

    P.S. Agree Excel (both on Mac and iOS) is nice too.

first Previous Page 3 of 3