periodic XIRR calc incorporating current value

[heski]

Hello,

I am trying to calculate the ongoing IRR off a share investment data set.

In the XIRR calc I am including the initial purchase of the shares, and any dividends receieved.

I would like to graph the cumulative IRR ON EACH DATE, based on the historic cash flows mentioned above plus the value of the share portfolio ON THAT DATE (to represent a theoretical sale of the shares on that date.)

I don't know how to make this calculation work, as I believe the XIRR calculation requires a fixed data source, and I don't want to include the historic value of the portfolio in that data source.

Hopefully that makes sense to someone out there who is able to help.

Thanks in advance to that person.

heski

[Peter1999]

Hi Heski,

I'm not quite sure I understand exactly what you need.
Please correct me if I'm wrong:
you have a dataset with dates, share prices and dividends, and you need to calculate the IRR at each date, representing a sale of the share, including the value of any dividends, right? The sale of the share is obvious; the dividends less so. Can you explain how your data is organized or post a sample of your data? Cheers,

Peter1999

[heski]

Peter,

Thanks for getting back to me. I have attached a sample of the data to help explain the issue. I refer to the attachment below.

Cell N83 shows the IRR of the cash flows from the purchase date (Jan 10 '07)to the sale date (Apr 30 '07).

I would like to show a daily IRR - showing the transition from 0% return immediately prior to purchasing the shares, to the 7% return realised on selling them. I want to this by using a 'theoretical sale value' on each particular date.

For example, cell Q79 shows what the theoretical IRR would be on Apr 25 '07. However to calculate this figure I needed to create a new set of data in column P.

I would like to be able to calculate IRR (as done in Q79) for each date without having to create a new column of data for each calculation.

I hope that makes sense.

Thanks again for your assistance.

[Peter1999]

Heski,

Thanks for the info. I see your problem now. I think you'd have to create a matrix to achieve what you want to replicate the cash flows in your model. You'll actually need two; one for the cash flows of selling the shares, and one for the dividends. Unfortunately, I won't have much more time until the weekend as I'm travelling, but hope I've helped you on your way. Good luck,

Peter1999