Timeweighted Returns ... with Cash Flows

motivated by email from Mustaque A.
Once upon a time we talked about Time Weighted Returns and Dollar Weighted Returns. (See this.)
>Huh?
Don't you remember?
With TWR, you have a $100K portfolio and, after some time it's grown to $123K.
Your Gain Factor is 123/100 = 1.23 and if it took 1.45 years your annualized return
is 1.23^{1/1.45}  1 = 0.153 or 15.3%.
>Gain Factor?
Yes, it's what happens to $1.00. If $1.00 grows to $1.23, the Gain Factor is 1.23, got it?
>So $456.78 would grow to 1.23*456.78 = ... uh ...
To $561.84 ... so now you got it.
Okay, but TWR is what you'd see advertised by companies to promote their mutual fund.
It assumes there's a single dollar amount invested and no further cash flows either in or out.
For example, a $100K portfolio might grow (over 50 months) like Figure 1.
 The initial $100K grew to $141,800
 The 50month Gain Factor is 141,800/100,000 = 1.418
 The 1month Gain Factor would be 1.418^{1/50} = 1.0070
 The 12month Gain Factor would be 1.0070^{12} = 1.087
 The annualized TWR would be 8.7%
Note that, at 25 months, the portfolio was worth $130K.
The first 25month Gain Factor was 130,000/100,000 = 1.30
The second 25month Gain factor was 141,800/130,000 = 1.091
The total 50month Gain was 1.30*1.091 = 1.418
which we can get by multiplying the Gain Factors over each time interval.
 Figure 1 
>But we already knew the 50month Gain!
Yes, we did. But now let's invest another $20K at the 25month point. (See Figure 2.)
Now what's the TWR?
>Uh, I give up ... but I thought for TWR there shouldn't be any cash flows.
Pay attention. The gain over the first 25 months is still 1.30 and over the second 25months is ...
>It's 162,615/150,000 = 1.091, right?
Yes, so the total 50month Gain Factor is 1.30*1.091 = 1.418, as before.
>So the Time Weighted Return is the same?
Exactly!
 Figure 2 
>I take it there's a moral here, eh?
Yes, and that's that you can STILL calculate the TWR even when there are cash flows.
You just have to calculate the Gain Factors over each time interval, between cash flows, and multiply them together.
Remember.We're following a single $1.00 of the original portfolio over various time intervals (between cash flows).
When we find what happens to that $1.00 ... if it grew to $1.418 for example ... then we've got the TWR.
>So where's the spreadsheet?
Click on the picture:
You enter your Cash Flows, then (at the top) you type in your current Portfolio Balance along with the Date.
You click a Calculate button and get the Gain Factors between Cash Flows and the resultant (annualized) TWR.
You also get the XIRR or DollarWeighted Return.
Recall that the XIRR (or DollarWeighted) Return will depend upon when the Cash Flows occur.
>It'll measure how good a market timer you are, eh?
I reckon.
>Wait! When I look at the spreadsheert, it seems you are considering the times when the trades occur!
Uh ... it only seems that way because ...
>How about an example?
Okay.
 You start with 100 shares of a $12 stock. That's a $1200 portfolio.
 After 4 months the stock increases to $14. Your 100share portfolio is worth $1400.
The gain factor (or what happens to each dollar over this time period) is 1400/1200.
 At this 4month point you buy another 100 shares of the $14 stock.
That brings your 200share portfolio to 200*$14 = $2800.
 After another 8 months the stock is only worth $9.
Your 200share portfolio is now worth only 200*$9 = $1800.
The gain factor (or what happens to each dollar over this second time period) is 1800/2800.
 The TWR gain factor is then: (1400/1200) (1800/2800)
>See? You have considered the times when the trade occurs!
Aaah, but (1400/1200) (1800/2800) is the same as
(14/12) (9/14),
the total change in the stock, from $12 to $9.
>And that doesn't depend upon your trades, right?
Right ... and it doesn't depend upon when your trades occur. Got it?
>I think so ...
Just remember: if you buy shares to increase your portfolio from $100 to $110, it doesn't mean you just got an instantaneous 10% return.
However, if your $100 portfolio grew to $110 over umpteen months (withour further investments or withdrawals), then you did get a 10% return.
To calculate TWR you somehow need to eliminate the amounts and times of the trades.
To do this, you'd find the actual gains over each period, between trades, and use those numbers. Got it?
>I think so ...
