Silicon Investor (SI) -- The First Internet Community

STOCKTALK

We've detected that you're using an ad content blocking browser plug-in or feature. Ads provide a critical source of revenue to the continued operation of Silicon Investor. We ask that you disable ad blocking while on Silicon Investor in the best interests of our community. If you are not using an ad blocker but are still receiving this message, make sure your browser's tracking protection is set to the 'standard' level.

Strategies & Market Trends : Gorilla and King Portfolio Candidates -- Ignore unavailable to you. Want to Upgrade?

To: Mike Buckley who wrote (19910)	3/12/2000 5:36:00 PM
From: gdichaz	Read Replies (1) \| Respond to of 54805

Mike: Beautiful. Applause. Cheers. Enough of that. To the thread: Mike knows that I have a short fuse and one which lights up when I think there is something wrong. I reacted to what I saw as a cheap shot at LindyBill by Tero. I have apologized to him and here now to the thread. I was trying to defend LindyBill against what I saw as an opportunist post by Tero. Sadly, divide and conquer is not just a Roman concept. I have had many many conversations with Tero over a span of around 5 years or so. He knows me, I know him. I resented his intrusion here, the timing, the way he did it. But Uncle Frank welcomed him. Done. Since we are around the time that I suggested to Mike that the Q had crossed the chasm, for those of you who bought the Q sooner rather than later, congratulations. (Turned out that I was wrong, the crossing actually took place a year earlier when I started my Telecom game with the Q at half) As I have suggested to Frank many times when he was wavering, the sale of the Q is a risk. Holding is less so. But even so I rose to LindyBill's defense. He of course, needs none, certainly not by me. That is finished. "Finnished", bad pun. All of course just IMO. Best. Cha2

To: Mike Buckley who wrote (19910)	3/12/2000 6:35:00 PM
From: Mike Buckley	Read Replies (2) \| Respond to of 54805

XIRR FORMULA -- PLEASE BOOKMARK THIS EVEN IF YOU DON'T WANT TO BOTHER WITH IT NOW You too can be a number cruncher! (I'm giving away a trade secret that few carpetologists know of. :) You don't need a financial calculator to determine Chaz's 18,000% annualized return. You can calculate it using your spreadsheet program's XIRR formula. I believe the same formula is used by all spreadsheet programs in the same way, but the only thing I know for a fact is that the exact details described below are pertinent to any Windoze version of Excel. Perhaps someone using a different program can confirm or deny this. WARNING: The details below appear at a glance to be very cumbersome. But that's only because I probably provided too much detail to explain the formula's use. It's really easy and intuitive as you set it up in your spreadsheet the very first time. SAMPLE TABLE OF VALUES Column C Column D 1/1/97 ($50,000)<----Initial Portfolio Value 4/10/97 $10,000 <----Cash Withdrawal from portfolio 8/16/97 ($2,000)<----Cash Addition to portfolio 10/5/97 ($8,000)<----Cash Addition to portfolio 3/12/00 $60,000 <----Current value of portfolio Annualized Return: 6.05% <----"XIRR" formula Assume the above table of dates and values appears with the dates in column C and the values in column D. Also assume they appear in rows 10 thru 14, with the annualized return calculated in row 15. THE XIRR FORMULA In that scenario, the XIRR formula must read as follows: =XIRR(D10:D14,C10:C14,0.1) To fully appreciate how that formula works, the first set of parameters (D10:D14) is the range of values. The values can be in any continuous range of cells on the spreadsheet but it is very important that the parameter of values within the formula be the first parameter. The second set of parameters (C10:C14) is the range of dates. Similar to the above situation, the dates can be in any continuous range of cells on the spreadsheet but it is very important that the parameter of dates within the formula be the second parameter. The third parameter (0.1) is a "guessed" value that will sometimes be required, sometimes not. It doesn't hurt to include the guess so it's best to always use it, ensuring that the formula does indeed return a calculated value in all cases. Just as the ranges of dates and values can be placed anywhere in the spreadsheet, the XIRR formula can also be placed anywhere. Similarly, when using a workbook, the ranges of dates, values and the formula can be in different spreadsheets so long as your formula's parameters are properly stated. For beginners, put everything all in the same spreadsheet! EXPLANATION OF POSITIVE AND NEGATIVE VALUES The "initial portfolio value" and the "current value" must be opposite values, meaning one must be a negative value and the other must be a positive value. I use negative values for the initial value and the value of subsequent cash additions to my portfolio. That's because I like to think of an investment as cash outflow that is flowing "out" of my pocket to the brokerage account. Just the opposite, I use positive values for the values of all withdrawals from my portfolio and the portfolio's current value. I find it easy to think of those values as cash inflow, into my pocket from the brokerage account. In that context, the current value of my portfolio is the amount of cash inflow I would enjoy if I decided to liquidate my portfolio and take the cash (not including commissions and taxes of course.) Feel free to use the positive and negative numbers as you wish. But be sure to use the same type of value for the initial value and all subsequent savings added to your portfolio. Similarly, use the opposite value for all withdrawals and the current value of your portfolio. Make sense? CALCULATING THE RETURN OF AN INDEX OR STOCK You can use the XIRR formula to determine the annualized return of any series of changing values. As an example, the formula calculates the annualized return of the S&P 500 from 1/1/97 thru 3/12/00 as follows: 1/1/97 (740.74) 3/12/00 1,395.07 Return: 21.92% TESTING YOUR USE OF THE FORMULA I suggest that you "test" your use of the formula by placing the sets of values shown above in your spreadsheet. Be certain to use the final date, 3/12/00, instead of the "today" formula. Compare your "return" values against those above to be sure you've set up everything properly. CONFIGURING EXCEL TO USE THE XIRR FORMULA Do not be surprised if you do not get a return value the first time! If you get something other than a numeric value in that cell, it is probably because you do not have your Excel program set up to use the XIRR formula. I've never had an Excel program automatically configured to use that formula, but it's very easy to configure it. In the Tools Menu, select "Add-ins." In the list of add-ins that appear, look for "Analysis ToolPak." (Don't confuse it with "Analysis TookPak - VBA.) Enable that toolpak. If that toolpak is not listed, you will need to install it using the Setup program that adds program files not yet installed on your computer's hard disk. (You'll need your Excel CD to do that.) Once you've used Excel's setup program to install that particular add-in, follow the instructions in the above paragraph. Depending on the version of Excel you are using, even if the toolpak is listed you still may need to install it. After you enable that toolpak, don't be surprised if instructions appear showing you how to easily install the required add-in files on your hard disk by accessing your Excel CD. Remember that the whole thing is MUCH easier than this missive would indicate. Have fun calculating Chaz's annualized returns, not to mention your own! --Mike Buckley