On 2002-04-29 05:08, kleinert wrote:
have an investment account from which I make varying annual withdrawals, and to which I
made varying annual contributions over the years. I would like to calculate my rate of
return over the life of the account, and during selected periods. What excel function should I use, or is there another formula I should use?
Thanks,
Jack Kleinert
Hi Jack,
Using Excel functions, go with XIRR, MIRR, or IRR. Be sure that you understand the differences between them, and pay careful consideration to non-normal cashflows (multiple sign changes) which give multiple IRRs.
For a nice extension of the above, the following thread gives the link to David Hager's XMIRR function.
http://groups.google.com/groups?hl=en&selm=1175d01c1aaed$cdd47af0$35ef2ecf@TKMSFTNGXA11
If you want interperiod returns, the Modified Dietz method can be used, and if you require a more sophisticated technique, use the BAI Iteration method (the one AIMR recommends for performance reporting).
Then, geometrically link the returns using GEOMEAN on the resulting array.
Example: Period returns in A1:A12, array enter
{=GEOMEAN(100+A1:A12)-100} or
{=GEOMEAN(1+A1:A12)-1}
depending on the way you structure the return results.
For info on the Modified Dietz and BAI Iteration, see my post at
http://groups.google.com/groups?hl=en&selm=078c01c18420$eff279f0$36ef2ecf@tkmsftngxa12
Check the entire thread for background info.
The IRR, XIRR, MIRR functions can be used in non-"since inception" periods by using the market value (switch the sign) as the beginning "cashflow" at the beginning of the period.
Finally, and most simply, you can use a total period return [(end-begin)/begin] - 1 and adjust by a CPI deflator, for instance, to get a "real" rate of return very simply. (I have an inflation indexed IRR file, but that is very, very involved just like the BAI technique is.)
This is a lot, so please let us know if this is helpful or if we can help further.
Bye,
Jay