Re: Irr

**bballman178** · 02-27-2014, 05:29 PM

Hello,
I am trying to calculate the IRR of a series of cash flows based on a certain formatting I have to use.
I have a series of capital calls running from G40:W40. The respective dates of these capital calls is one cell above each so G39:W39. My distributions run from Y40:AN40 and their respective dates are listed in Y39:AN39. My terminal value for now is shown in C32 (for the 3rd quarter). I want to run an IRR based off a date that I can input in C2, that will correspond to the respective terminal value. For example, if I input 9/30/2013 in C2, I want it to catch the terminal value based off different dates (in this example I talk about the 9/30/2013 terminal value that I have in C32). So I have a bunch of different historical terminal values and I want to be able to input the date in C2 and for it to catch the corresponding terminal value for that date as well as only those cash flows that are applicable (cash flows up to the specified terminal date). What is the formula for this? Also, I have all values positive right now (I understand capital calls should be negative). Is there a way to input in the formula a way to change the values for capital calls negative even though they are shown positive? Thanks for your help!

**bballman178** · 02-27-2014, 06:06 PM

Is this even possible in excel?

**alansidman** · 02-27-2014, 06:15 PM

This would be easier to comprehend if you attach a sample worksheet.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**bballman178** · 02-27-2014, 06:50 PM

Thank you alansidman. In the attached spreadsheet, I am trying to calculate the IRR based off the date inputed in C2. When I input the date, I want the IRR calc to grab that date's terminal value (in the attached spreadsheet, it would be C31 (the terminal value for the inputted date of 3/31/2010) and calculate the IRR based off the cash flows (capital calls and distributions) up to the inputted date (so in this example it would be the values of calls from F8 to L8 and their corresponding dates F7 to L7 as well as the distributions up to the inputted date which would be F12 for the value and F11 for the date since there is only one distribution up to 3/31/2010). Is this possible? Also, I have not inputted the capital calls as negative values (as I know you need to do to show capital outflows), but is there a way to reverse those values in the IRR calc to calc them as negative values? Thanks

**bballman178** · 02-28-2014, 11:36 AM

Anyone have an idea how to do this based on the above post and attached spreadsheet? Thanks

**bballman178** · 02-28-2014, 05:02 PM

Bump bump bump

**bballman178** · 03-03-2014, 11:16 AM

Sorry if I am being a rookie here but would anyone have any insight?

**shg** · 03-03-2014, 11:42 AM

You need to get the data arranged in contiguous fashion, appropriately signed, and use XIRR rather than IRR because the cash flows are at irregular intervals.