Rate of Return with Summarized Data

[lfriesen]

I have data in a spreadsheet where part has a summary of the flows in and out with the period of time that applies. I am trying to calculate the rate of return but IRR expects the data in columns or that the amounts be the same for each period.

Option Amt_In Yrs_In Amt_Out Yrs_Out Value IRR
A 17000 10 15000 20 37000 4.53%

I can get the 4.53% if I turn the flows into a column with the appropriate number of occurences.

But, I have 20,000 lines so that isn't really a practical solution.

Does anyone have an option or variation on IRR that would work?

Thanks.

 

[Jose_Chasez]

I've revised my approach for this. Now I'm thinking it may be simplest to, via macro, create a temporary worksheet that will:

• map out the 20-, 30- and 40-year cash flows for each record
• calculate the IRRs
• paste the IRR values back into your data sheet
• move to the next record

I've got a working prototype of this together. Perhaps send me a private message with your email address and I'll send it off to you.

Hope this is helpful.

 

[Jose_Chasez]

Hi Lynn,

I've got a working spreadsheet for you. Is there an email address I could send it to?