XIRR and Dates

Hello

First post on here - quite happy to have found the site.

Anycase, I have the following problem: Calculating a monthly return from the following cashflows: In the first calc I use the end dates of the month and the second calc the start dates. Which is the correct one??

Market Values:
3/31/2015 337,833,316.28 4/1/2015 337,833,316.28
4/30/2015 -351,945,656.95 5/1/2015 -351,945,656.95

Cash flows for month:
4/16/2015 4,067,094.17 4/16/2015 4,067,094.17
4/17/2015 4,164,693.78 4/17/2015 4,164,693.78
4/20/2015 -459,636.00 4/20/2015 -459,636.00
4/29/2015 227,969.68 4/29/2015 227,969.68

Return using XIRR: 1.7905% 1.7891%


If anyone can shed some light on this it would be great

Liebschki

Originally Posted by Liebschki

Calculating a monthly return from the following cashflows: In the first calc I use the end dates of the month and the second calc the start dates. Which is the correct one?

I would use the closing values on the end dates (3/31 and 4/30).

Originally Posted by Liebschki

Return using XIRR: 1.7905% 1.7891%

I do not agree with your method of calculating the (effective) monthly IRR.

Apparently, your formula is: =(1+XIRR(values,dates))^(30/365) - 1.

I would use: =(1+XIRR(values,dates))^(1/12) - 1.

The point is: we want to consider each month to be an equal number of days. 30 is used when the year is consider to have 360 days. But Excel XIRR assumes 365 days per year. So a month is 365/12 days.

Great, thanks for the insight.

Another question is then raised: If I specify the date as the last day of the month as suggested, and no "time of day" is specified, what is the default value that excel uses? ie. does 3/31/2015 mean the stroke of midnight as we move from the 30th to the 31st, or the stroke of midnight as we move from the 31st to the 1st? (lol I hope the question is clear?)

Great thanks, you've been of great help.

Thank you very much indeed