+ Reply to Thread
Results 1 to 10 of 10

XIRR with Non-Contiguous Values and Dates?

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    XIRR with Non-Contiguous Values and Dates?

    I have a table of investment returns, which contains chronological dates in one column, and amounts invested or withdrawn in an adjacent column. I want to use XIRR to calculate the overall annual return on the investment. The problem: The current account balance is in a separate cell, above the table of returns. (I should note that the "table" of returns is not an Excel table, but merely a range of cells.) For example, dates might be in A5:A30, amounts in cells B5:B30, and the current (remaining) account balance in cell B2.

    XIRR needs to reference the values and dates. How can I construct (within the formula) arrays of the values and dates, referring to my "table" of returns, but with the current account balance (and today's date) appended at the end of the table's values? Something like: =XIRR({B5:B30,B2},{A5:A30,TODAY()})... but curly brackets apparently don't work to create an array of values.

    Is there any way to create a reference (within a formula) to a range of non-contiguous cells?

  2. #2
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: XIRR with Non-Contiguous Values and Dates?

    Something like =XIRR(IF(ISNUMBER(B1:B14),B1:B14,0),IF(ISNUMBER(C1:C14),C1:C14,0)) works if the cells in between don't have numbers in?

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: XIRR with Non-Contiguous Values and Dates?

    Thanks, Nick, but all of the cells do contain numbers. I just need to know how to add the two non-contiguous values (current date and current account balance) to the ranges of dates and amounts.

  4. #4
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: XIRR with Non-Contiguous Values and Dates?

    Well the function needs a contiguous array as an input, so you need to create this somehow. One way is to select the full range you want and filter out the cells you don't want to include in some way. I guessed at a solution, but without seeing a sample workbook it's hard to be more precise.

  5. #5
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: XIRR with Non-Contiguous Values and Dates?

    So what you're saying is, it's not possible to create a single reference (within a formula) to an array of non-contiguous cells, correct?

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: XIRR with Non-Contiguous Values and Dates?

    If they help, here are some past attempts I am aware of to create array formulas that pull data into the XIRR() function from non-contiguous ranges:
    https://www.excelforum.com/excel-for...-function.html
    https://www.excelforum.com/excel-gen...t-columns.html
    https://www.excelforum.com/excel-for...h-vlookup.html

    I personally prefer to figure out how to get the data into a contiguous range, but most other people prefer complicated array formulas. Your call how to approach it, but something in those discussions may help come up with a formula that works for your scenario.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: XIRR with Non-Contiguous Values and Dates?

    Quote Originally Posted by Merf View Post
    So what you're saying is, it's not possible to create a single reference (within a formula) to an array of non-contiguous cells, correct?
    Not quite, I'm saying there's ways to do it but it depends on what is in the other cells and how they are arranged, so it's a lot easier to help you if you can provide a sample workbook.

  8. #8
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: XIRR with Non-Contiguous Values and Dates?

    Yes, I realize I can just add a copy of the current balance to the bottom of my table, and then use contiguous ranges. I was just hoping there was a (fairly simple) way to avoid that. Thanks...

  9. #9
    Forum Contributor John Vergara's Avatar
    Join Date
    01-17-2022
    Location
    Colombia
    MS-Off Ver
    365
    Posts
    200

    Re: XIRR with Non-Contiguous Values and Dates?

    Hi to all!

    One way to do it:
    PHP Code: 
    =XIRR(IF(ROW(B5:B31)=ROW(B31),B2,B5:B30),IF(ROW(A5:A31)=ROW(A31),TODAY(),A5:A30)) 
    This is an array formula. If you not have Microsoft 365 or Excel 2021, you must confirm with Ctrl + Shift + Enter instead of Enter. Blessings!

  10. #10
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: XIRR with Non-Contiguous Values and Dates?

    Thanks, John! Your solution does indeed work!
    Last edited by AliGW; 03-26-2022 at 10:15 AM. Reason: PLEASE don't quote unnecessarily!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-12-2021, 10:47 AM
  2. XIRR non-Contiguous Values
    By dumbaccountant in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2019, 01:20 AM
  3. XIRR for non contiguous arrays
    By Alexander_Golinsky in forum Excel General
    Replies: 3
    Last Post: 07-13-2012, 05:15 AM
  4. Replies: 11
    Last Post: 06-24-2010, 03:11 AM
  5. [SOLVED] XIRR non contiguous references
    By tloano in forum Excel General
    Replies: 2
    Last Post: 05-07-2006, 12:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1