Ask Rande 10,000+
- Rande
- DellaO
- smile_1
- Rande
- Rande
- Kirk
- smile_1
- Thruhiker
- Rande
- smile_1
This archived discussion is "read only".
For the corresponding "live" discussions, post in the active topic forum here.
« Previous 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 Next »
» Rande - Re: Definiton of a Bull Market ?
In response to message posted by Slick:Slick,
If you accept the bear market definition of a drop of 20% from previous highs, then, by definition, a bull market would have to be a rise of 20% from previous lows. Perhaps a bettter definition of both would include the modifier "a prolonged period" where the market either rises or falls from previous levels by at least 20%. Maybe 20% isn't the best benchmark, but it's fairly well accepted. There's also the question of what defines "the market." Most would accept the S&P 500 as the benchmark, but in the last couple of years less than a dozen stocks in that index accounted for both half of the meteoric rise and, subsequently, the rapid fall. All semantics in the end.
-- posted by Rande
» smile_1 - Annualized Return
Rande et al,Warning to et al (if you do not enjoy number crunching exercises, this post may make you go into deep REM sleep). Rande I know you enjoy numbers so this warning does not apply to you.
I'm considering adding annualized return to my xcel spreadsheets. This has come up because I like the concept of tracking annualized return and my primary source for this data was the Fools portfolio tracker which was recently out sourced. It may be a while before the bugs are worked out.
Quicken on-line provides individual tier annualized return but is not capable of doing the calculation to concatenate the individual tiers.
If I add annualized return to my spreadsheets, I plan on using the following formula*:
Annualized Return = (Value Now / Original Value) ^ (1 /Years) - 1
2 Questions:
1) Is this formula as accurate as the IRR calculations typically used to calculate Annualized Return.
2) I'm using a weighted average approach based on # shares in each tier to get an overall view (see bottom of post, (Q) is this reasonable.
Rande, any insight you or anyone else can provide would be appreciated.
I do not plan on buying Quicken which will probably crunch the numbers for me and give me what I want.
Using a small IBM purchase I compared my results against Quicken.com portfolio tracker, the Fools portfolio tracker, and XIRR function xcel calculation.
I'm getting a small discrepancy, but it does not appear to be large enough to be concerned about.
example:
input data two tiers:
tier 1:
date bought 7/6/2000
current date 11/16/2001
shares purchased = 25
cost = $2,503.13
value @ 11/16/2001 = $2,862.5
tier 2:
date bought 10/25/2000
current date 11/16/2001
shares purchased = 25
cost = $2,196.88
value @ 11/16/2001 = $2,862.5
_________________________________
results:
tier 1
* annualized return formula = (2,862.5/2,503.13)^(1/1.361648746)-1 = 0.103541162 or 10.35%
quicken on-line shows: 10.31%
fools portfolio tracker shows: 10.27%
xcel XIRR function shows: 10.3324%
tier 2
* annualized return formula = (2,862.5/$2,196.88)^(1/1.060573477)-1 = 0.283439702 or 28.34%
quicken on-line shows: 28.27%
fools portfolio tracker shows: 28.1%
xcel XIRR function shows: 28.3532%
I'm thinking the reason for the slight discrepancies is the manner in which IRR is calculated for quicken & fools (use of iteration technique).
________________________
weighted average approach for overall view:
= (total of tier annualized return x tier shares) / total shares held = weighted average return
in the above IBM example the numbers are as follows:
(2.588529046+7.085992551)/50 = 0.193490432 or 19.34%
fools portfolio tracker shows overall IBM return = 19.19% for this example.
-- posted by smile_1
-- posted by Rande
» Rande - Re: Annualized Return
In response to message posted by smile_1:
smile,
The basic formula you're using is the caluclation for the annualized geometric mean. The geometric mean is good for measuring the change in wealth over more than one period, better than the arithmetic mean. A good example of how the arithmetic mean can be misleading is the classic example of $1 invested with a return of 50% in the first year followed by -50% in the second year. The arithmetic mean gives a misleading 0% annualized return [1/2(.50-.50) = 0] whereas the geometric mean gives the more realistic annualized -13.4% [(.75/1)^1/2 -1 = -.134]. The arithmetic mean is more suitable for representing typical performance over single periods and is the correct rate for forecasting, discounting and estimating the cost of capital (see Ibbotson SBBI).
If there were never any external cashflows, in or out, then the geometric mean calculation would be sufficient. IRR is good for calculating the rate of return where there are external cash inflows and outflows, as it takes into account the time value of money, so long as the cash flows occur at regular intervals. Where irregular cash flows (amount and timing) are involved (as is often the case in the real world where new cash might be added to the portfolio and/or cash withdrawn from the portfolio at periodic monthly intervals) the "Modified Dietz Method" used for calculating internal rate of return on a weighted monthly chain-linked basis is acceptable under AIMR standards. For personal spreadsheet purposes, what you're doing looks like it's more than adequate.
-- posted by Rande
» Kirk - Re: Annualized Return
In response to message posted by smile_1:Why not use the formula provided by Excel?
Compound Return=RATE((NOW()-M23)/365,,-I23,K23)
Now-M23 is today less the date I bought the security
365 turns it into years.
-I23 is present Value
K23 is future Value
I do this for all shares of Stock I bought in HWP where I had a company stock purchase every quarter.
You could probably average this return table according to percent of total invested each buy represents and multiply it by the return.
-- posted by Kirk
» smile_1 - Re: Annualized Return & XIRR (excel function)
In response to message posted by Rande:Thanks Rande.
One more observation, feel free to comment if you or others have used the XIRR calculation in excel, or if you like to solve stumpers.
For those who do not have access to the XIRR function in excel, you can add by doing the following:
go to the Tools menu, select, Add-Ins, and then check the box called Analysis ToolPak
This will add the XIRR function to your list of financial functions.
supposedly this function will take into account inflows and outflows of cash over time and provide annualized return.
for my IBM example as stated earlier, XIRR gave the following result:
tier 1: =XIRR({-2503.13,2862.51},{36713,37211},0.1) = 10.3324%
tier 2: =XIRR({-2196.88,2862.51},{36824,37211},0.1) = 28.3532%
problem is when I tried to concatenate the two tiers into an overall view, it appeared to give me a bad (different) number as follows:
=XIRR({-2503.13,2862.5,-2196.88,2862.5},{36713,37211,36824,37211},0.2)= 17.4876%
note: 36713 = serial date number for 7/6/00
37211 = serial date number for 11/16/01
36824 = serial date number for 10/25/00
if anyone knows why this result is different from the wtd average Annualized return I calculated and the fools view which I posted previously (19.34904% for my calculation & 19.19% for the Fools) please feel free to comment.
Call me curious.
ps. Kirk, I'll take a look at your suggestion of using Compound Return=RATE((NOW()-M23)/365,,-I23,K23) ....) thanks
-- posted by smile_1
» Rande - Re: Rande,
In response to message posted by Thruhiker:Thru,
Presumably, current interest income isn't an issue if CDs are being used. Also presumed is a risk-averse profile that has little tolerance for principal fluctuation so it could be a big mistake to go chasing yield if the result is sleepless nights and decisions that end up being driven by emotion if things don't work out. Might be best to just search for the best CD yield they can find so long as it's at a reputable institution with FDIC insurance and/or consider Treasury securities which are even safer and state tax free. I-Bonds might not be a bad choice, at least for the first $30K or $60K for couple.
-- posted by Rande
» smile_1 - Re: Annualized Return & XIRR (excel function)
In response to message posted by smile_1:Looking likely that the 17.4876% number calculated by XIRR (excel) for the overall annualized value in my IBM example is the correct annualized return.
score 1 for Bill Gates & Company.
only problem with using XIRR is it only accepts values & serial dates for the function argument, & not cell references. Boo Bill... Bummer man... I'll check to determine if there is a work around...
I checked my IBM example against 2 on-line calculators:
http://www.sironix.com/ratecalc.htm
http://tcalc.com/tvwww.dll?Invest
both came in at around the 17.5% annualized return range.
if I can't find a work around to using XIRR, guess I'll go with my geometric annualized formula & I'll need to weight my individual tier returns using cost instead of shares to bring the overall average more in line with reality.
I originally started with weighting the overall average return based on cost, but found the result different from the Fools portfolio tracker.
Foolish me.
switched to weighting the tier return based on shares (huh? desperate act is the only explanation, although somewhat justifiable when one thinks in terms of how averages are derived) to get it more in line with the annualized return from the Fools portfolio tracker.
I'm getting closer.
Thanks to all who responded.
-- posted by smile_1
« Previous 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 Next »
Please follow the guidelines set forth in the Suite101 Posting Etiquette when adding to the discussion.