Time-weighted rate of return
Ivan Winters
investments, distributions, expenses and monthly market
values.
Based on this cash flow, I wish to calculate the monthly
time-weighted rate of return (TWR - se also
http://www.royalbank.com/rmf/pdf/edu/monthly_val.pdf).
Apparently Excel does not have this function.
Does anyone know where I can find a function for time-
weighted rate of return based on the cash flow described
above?
Ivan
Jay Petrulis
(sorry about the length of this!)
A bit of a misnomer here. The method shown in the article
is really an IRR variant. The time-weighting comes into
play when the results are linked geometrically.
The time-weighted rate of return is the "performance of a
unit of assets held continuously for the time period
measured." The IRR is the "return necessary to produce an
ending market value when applied to each cashflow in the
period measured."
The entire difference is how the cashflows are handled.
For the IRR they are everything, while the TWRR attempts
to negate the impact of the cashflows.
Suppose I had an portfolio which had a beginning market
value 100 on January 1 and an ending market value of 200
on December 31. The yearly return is 100%.
=IRR({-100,200})
or,
=XIRR({-100,200},{"1/1/01","1/1/02"})
Please note the day conventions used. XIRR works on end
of prev period -> end of current period, or beg current ->
beg next period. The same period can also be defined as
beginning of current period to end of current period.
The TWRR produces the same result. Technically, it should
have returns linked daily (or even continuously).
Suppose on day 1 the value of the portfolio is 105 and the
next time it was revalued was at the end of the year at
200.
Day Value Return
0 100 --
1 105 +5%
365 200 +90.4762%
Time weighting this example would give the return
(1+r1)*(1+r2) -1 = (1.05)*(1.904762) -1 = 1 = 100%
Carry this through daily for the whole year
(1+r1)(1+r2)(1+r3)....(1+rn) - 1 = TWRR
But what if there were external cashflows to the
portfolio?
Suppose I contributed 25 on June 15.
=XIRR({-100,-25,200},{"1/1/01","6/15/01","1/1/02"})
returns 66.9% for the year
For the TWRR, we need a bit more information. Suppose
that the June 14 end mv was 150, then...
Period 1 return (Jan 1 - Jun 14)
=150/100 -1 = 50%
Period 2 (June 15- Dec 31)
adjust beg mv for flow
beg mv = 150+25
end mv = 200
period return 200/175 - 1 = 14.2857%
Linked return (1.5)(1.142857)-1 = 0.714286 = 71.4%
This is the essence of the difference between the two
returns. No cashflows and the results are identical.
Be very clear on what constitutes an cashflow external to
the portfolio. If you sell a stock, the cash holdings
increase, but the market value of the stock drops an
equivalent amount. The net is zero.
Valid flows are fundings and disbursement items -- adding
more money after a paycheck, etc. (+) or paying management
fees and the like (-).
To revalue a portfolio before and after each flow is not
practical, so there are some conventions used. Basically,
the TWRR is done by linking sub-period IRRs.
Below is a copy of a google post of mine describing the
formulas used.
--------------
Both the Dietz and Modified Dietz methods are "back of the
envelope" calculations that can give a relatively quick
and reasonably accurate answer for the rate of return.
The conventions used in the formulas:
Vo = Beginning Market Value
Ve = Ending Market Value
C = cashflow, can be + (contribution) or - (withdrawal)
n = number of days in the period
d = day in which the flow occurred
r = rate of return for the period
Modified Dietz r =
(Ve - Vo - SUM(C))
--------------------
(Vo+SUM(weighted C))
or,
(End-Begin-Net flows)
---------------------
(Begin+Weighted flows)
Dietz r =
(Ve-SUM(weighted C))
-------------------- - 1
(Vo+SUM(weighted C))
or,
(End-Weighted flows)
--------------------- -1
(Begin+Weighted flows)
Of the two, the modified dietz give a far "truer" picture
of the "real" rate of return.
Investment firms are now almost fully taking direction
from the AIMR guidelines for performance and are using a
more accurate measure developed by the Bank Administration
Institute (BAI). It is an iterative formula called the
BAI Iteration method.
BAI Iteration
Vo(1+r) + Sum[(C)*(1+r)^((n-d)/n)] = Ve
This cannot be solved exactly, so you must use a iterative
algorithm solution:
r = Ve/Vo - [Sum(C)*(1+r)^((n-d)/n)]/Vo - 1
and solve until it converges to a single point.
I believe that if the Modified Dietz result is used as the
initial guess, it converges quickly. I leave it to others
to describe the mathematical concepts when it starts to
diverge. It was a pain to figure out in Excel how
to "kick" the rate back into a converging sequence.
This is the recommended formula to generate the rate of
return. Large flows (>10% of Vo is current threshold)
distort the return, so the recommendation is to revalue
the portfolio in the interim period and link the two (or
more) periods.
The differences between the IRR (XIRR) and the BAI
Iteration have to do with the treatment of flows
(contribution/withdrawals). The BAI method seeks to
eliminate their impact by explicily factoring them out of
the rate of return, to give the portfolio a fair picture
of how the investment(s) did for the period. The IRR is
the opposite, where the flows are vitally important.
The idea has to do with who has the discretion to move the
money. The typical money manager doesn't have discretion
of the timing and size of the cash flows -- the client
does -- so there has to be a way to measure performance
taking that into account.
Where the manager has discretion (venture capital, real
estate, etc.) then the IRR is often used.
Please note that this is a really simple explanation --
the academics can better describe the appropriateness of
each method.
One last thing. The BAI method is a very good
approximation of the 'real world.' Ideally, every time a
flow occurs, the portfolio is revalued at the time prior
to the flow, the flow is incorporated, and the period is
linked to the next one geometrically. This isn't
practical for the most part, so the BAI formula is used.
--------------------
Thanks,
Jay
>-----Original Message-----
>Ivan,
>I have never heard of TWR, and your link doesn't work
("Requested
>document could not be found")
>
>What's the formula? You may have to use Solver to get it,
but that's
>fine. Why don't you repost with what it's all about,
mathematically.
>
>Dave Braden
>
>
>In article <041001c183c5$caf73880$9be62ecf@tkmsftngxa03>,
Ivan Winters
>--
> E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
>.
>
David J. Braden
I have never heard of TWR, and your link doesn't work ("Requested
document could not be found")
What's the formula? You may have to use Solver to get it, but that's
fine. Why don't you repost with what it's all about, mathematically.
Dave Braden
In article <041001c183c5$caf73880$9be62ecf@tkmsftngxa03>, Ivan Winters
<n...@mail.com> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
Dick Kusleika
With this data in A1:C4
5/31/01 10831.52
6/10/01 300 11131.52
6/23/01 -200 10931.52
6/30/01 11261.67
This formula will give you the rate for June
=(C4-C1-SUM(B2:B4))/(C1+SUM((B2:B4)*(((A4-A1)-(A2:A4-A1))/(A4-A1))))
entered with control+shift+enter, not just enter.
HTH
Dick K.
David J. Braden <no...@ugotta.bekidding.com> wrote in message
news:131220011502238345%no...@ugotta.bekidding.com...
Curt Dye
do is follow their calculations. I did it by making column A = % of time
remaining, B = Present Value of fund before transaction and C =
deposits/withdraws. If you do it this way, you can calculate the overall
TWR without calculating it month to month as they show.
If your data is somehow different, then please elaborate.
Otherwise, TWR = (Final Value - Initial Value - Sum of Deposits & Withdraws)
/ Sum of ( A * (B+C)) where A, B, and C are the data points.
"David J. Braden" <no...@ugotta.bekidding.com> wrote in message
news:131220011502238345%no...@ugotta.bekidding.com...