+ Reply to Thread
Results 1 to 5 of 5

XIRR - search range for first negative value

  1. #1
    Registered User
    Join Date
    05-29-2021
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    2

    XIRR - search range for first negative value

    Hello, I've searched this forum and the larger web and cannot seem to find a solution for my problem.

    I need to run an XIRR formula across multiple investors who contributed and received distributions from a project at different times during the life of the asset. The problem is that I can't figure out how to formulate the XIRR so that, for each investor, it looks at their individual cash flows and does not start calculating the returns until the first contribution date for each individual investor.

    Attached is an example. Would greatly appreciate any help!

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: XIRR - search range for first negative value

    One approach: array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into C2, then copy C2 in D2:G2.

    HTML Code: 
    See the attached file.

    Note: In more recent versions of Excel, it might not be necessary to press ctrl+shift+Enter.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: XIRR - search range for first negative value

    Try entering this formula in G2 using Ctrl Shift Enter.

    =XIRR(INDIRECT("G" & MATCH(TRUE,G7:G234<0,0) +6& ":G234"),INDIRECT("$B$" & MATCH(TRUE,G7:G234<0,0) +6& ":$B$234"),)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: XIRR - search range for first negative value

    [.... deleted by me ....]
    Last edited by joeu2004; 05-30-2021 at 02:04 AM.

  5. #5
    Registered User
    Join Date
    05-29-2021
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    2

    Re: XIRR - search range for first negative value

    Thanks, Joe! That did the trick - really appreciate the help!

+ 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: 11
    Last Post: 05-04-2021, 02:40 AM
  2. XIRR near 0 when should be negative
    By traylore333 in forum Excel General
    Replies: 5
    Last Post: 04-14-2020, 07:50 PM
  3. XIRR near 0 when should be negative
    By traylore333 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-30-2020, 05:22 PM
  4. Replies: 3
    Last Post: 10-21-2017, 04:03 AM
  5. XIRR issue with non negative beginning month values
    By distressedasstXL in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 10:26 PM
  6. XIRR and negative IRRs
    By dave in Toronto in forum Excel General
    Replies: 1
    Last Post: 03-28-2006, 12:20 AM
  7. [SOLVED] Can't calculate the result if all cash flow are negative in XIRR
    By Ellis Yu in forum Excel General
    Replies: 0
    Last Post: 02-21-2005, 06:06 AM

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