XIRR to calculate portfolio return

[Pariah81]

Hi all,

Can someone please guide me to get an answer for XIRR. I know via IRR that the portfolio return should be 10.1%. I can't use IRR as in reality the dates are irregular but I want to know the answer as a check.

Or do I need to give up on XIRR and use another method?

Help much appreciated.

 

[ajetrumpet]

well since you are probably a financial professional, when you say:

I know via IRR that the portfolio return should be 10.1%

.....one would have no choice to believe you. but your sheet has dates on it, and the IRR() function does not have a date argument in it, per this webpage from microsoft. but XIRR() does have a date argument, again per the same website. why would you want to give up on XIRR() if is has the argument you're looking for in it?

 

[Pariah81]

well since you are probably a financial professional, when you say:.....one would have no choice to believe you. but your sheet has dates on it, and the IRR() function does not have a date argument in it, per this webpage from microsoft. but XIRR() does have a date argument, again per the same website. why would you want to give up on XIRR() if is has the argument you're looking for in it?

Thanks for taking the trouble to reply.

The IRR formula in excel informed me the portfolio return was 10.1%. The input/outputs are shown on far right of the attached pic in opening post. You can check my workings.

I know XIRR does need a date and IRR doesn't. In the attached I have purposely put dates at regular intervals so I can try try and equate XIRR and IRR. I know part of the difference is 360 day vs 365 day but the adjusted XIRR formulas found by searching around don't appear to help me.

 

[ajetrumpet]

well I thought I would offer you something to get you started. I'm not really sure what you're after because you're original question is not real clear to me. sorry about that. I thought you were after the difference in the functions, in that one requires a date array and the other one doesn't. aside from the, since I'm not a financial advisor, I don't really have anything else for you. I hope you get it solved though!

 

[joeu2004]

I would compare (1+IRR)^12-1 to XIRR, not (1+XIRR)^(1/12)-1.

The primary reason for differences, despite the "regular" frequency ("monthly") is: (1) the annualized IRR compounds monthly, whereas XIRR compounds daily; and (2) XIRR uses actual number of days, which varies from 28 to 31, whereas annualized IRR assumes equal periods.

Nonetheless, in your example, the max (unsigned) magnitude of the difference is less than 0.18%, and the (unsigned) magnitude of the relative error is less than 15/1000. That seems "close enough" to validate the XIRR calculation, IMHO.

I assume that the amounts are cash flows (deposits and withdrawals), not balances. And I assume that the ending balance is zero after the last cash flow in each column.

I prefer to use TWR (TWRR) instead of XIRR. IMHO, that gives a more realistic view of the performance of the porfolio and individual investments. It is also comparable to market indexes. And that is the default method that investment firms use to calculate "rate of return".

However, that does not require that we track more information; specifically, the beginning and ending balances on each date that a cash flow occurs. At least, that's the ideal. IMHO, it is sufficient to work with the beginning and ending monthly balances, which we usually have in brokerage statements.