• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XIRR calculation to date and 6 month


New Member
Is it possible to calculate the Monthly TWR when we don't have the "portfolio value @ end of month" for every month, so, we don't get #DIV/0

Yes. But I will only offer a brief answer -- untested and very vague -- since I do not want to keep addressing an ever-changing set of requirements.

Eventually, an entirely different approach might be more appropriate.

This will be my last contribution.


At a minimum, I might insert a column that contains the number of months to the previous end of month.

(How the number of months is calculated -- if it is calculated, at all -- is TBD.)

For your examples, that might be 6 for June 30 and 5 for Dec 31.

Then I would reference that month count instead of -1 where I wrote EOMONTH(..., -1).

But that is just a bandaid.

I hasten to point out that the calculated TWR might be very inaccurate, the result of GIGO.


The most accurate way to calculate the TWR is to determine the ending balance on the same date as the cash flows.

Absent that same-day balance, we need an ending balance as close as possible to the cash flows.

The further the distance between cash flows and ending balance, the less accurate is the TWR.

In your original design, the compromise of monthly ending balances was marginally acceptable.

(Even then, I might be tempted to add deposits to the previous ending balance and add withdrawals to the current ending balance.)

For your latest example, the cash flows in Jan would be applied to the ending balance for June, more than 5 months later.

That is a very bad compromise, IMHO.