Imported Spreadsheets to Zoho

I mentioned in another post that I started using Zoho recently. Zoho offers a suite of "office" software online. They have online word processor, spreadsheet, presentation and many other types of software that traditionally resides on a local computer. Having these software online lets me access my documents from anywhere. It also lets me share my documents with the world without requiring Microsoft Excel. My experience so far has been very good. I have created a few Excel spreadsheets and linked to them on this blog in the past. Zoho's spreadsheet program correctly imported them without glitch. Here they are if you'd like to use or bookmark them:

ESPP Rate of Return - Calculates the annualized return from Employee Stock Purchase Plan (ESPP) purchase and sale. See previous post Employee Stock Purchase Plan (ESPP) Is A Fantastic Deal.

TIPS Pricing - Estimates how much cash you will need for buying TIPS at auction. See previous post TIPS: Inflation Linked Bonds.

A Tale of Two Charts

The S&P/Case-Shiller Home Price Indices came out for February 2008. They showed a year-over-year decline for most cities. The announcement from Standard & Poor's came with the following chart (click on it for a larger size).

The plunge is quite impressive, isn't it? Now look at this second chart.

What do you see? A long rise followed by a small drop. The two charts are actually based on the same underlying data for the same time period. This is another case of when charts lie. The first chart from S&P plots the year-over-year growth *rate*, not the price index itself. I created the second chart from the 10-city composite data from S&P. It plots the 10-city composite price index. Before home prices reached a top in June 2006, the year-over-year growth rate slowed, but the prices were still growing. They were just growing more slowly than before. Lately the prices also dropped. That's for sure. However the growth rate nose-dived much more than the price index itself. If you look at the first chart, you would think the housing market is so bleak. If you look at the second chart, things are not so bad after all. Even with the price drop, the price index is still higher than it was before late 2004. In 20 months since June 2006, it merely gave up the gains of the previous 19 months. The index remains substantially higher than the trendline (red) established before the growth took off in 2000.

You can make charts tell whatever story you want. It depends on your perspective. Extending the decline in the second chart back to the trendline will be interesting.

RSU Sell To Cover Deconstructed

Ever since I wrote Restricted Stock Units (RSU) Sales and Tax Reporting, I received many questions. They all relate to sell-to-cover, which is the default, and often the only option people have for their restricted stock units (RSU). I must have not been crystal clear in my previous post. Otherwise I would not have received so many questions. I thought of a better way to explain it. So hopefully it is clear this time. For background on RSUs and tax withholding, please also read my previous post Restricted Stock Units (RSU) Tax Withholding Choices.

Let's use this hypothetical example.

100 RSUs vested on 4/20/2007. The closing price on the vesting date is $50 per share. The company sold 40 shares for taxes. You received 60 shares. Without the RSUs, your W-2 income for the year would've been $60,000, with $8,000 withheld for various taxes (federal, state, social security, medicare).

This transaction can be deconstructed into 5 steps as follows.

1. The company gives you a cash bonus. In our example, the bonus is $5,000, which is the closing price on the vesting date ($50) times the number of RSUs vested (100). The company adds this cash bonus to your W-2. If your W-2 income without the RSUs is $60,000, your W-2 income with RSUs now becomes $65,000. After the end of the year, they will issue you a W-2 showing $65,000 in box 1.

2. You use the cash bonus to buy shares. $5,000 bonus buys 100 shares at $50 a share. Buying shares by itself does not trigger any taxes. Your cost basis in these 100 shares is $50 a share, for a total of $5,000.

3. The company sells some shares on your behalf. In our example, they sell 40 shares on your behalf. You must report sales of stocks on Form 1040 Schedule D. There can be a few variations here.

3a. The company does not use a broker. The shares are sold on the vesting date at the same closing price. You report on your Schedule D:

Description	40 Shares XYZ Corp.
Date Acquired	4/20/2007
Date Sold	4/20/2007
Sales Price	$2,000
Cost Basis	$2,000
Gain or Loss	$0

3b. The company uses a broker. The shares are sold on the next day after vesting at a different price. Suppose the sale price is $50.60 and the broker's commission is $20. The net proceeds of the sale is $50.60 * 40 - $20 = $2,004. You report on your Schedule D:

Description	40 Shares XYZ Corp.
Date Acquired	4/20/2007
Date Sold	4/21/2007
Sales Price	$2,004
Cost Basis	$2,000
Gain or Loss	$4

If the shares are sold at a lower price, you show a loss instead of a gain. The loss can offset capital gains elsewhere. After that, it can offset up to $3,000 of your ordinary income. If you still have more losses, the remainder is carried over to the next year, offsetting any gains you have next year and up to $3,000 of your ordinary income again next year.

4. You hand over the money from the stock sale to your employer. Your employer remits the money to the federal and state tax authorities. They add the taxes paid to the withholding numbers on your W-2. If your tax withholdings without RSUs would've been $8,000, your tax withholdings with RSUs now become $10,000. After the year end, the W-2 you receive from your employer shows $65,000 of income (step 1) and $10,000 in withholdings.

5. Finally, your employer gives you the remaining shares. You bought 100 shares in step 2. They sold 40 shares on your behalf in step 3. You have 60 shares left.

Now, when you file your tax return,

• Enter the income and taxes paid from your W-2 as-is. The RSU related income and tax withholdings are already included on your W-2. You don't have to do anything else with them. Do not add more income. Do not add more taxes paid.
• Report the stock sale on Schedule D as shown in step 3. If the company does not use a broker and sells the shares at the same price as the closing price on the vesting date, you should have a zero gain/loss for that sale. Others might have a small gain or loss depending on the sale price and brokerage commission if any.

Your cost basis in the remaining shares stays at $50 a share. In our example it's $50 * 60 = $3,000 in total. Whenever you sell these shares, you have to remember this cost basis. If you sell them for more than $50 a share, you have a capital gain. If you sell them for less than $50 a share, you have a capital loss. You will report the gain or loss in the year you sell these remaining shares. The gain/loss will be a short-term gain/loss or a long-term gain/loss depending on your holding period after the vesting date.

I hope this post addresses all the questions. If you break up the RSU vesting and sale this way, it's not that complicated.

Related Posts:

• Restricted Stock Units (RSU) Sales and Tax Reporting
• Restricted Stock Units (RSU) Tax Withholding Choices
• Employee Stock Purchase Plan (ESPP) Is A Fantastic Deal

Mortgage Interest and Property Tax Deduction for Homeowners Who Don't Itemize

The New York Times reported that Senate Democrats and Republicans reached a tentative deal on the new housing bill. Among the various provisions is a federal income tax deduction for property tax paid by taxpayers who don't itemize deductions. Single taxpayers get a $500 deduction. Married taxpayers filing a joint return get $1,000. Presidential candidate senator Barack Obama also proposed a "universal mortgage credit" which gives a refundable tax credit to taxpayers who pay mortgage interest but don't itemize deductions.

The rationale behind these proposals is that the mortgage interest deduction and the property tax deduction benefit only the well-off. They say people who don't itemize their deductions don't get those deductions. From Obama's Tax Fairness Plan:

"Owning a home is the culmination of the American dream that so many Americans work so hard for. The tax code is supposed to encourage home ownership with a mortgage interest deduction, but it goes only to people who itemize their tax deductions. Like so much in our tax code, this tilts the scales toward the well-off. The current mortgage interest deduction excludes nearly two-thirds of Americans who do not itemize their taxes."

Is that so? On the surface, yes. If you don't itemize your deductions, you use the standard deduction, which in 2008 is $5,450 for single and $10,900 for married filing jointly. If you pay mortgage interest and/or property tax, but if they are not large enough, you still use the standard deduction. That's why by definition Americans who don't itemize their deductions don't show a mortgage interest deduction on their tax return.

However, to say that those Americans don't benefit from the mortgage interest deduction or the property tax deduction is a misunderstanding of how taxes and math work. The tax law says everybody is allowed to itemize their deductions. Everybody starts out listing their mortgage interest, property tax, state income tax, plus any other deductions they are allowed. Say for a married couple filing jointly, those deductions add up to $6,000, then the IRS tells them

"Guess what, you are lucky. We are going to let you deduct even more than what you've already got here. Would you like us to top off your deductions to $10,900?"

Now they can take up on the offer from the IRS or say "no thanks" and stick to their original list of deductions, which include their mortgage interest, property tax, state income tax, and everything else. In reality, when one has less in deductions than the standard deduction, nobody declines the sweet offer from the IRS because they get to deduct all the deductions they are allowed, plus a bonus deduction offered by the IRS.

Now tell me who's better off? The taxpayers who don't itemize their deductions but end up deducting even more than their deductions, or the taxpayers who itemize their deductions? The non-itemizers get to deduct everything they are allowed plus a bonus deduction they receive from the IRS. Itemizers don't receive such bonus. The non-itemizers are already better off than the itemizers. If we allow a new property tax deduction under the proposed housing legislation or a new "universal mortgage credit" under Obama's tax plan, the non-itemizers will deduct their mortgage interest and property tax twice, plus taking a bonus deduction from the IRS. Does that sound like fair to you?

I'm afraid our legislators and presidential candidates don't understand how taxes and math work because they don't do their own taxes. 

Restricted Stock Units (RSU) Tax Withholding Choices

Ever since the companies are required to expense employee stock options, more companies started to grant the employees Restricted Stock Units (RSUs) instead of stock options. The first batch of RSUs I received will vest shortly. Unlike non-qualified stock options which are taxed at the time of option exercise, RSUs are taxed at the time of vesting. Our stock plan administrator has asked me to choose how I want to pay for the tax withholding when my RSUs vest. I have 3 choices:

1. Same Day Sale. This is the simplest. On the vesting date, I sell everything. After subtracting for tax withholding, I end up with net cash.

2. Sell to Cover. If I choose this option, they will sell just enough shares to cover the tax withholding. I keep the remaining shares and I can sell them myself whenever I want to.

3. Cash Transfer. For this option I will have to come up with cash myself to cover the tax. After that I have all the shares and I can sell them whenever I want to.

Which should I choose? Let's use an example and see the math. Suppose I will have 100 shares vested; the price on the vesting date is $50; and the tax withholding is 40%.

1. Same Day Sale. I will have $50 * 100 * (1 - 40%) = $3,000.

2. Sell to Cover. I will have 100 * (1 - 40%) = 60 shares and no cash.

3. Cash Transfer. I will be out $50 * 100 * 40% = $2,000 cash but I will keep 100 shares.

Option (2) Sell to Cover is equivalent to doing Option (1) Same Day Sale and immediately buying 60 shares with cash on the open market.

Option (3) Cash Transfer is equivalent to doing Option (1) Same Day Sale and immediately adding $2,000 from my own pocket and then buying 100 shares.

If I find my employer's stock attractive, I can buy it at any time for however many shares I want. I don't have to buy it on the RSU vesting date or buy those exact number of shares. So there is no advantage whatsoever for them to do it for me. This is a no-brainer. I chose Sale Day Sale.

Related posts:

• RSU Sell To Cover Deconstructed
• Restricted Stock Units (RSU) Sales and Tax Reporting
• Employee Stock Purchase Plan (ESPP) Is A Fantastic Deal

2007 Tax Year AMT Brackets

Congress passed another patch for the Alternative Minimum Tax (AMT) late last year. With that, I can finally calculate the AMT marginal tax brackets for the 2007 tax year. If you are not familiar with AMT, please read my previous post, Tax Deduction Denied.

Because of an exemption phase-out rule, people whose incomes are in the middle of the AMT range pay a higher AMT marginal tax rate than people on either the low or the high end. This is relatively unknown. Many people think there are just two brackets in AMT, 26% and 28%. There are actually four brackets. In addition to 26% and 28%, there are also 32.5% and 35% brackets for people who are in the exemption phase-out range. Unfortunately many people who are hit by the AMT also fall in the phase-out range.

For each filing status, three numbers are pertinent for calculating the AMT brackets.

	Married Filing Jointly	Single OR Head of Household
AMT exemption amount (E)	$66,250	$44,350
AMT exemption phase-out point (P)	$150,000	$112,500
28% AMT breakpoint (B)	$175,000	$175,000

 

The AMT exemption amount (E) is the number congress has been increasing temporarily every year in the last few years. The other two numbers have not been changed lately. If your income is below E, you are not subject to the AMT. If your AMT Income is between E and P, your AMT marginal tax rate is 26%. The other two milestones, which I call X and Y, are given by the following formula:

X = (B + E + 0.25 * P) / 1.25

Y = 4 * E + P

For AMT Income between P and X, the marginal AMT rate is 32.5%; between X and Y, it's 35%. Once you go over Y, the AMT rate drops to 28%. If you are curious in how the formula for X and Y are derived, please read this blog post by IndexFundFan.

For 2007, using values for E, B and P in the table above, X comes out to $223,000 for married filing jointly, $197,980 for single or head of household; Y is $415,000 for married filing jointly, $289,900 for single or head of household. Here's the complete AMT rate table for the 2007 tax year:

Married Filing Jointly	Single or Head of Household	AMT Income	QD and LTCG*
<= $66,250	<= $44,350	0%	5% / 15%
<= $150,000	<= $112,500	26%	15%
<= $223,000	<=$197,980	32.5%	21.5%
<= $415,000	<=$289,900	35%	22%
> $415,000	> $289,900	28%	15%

* QD = Qualified Dividends; LTCG = Long Term Capital Gains

First notice the marriage penalty. If each spouse earns $80,000, a married couple is in the 32.5% bracket. If they were single, they are both in the 26% bracket. That's a big difference. Throw in the state income tax and Social Security and Medicare tax, the couple's combined marginal tax bracket can reach nearly 50%. Also notice the significant penalty on qualified dividends and long term capital gains for people in the phase-out zones . Together with state income tax, the marginal tax rate on qualified dividends and long term capital gains can exceed 30%. That's a lot higher than the 15% number everybody talks about.

What do you do if you are affected by the AMT, or worse yet, if you are in the AMT exemption phase-out zone? Not much unless you are willing to move to a low/no tax state or not have kids. Know what your marginal tax bracket really is. Use an AMT-free tax-exempt money market fund instead of a regular money market fund or savings account. If you are in the phase-out zone, minimize even qualified dividends and long term capital gains.

More On Missing The 10 Best Days

Blogger Nickel at fivecentnickel.com made some great comments to my post about missing the 10 best days in the stock market. I showed in my post that the probability of missing the best 10 days in 10 years is one in 2.8 billion billion billion. Nickel disagreed. Because the comments require a long response, I'm making a new post as opposed to burying it in the comments. First, the comments from Nickel:

"While you're correct that this overstates the problem in that people won't miss just the 10 best days of the market, you're forgetting that the biggest days often come in the earliest stages of a recovery.

"For example, looking over the past 25 years, three of the 10 biggest days came in the week and a half following Black Monday, and two more of them occur in close succession at the very tail end of the dot bomb debacle. Thus, these days are concentrated into periods when people are especially likely to have bailed on the market and not gotten back in.

"Consider the scenario in which sometimes gets smacked on Black Monday, jumps out of the market to lick their wounds, and then immediately misses gains of 9.3%, 5.3% and 4.9%. They've now locked in a huge loss that they had little chance of avoiding in the first place, and they also missed out on a huge recovery.

"Calculating the probability that people will randomly miss the ten best days is a *huge* oversimplification, and it casts doubt on your entire argument."

I want to thank Nickel for the comments and address the issue of best days coming right after the stock market bottom. Since he brought up Black Monday in 1987 and the dot com bubble, let's take a closer look.

Black Monday was October 19, 1987. The S&P 500 dropped a whopping 20.5% on a single day, from 282.70 to 224.84. Let's say a nervous investor sold the very next day on the open. The price was 225.06, close to the bottom made on the previous day. In the next 10 days, he would've missed 3 of the 10 best days in the next 20 years, which had returns of +5.33%, +9.10%, and +4.93% respectively. Does it mean this investor missed a total of (1 + 5.33%) * (1 + 9.10%) * (1 + 4.93%) - 1 = 20.6% of returns? No, after 3 best days passed, S&P 500 closed at 244.77 on 10/29/1987, up 8.8%, not 20.6%, from the 225.06 level he sold at. A little over a month later, on 12/3/1987, the market returned to 225.21, which was about the same level as the previous bottom. Now, having missed 3 of the 10 best days in the next 20 years, this investor didn't suffer any damage if he got back in a month and half later.

Date	S&P 500 Close
10/16/1987	282.70
10/19/1987	224.84 (sold here)
10/20/1987	236.83
10/21/1987	258.38
10/29/1987	244.77 (missed 8.8% of gains)
12/03/1987	225.21 (back to where it was)

Now, let's look at the same for the 2 best days in 2002. On 7/24/2002 and 7/29/2002, the S&P 500 had two best days, up 5.73% and 5.41% respectively. By then the bear market had gone on for over two years. If an investor was nervous, he would've sold way before then, perhaps in early 2001 when the S&P 500 dropped to 1,300 from 1,500 in the previous year, or in early 2002 when the S&P 500 dropped more than 20% in two years. For argument's sake, let's say our unlucky investor sold right before the best days, on 7/23/2002, at the close of 797.70. After two of the 10 best days in 25 years, the market closed on 7/29/2002 at 898.96, up by 12.7%. Was that a permanent loss of opportunity if the investor missed those two best days? Once again, no. 2 months and 10 days later, on 10/7/2002, the S&P 500 went back to 785.28, lower than the 797.70 price before the best days.

Date	S&P 500 Close
1/3/2000	1,455.22
1/2/2001	1,283.27 (down 12% from a year ago)
1/2/2002	1,154.67 (down 21% from two years ago)
7/23/2002	797.70 (sold here)
7/24/2002	843.43
7/29/2002	898.96 (missed 12.7% of gains)
10/7/2002	785.28 (lower than where it was)

Will the market always return to the previous low before the best days? I don't think anybody has an answer to that. The market is volatile and unpredictable. I continue to believe that (a) it's impossible to miss only the 10 best days; and (b) even if some best days were missed, the damage isn't nearly as bad as those meaningless stats imply.

Suppose calculating random odds is a *huge* oversimplification like Nickel said, and because the best days often come in the early recovery days, I'm off by a factor of a billion. That is huge, right? Say instead of one in 2.8 billion billion billion, the odds of missing the 10 best days in 10 years is actually only one in 2.8 billion billion. That is still 100 times less likely than winning 2 consecutive Powerball jackpots with the same set of numbers. If I write about what I would do if I won 2 consecutive Powerball jackpots with the same numbers, nobody will take me seriously because it's meaningless to talk about impossible events. Well the stats on missing the 10 best days in 10 years fall into the same camp. They are not worth the attention given to them.

Trust me, I don't advocate timing the market. I just think this missing the 10 best days in 10 years thing is over-hyped by at least a factor of a billion. My question to Nickel and all other readers, if it's not one in 2.8 billion billion billion, or one in 2.8 billion billion, what do you think the odds are for missing the 10 best days in 10 years and how do you prove it?

When Charts Lie

Charts are good ways of presenting data. But sometimes they lie, or shall I say create a wrong impression.

I was at a Barnes & Noble bookstore the other day looking for new finance and investment books. I picked up The Smartest Investment Book You'll Ever Read by Daniel Solin. It's from a new author I haven't heard before. The book is actually not bad. I will do a review later. But this post is not about the book. It's about charts.

In the book there is a chart showing that even over a long period of time, sometimes stocks would return less than bonds. The period chosen was 1965-1984. This is true. During 20 years from 1965 to 1984, stocks returned less than bonds. The chart looks like this:

Got it? Stocks returned less than bonds. The data in the chart are correct. The impression you got from it is probably wrong though. Look again:

See the problem? The X axis doesn't start from zero. So you are looking at the end of a chart through a magnifying glass. The correct chart should look like this:

In the first two charts, the bar for bonds is 2.7 times longer than that for stocks. In the correct chart, the two bars are almost the same length. Stocks still returned less than bonds in those 20 years, but not a whole lot less, just a hair less.

Same data, different charts. It all depends on where the starting point is. I don't mean to imply that the author Dan Solin intended to mislead. The chart in the book is what Excel produces by default. Next time you read a chart though, pay attention to the starting point!

More examples of how charts lie: http://www.mrexcel.com/tip142.shtml. Enjoy!

APR or APY, It Doesn't Matter

It's very strange. I see a lot of people reaching my blog when they search for information on converting APR to APY or vice versa. They end up on my post last year Interest Rate: APY and APR which mentioned two Excel formula: EFFECT which converts APR to APY, and NOMINAL which converts APY to APR. While it's nice to know that 5% APR is 5.13% APY and 5% APY is 4.88% APR, I think they are missing the big picture. The difference between APR and APY is not a big deal.

If someone is carrying a car loan at 4.99% APR and the interest rate on an online savings account is 5.30% APY, is this person better off keeping the money in the savings account or paying off the car loan? Do I need to convert one to the other and compare the numbers? Not really. The difference between APR and APY is so small you can pretty much ignore it. What makes a much bigger difference is taxes. You have to pay federal and state income taxes on the interest earned in a savings account. There is no tax deduction for the car loan. Therefore, before taxes, 5.30% APY and 4.99% APR are about equal; after taxes, 5.30% APY is much smaller than 4.99% APR.

So, if anyone comes to this post again searching for converting APR to APY or converting APY to APR, please stop. Forget it. It doesn't matter. Look at the effect of taxes instead.

Avoiding the Worst Days and Missing the Best Days

Two readers commented about avoiding the worst days on my post about the meaningless stats on missing the best days. The stock market had some bad days since then. I think some might be interested in reading about avoiding the worst days.

First I want to emphasize that the whole point of my previous post was that it's IMPOSSIBLE to miss the best 10 days in 10 years. The odds are 1 in 2.8 billion billion billion, which is like winning the Powerball jackpot with a single ticket purchase back to back to back. By the same calculation it's equally IMPOSSIBLE to avoid the worst 10 days. But since they asked, I compiled some numbers for avoiding the worst 10 days in 10 years. So here you go, more meaningless stats.

The rewards for avoiding the worst days are equally as impressive as the penalty for missing the best days. Look at this chart:

 

$1 invested for 10 years turned into $2.24 if left untouched. If the best 10 days had been missed (IMPOSSIBLE), it would grow to only $1.40, barely beating inflation. If the worst 10 days had been avoided (IMPOSSIBLE again), it would become $3.67, a huge jump. If both the best 10 days and the worst 10 days were taken out, they would cancel out each other -- $1 would grow to $2.29, similar to the $2.24 number if left untouched. So don't worry about missing the best days or try to avoid the worst days.

The exercise did produce some useful insights. Between July 1, 1997 and June 30, 2006, which is the timeframe Schwab used in its article, the best 10 days and the worst 10 days for S&P 500 were: 

All together the best 10 days were up 60%, and the worst 10 days were down 39%. You see the best days don't necessarily fall in bull markets and the worst days don't necessarily fall in bear markets. 7 out the 10 best days happened in the bear market from 2000 and 2002, during which the S&P 500 index lost 38%. 4 out of 10 worst days happened in 1997 and 1998, when the S&P 500 gained 71%. 

The bottom line is that the stock market is volatile. Sometimes it goes up and down a lot. A good day on the stock market doesn't necessarily mean good times are ahead. A bad day doesn't necessarily mean good times are over. Ignore the noise.

Personal Rate of Return: Dollar Weighted Or Time Weighted

After reading my post about estimating overall personal rate of return, a reader Brian asked:

"I have a Fidelity serviced 401(k) and I had always wondered about how they calculated the personal rate of return. Do you know how/if other providers calculate personal rates of return? If I were to open a brokerage account, is there one company that does this better than others?"

Rates of return fall into two major categories: Dollar Weighted Rate of Return and Time Weighted Rate of Return. They measure different things and they should be used for different purposes.

Dollar Weighted Rate of Return measures how much your investment dollars returned on average. Use this measure when you want to see if your return is above or below your long term return objective. The method for calculating the Dollar Weighted Rate of Return is XIRR. You will need to know the beginning balance, the date and the amount of your every contribution (and withdrawal, if any), and the ending balance. With a computer, the calculation itself is not difficult but collecting all the data can be tedious.

Time Weighted Rate of Return measures how much the combination of your investment choices returned on average, without the influence of the size and timing of your own contributions or withdrawals. There's a subtle difference here. Time Weighted Rate of Return ignores the effect of the external cash flows, that is, the cash flows from you. Use this measure when you want to see how your investment choices taken together, including any changes you made to your investment choices, returned compared to other choices or an index. There are two primary methods for calculating the Time Weighted Rate of Return: Daily Valuation and Modified Dietz. Daily Valuation is more accurate. Modified Dietz is a close approximation. For practical purposes, there's not much difference between those two calculation methods.

The personal rate of return you get from a financial service provider like Fidelity or Schwab is usually a Time Weighted Rate of Return. If you want a Dollar Weighted Rate of Return, you will have to do it yourself.

Let's put these in an example. Say you had $10,000 at the beginning of the year and your investments did great in the first 3 months. Your $10,000 turned into $12,000 without you adding a penny. Now on April 1, you put in $20,000 more, but your investments stalled in the rest of the year, and you end the year with $32,000. So you made 20% on $10,000 in the first 3 months and you made 0% on $32,000 in the next 9 months. If you plug in these values in a spreadsheet and use the XIRR function, you get 8%. This makes sense because:

10000 * (1 + 8%)^1/4 = 10194
(10194 + 20000) * (1 + 8%)^3/4 = 31988

Pretty close except for rounding. If you didn't put in additional $20,000 on April 1, your return would've been 20%. That's how your investment choices did and that's how Fidelity or Schwab will report to you. There's a big difference between the 8% Dollar Weighted Rate of Return and the 20% Time Weighted Rate of Return because the cash flow in this example is unusually large. If we change the additional contribution on April 1 from $20,000 to $1,000 and have the end of year value at $13,000 instead of $32,000, the two returns would be much closer. The Dollar Weighted Rate of Return would be 18.6%, and the Time Weighted Rate of Return would still be 20%.

Finally, because financial service providers typically provide only Time Weighted Rate of Return, and because the actual calculation methods for Time Weighted Rate of Return (Daily Valuation and Modified Dietz) yield similar results, there is no reason to believe that one company does it better than another. 

For more information on Dollar Weighted Rate of Return, Time Weighted Rate of Return, Daily Valuation and Modified Dietz, if you are not afraid of math formula, please read this article from dailyVest:

• Personal Rate of Return/Investment Performance Calculation Methodologies & Assumptions

Out of the Market and Meaningless Stats

The stock market had a field day last Thursday (7/12/2007). The Dow rose 284 points, its biggest point gain in nearly five years. It reminded me of the stats about the risk of being out of the market. It goes like if you missed the best X days in Y years in the stock market, your return would've been cut in half or something like that. Let me tell you those stats are meaningless.

There's a chart like this in a recent issue of Schwab's On Investing magazine (sorry, no online link):

It said the S&P 500 Index returned on average 8.4% a year between July 1, 1997 and June 30, 2006. Based on an average of 252 trading days a year, if someone missed the best 10 trading days in those 10 years, the return would've been only 3.4% a year. In dollars, 8.4% a year means $10,000 invested in 1997 would turn into $22,402 in June 2006, for a cumulative gain of 124%. If one missed the best 10 days, $10,000 in 1997 would only turn into $13,970 in 2006, or only a 40% cumulative gain. If someone missed the best 40 days, the return would've become -6.4%, which means $10,000 in 1997 would turn into $5,161, for a cumulative loss of 48%. Hmm ... 124% gain or 40% gain, perhaps even a 48% loss, night and day, huh? Unbelievable.

These striking stats are used as arguments against market timing because they illustrate the risk of being out the market. Market timing means investing in the market when the conditions are considered favorable and getting out of the market when the conditions are considered as unfavorable. There are various schemes of market timing. Some are based on seasonality, some on chart shapes, some on valuation metrics. It is argued that if someone is out of the market for even a short period of time, 10 days or 40 days in the example above, and if they happen to be out on the wrong days (best X days in Y years), the long term return would suffer, a lot.

Although I haven't double checked the statistics myself, I don't doubt their accuracy. The stats are technically true however this piece of information is meaningless. Why? Let's see what the stats really say. Being out of the market on the 10 best days in 10 years means that

1. Someone is out of the market for 10 and only 10 days out of 2,520 trading days in 10 years; AND
2. Those 10 days happen to be the best 10 days in 10 years.

If someone is going to be out of the market for 10 days, how likely is it that he/she will cherrypick 10 random days which in hindsight happen to be the best 10 days in 10 years? Very unlikely. How unlikely though? A math exercise will tell us.

The math formula for our calculation is called combination. We are calculating the number of ways you can choose 10 days from 2,520 trading days in 10 years. There is only one possible way those 10 days happen to be the 10 best days.

C(2520, 10) = 2520 * 2519 * 2518 * ... * 2511 / 10! = 2.796E+27

You will need a scientific calculator for this. The ! symbol means factorial. If you use Excel, enter this formula and you will get the same result.

=COMBIN(2520,10) = 2.796E+27

The symbol E here represents scientific E notation. That's 2.796 * 10²⁷, or 2,796 followed by 24 zeros. A billion is 10⁹. What we have here is that this unlucky market timer has one in 2.8 billion, billion, billion chance for missing the best 10 days in 10 years. In other words, IMPOSSIBLE. What about missing the best 40 days? Don't even go there.

What's the point of zeroing in on this impossible event? I don't know. Shock and awe, perhaps. Nobody should care what happens if the chance of it happening is one in 2.8 billion billion billion. If some other one in 2.8 billion billion billion event happens to me, I will be a million times richer than Bill Gates and Warren Buffett combined. The meaningless stats don't support effectively what they are supposed to prove. The really meaningful stats are those for the average or median impact to one's long term return if someone is out of the market for 10 random days, or 10 random consecutive days, not the 10 best days. I've never seen stats for those scenarios. Perhaps because they don't support what they are trying to tell you. My guess is that the average impact of being out of the market for 10 random days or 10 random consecutive days in 10 years is practically zero.

Does this mean it's OK to time the market then? No, just the cited evidence doesn't support the case. There are other valid reasons for not timing the market, but this 10 days out of 10 years thing isn't one of them. At least one shouldn't be too worried about being out of the market for a few days when they have a 401k rollover being moved from one place to another. Relax. It's not a big deal as some make it out to be.

Related Posts:

• Avoiding the Worst Days and Missing the Best Days
• More On Missing The 10 Best Days

Commutative Law of Multiplication

Commutative Law of Multiplication is a fancy way of saying when you multiply two numbers, it doesn't matter which number you put down first and which number you put down second.

a * b = b * a

This basic law of arithmetic is taught in the second grade in elementary school. Yet it is very useful when you evaluate the relative merits between Traditional 401k, Roth IRA, and the new Roth 401k.

Blogger Trent writes the popular blog The Simple Dollar, which is one of the most successful personal finance blogs. Unfortunately Trent made the mistake of not recognizing the Commutative Law of Multiplication. In his post The New Roth 401(k) Versus The Traditional 401(k): Which Is The Better Route? he said Roth 401k is better even if the tax rate in the future is lower than the tax rate at present. His reasoning was

"Basically, by paying $2,800 a year now in extra taxes, Joe saves himself $14,000 a year in retirement."

Wrong. It matters not how much tax you pay at different times. What matters is how much money you have left after all the taxes are paid. Sadly when more than one commenters pointed out the problem with Trent's math, he still insisted that his math was correct. You would think a blogger writing about finance and investment should "get it," but I guess not.

In case someone out there is still confused, here's how the math works. Let t₀ be the marginal tax rate now, and t₁ be the marginal tax rate at retirement time. Suppose through successful investing, you are able to grow each dollar to $n when you are ready to retire. For each dollar you invest in a Traditional 401k, you will have $n before tax, and n * (1 - t₁) after tax. In a Roth IRA or Roth 401k, for each dollar before tax, you pay tax first and have (1 - t₀) dollars left after tax. Growing the money to the same degree, you will have (1 - t₀) * n when you are ready to retire. If the tax rate now (t₀) is the same as the tax rate at retirement time (t₁), we have

n * (1 - t₁) = (1 - t₀) * n

There, is the Commutative Law of Multiplication.

If the tax rate at retirement time is lower, t₁ < t₀, Traditional 401k will be better than Roth 401k because the value on the left hand side is larger than the value on the right hand side. The opposite is true if the tax rate at present is lower, t₀ < t₁.

Of course nobody knows what the future tax rates will be or whether they will be higher or lower than today's. In choosing between a Traditional 401k and a Roth 401k, you just have to take a guess or do a little of both. For me, my money is on the Traditional 401k. I think the Roth 401k is a device for the current government to maximize its current revenue at the cost of robbing revenues from the future government. When the future government needs money, it will find ways to raise revenue including taxing on Roth withdrawals either directly or indirectly. The laws on Roth IRA and Roth 401k only say withdrawals from them today are not taxed. They don't say withdrawals won't ever be taxed. Tax laws can be changed by the legislature in the future.

Related Post: The Case Against Roth 401(k)

Estimate Your Personal Rate of Return for Multiple Years

Since I wrote about a simple formula for estimating your personal rate of return, someone asked whether the same formula works for multiple years as well. The answer is yes and no. It works well, provided that

1. the net investments during the period are roughly even; and
2. the beginning balance is large relative to the net investments

The simple formula doesn't work well if these two conditions are not met.

After applying the simple formula, you get a cumulative return. You will have to annualize the result using this formula:

average annual return = (1 + cumulative return) ^ (1 / number of years) - 1

Here's an example:

Beginning Balance 1/1/1998: $20,000
net investment in 1998: $1,000
net investment in 1999: $1,100
net investment in 2000: $1,200
net investment in 2001: $1,300
net investment in 2002: $1,400
net investment in 2003: $1,500
net investment in 2004: $1,600
net investment in 2005: $1,700
net investment in 2006: $1,800
Ending Balance 12/31/2006: $50,000

Total "net in" = $1,000 + $1,100 + ... + $1,800 = $12,600

cumulative return = ($50,000 - 0.5 * $12,600) / ($20,000 + 0.5 * $12,600) - 1 = $43,700 / 26,300 - 1 = 66.2%

This means for this example, the cumulative return in 9 years from 1/1/1998 to 12/31/2006 is 66.2%. To convert it into an annualized number, you do:

average annual return = (1 + 66.2% ) ^ (1/9) - 1 = 5.8%

If I use the more precise XIRR method for this example, I get 6.0%. So 5.8% is close enough. But, if we have a different set of numbers,

Beginning Balance 1/1/1998: $2,000
net investment in 1998: $1,000
net investment in 1999: $1,100
net investment in 2000: $1,200
net investment in 2001: $1,300
net investment in 2002: $1,400
net investment in 2003: $1,500
net investment in 2004: $1,600
net investment in 2005: $1,700
net investment in 2006: $1,800
Ending Balance 12/31/2006: $25,000

Note the beginning balance of $2,000 is small relative to the net investments of $12,600. The simple formula gives the result of 9.4%, while the more precise XIRR method gives 11.0%. In this second example you are better off with the XIRR method because I think the 1.6% difference is too big.

Estimate Your Overall Personal Rate of Return

Happy New Year! Now that 2006 is over, it's time to see how our investments did last year. You can look up the performance numbers online or in newspapers, but if you bought or sold during the year, those numbers won't match your own rate of return because the numbers online or in the paper assumes a) a single investment at the beginning of the year; and b) held until the end of the year with no additional deposits or withdrawals. Plus, in order to measure your progress toward your end goal, it's more meaningful to get an overall rate of return on all your savings and investments, instead of returns for each investment. You want to know overall how you did during the year, when everything is taken into consideration, your 401k, IRA, regular taxable investments, savings accounts, treasury bills, etc., etc. Did you beat inflation? By how much?

If you have detailed records for each buy and sell, you can gather them up and use the XIRR function in Excel or OpenOffice to calculate the precise rate of return. See this post by JLP for details. But if you have several accounts and you invest every month, getting all those numbers together will be quite time consuming. There is a much simpler way to estimate your rate of return. Here's what you need for doing the estimate:

• Define your savings and investment pool
• Total balance in your pool at the beginning of the year ("beginning balance")
• Total amount you put into your pool minus total amount you took out of your pool during the year ("net in")
• Total balance in your pool at the end of the year ("ending balance")

That's it. Just 3 big-picture numbers. Ignore all inter-account transfers -- sell one fund, buy another; buy stocks using money in saving account, etc. These all happened within the pool. Ignore all interests, dividends, capital gains distribution, unless you took them out of your investment pool. Ignore all fees and commissions paid from the money in the accounts. If you paid fees outside of your pool, count them in the "net in" number.

Here's the formula:

         (ending balance - 0.5 * net in)

return = ----------------------------------  - 1

         (beginning balance + 0.5 * net in) 

It's very easy to remember. It assumes that your total net investment was invested 50% at the beginning of the year and 50% at the end of the year.

Here's an example:

Beginning balance: $100,000
Net In: $38,000
Ending balance: $150,000

rate of return = ($150,000 - 0.5 * $38,000) / ($100,000 + 0.5 * $38,000) - 1 = $131,000 / $119,000 - 1 = 10.1%

How accurate is this quick and dirty method versus the more time consuming XIRR method? For me, the difference is only 0.2%. You save a lot of time and get to focus on the big picture: How much did you contribute toward your goal (net in)? Did your savings and investments provide adequate return?

Employee Stock Purchase Plan (ESPP) Is A Fantastic Deal

If you work for a publicly traded company which offers an Employee Stock Purchase Plan (ESPP), you've got yourself a fantastic deal. An ESPP typically works this way:

1. You contribute to the ESPP from 1% to 10% of your salary. The contribution is taken out from your paycheck. This is calculated on pre-tax salary but taken after tax (unlike 401k, no tax deduction on ESPP contributions).
   
2. At the end of a "purchase period," usually every 6 months, the employer will purchase company stock for you using your contributions during the purchase period. You get a 15% discount on the purchase price. The employer takes the price of the company stock at the beginning of the purchase period and the price at the end of the purchase period, whichever is lower, and THEN gives you a 15% discount from that price.
   
3. You can sell the purchased stock right away or hold on to them longer for preferential tax treatment.

Your plan may work a little differently. Check with your employer for details.

The 15% discount is a big deal. It turns out to be a 90% annualized return or higher.

How so? Suppose the stock was $22 at the beginning of the purchase period and it went down to $20 at the end of the period 6 months later. Here's what happens:

1. Because the stock went down, your purchase price will be 15% discount to the price at the end of the purchase period, which is $20 * 85% = $17/share.
   
2. Suppose you contributed $255 per paycheck twice a month. Over a 6-month period you contributed $255 * 12 = $3,060.
   
3. You will receive $3,060 / $17 = 180 shares. You sell 180 shares at $20/share and receive $20 * 180 = $3,600, earning a profit of $3,600 - $3,060 = $540.

Percentage-wise your return is $540 / $3,060 = 17.65%. But, because your $3,060 was contributed over a 6-month period, the first contribution was tied up for 6 months, and the last contribution was tied up for only a few days. On average your money is only tied up for 3 months. So, earning 17.65% risk free for tying up your money for 3 months is equivalent to earning (1 + 17.65%) ^ 4 - 1 = 91.6% a year.

90%+ a year return is fantastic, isn't it? That's when the employer's stock went down. Had the stock gone up from $20 at the beginning of the purchase period to $22 at the end, your return will be even higher at 180%!

[Update on May 30, 2008]: I created an online spreadsheet on Zoho. You can plug in your own numbers and calculate the annualized return. The annualized return is what a savings account will have to offer in order to match the same return from an ESPP. Even at 5% discount without lookback, an ESPP is still equivalent to a 20% APY savings account.

What should you do if your employer offers an ESPP? Participate to the MAXIMUM allowed as long as you can sell the stock soon after the stock is purchased.

Should you hold the purchased stock longer for preferential tax treatment? No! On the typical 6-month purchase program, you will have to hold on to the stock for additional 18 months in order to get preferential tax treatment. If everything goes well, you can reduce the tax on your profit from say 35% to 15%. In the above example, that will save you $540 * 20% = $108. But if your employer's stock goes down 3% during the 18 months you are holding the stock, because your entire $3,600 is at stake, the tax benefit will be completely wiped out. You already earned 90% annualized return on the purchase. Holding on for another 18 months and hoping the stock won't go down 3% is really penny wise pound foolish.

More technical details about ESPP on the web:

• Designing and Implementing a Section 423 ESPP by Timothy J. Sparks of Wilson Sonsini Goodrich & Rosati
  
• Guide to Employee Stock Purchase Plans (ESPPs) by Kaye A. Thomas of Fairmark (I strongly disagree with Mr. Thomas about his objection to flipping ESPP shares. The risk of holding the stock is too high.)

Finance Charge in Insurance Payment Plans

I often read on the blogs when someone talks about their insurance

my ___________ (life, car, home, ...) insurance is $______ a month.

or

I saved $_______ a month on my ____________ insurance.

So it seems that a lot of people pay their insurance by month. I've always paid my insurance in a single payment because insurance companies typically charge a small processing fee or service fee if you choose to pay by month. That service fee looks like a finance charge to me. No other business charges its customers by the number of times the customer pays. The reason the insurance company charges extra is because it extends credit to you if you don't pay the entire premium when it's due, the same way the credit card company charges you interest if you don't pay the balance in full. What the insurance companies call service fee is really interest or finance charge in disguise.

I received the bill for my car insurance renewal last week. Here are my options:

1. Make one payment for $736; or
2. Make four payments of $184, plus a $4 service fee for each payment.

So if I take the 4-payment plan, I will pay $752 over 4 months, $16 more than the $736 premium due. That's only a little over 2% of the premium. Not bad for making it easier on the budget? Not so fast. I decided to take a closer look at the embedded interest rate in the 4-payment plan. Here's the insurance company's cash flow for the 4-payment plan:

10/31/2006	-736
10/31/2006	188
12/1/2006	188
1/1/2007	188
2/1/2007	188

The interest rate turns out to be ... ... 19%! Wow, taking the 4-payment plan amounts to charging it on a credit card and paying 19% interest!

To calculate the interest rate, you need the XIRR function in Excel or OpenOffice. I made an Excel spreadsheet for this exercise. You can download it here. If your insurance company charges you a service fee for paying by month, plug in your own numbers and see what interest rate they are charging. If you don't mind, please post in the comments the company, product and the built-in interest rate on their payment plans.

If you want to learn more about the XIRR function, please read this post by Ricemutt at Experiments in Finance. For tips on saving for infrequent bills so you can avoid paying finance charge on insurance, please read this post at Tired but happy.

Calculator for 401(k), Roth IRA, then Back at 401(k)

I searched on the Internet for a calculator that implements the strategy outlined in my previous post 401(k), Roth IRA, then Back at 401(k). But to my surprise I couldn't find any. There are calculators for 401(k), calculators for Roth IRA, but not one that combines them together. So I had to make one myself. If the inline frame doesn't display well, you can download it. It's simple HTML, with calculation done by JavaScript. Please let me know if see any problems with it. Also let me know if you find a better one elsewhere.

TIPS Pricing Is Complex

At the end of my previous post about TIPS, I wrote:

There are many fine details on how TIPS really work.

Just to give you a glance of how complex it really can be, take a look at this thread on the Vanguard Diehards Forum. Al asked if someone can estimate how much a $1,000 TIPS note on auction next Monday will really cost. I took a shot at it. Then Pat Morgan showed me how I was off by 3/10th of a penny. Just when I was content with the correct calculation, Pat posted a link to the Appendix of a 400-page Federal Regulations, with these monster definitions and formula on page 403:

Oh boy! Fortunately we have computers for this kind of stuff. I made a spreadsheet for the calculation. In the end, the spreadsheet showed that my initial quick and dirty calculation was off by about $0.48 per $1,000 bond. Perhaps only a math nerd can truly appreciate the quest for precision.

Interest Rate: APY and APR

When the bank pays you interest on a savings account, it quotes the interest rate in APY -- Annual Percentage Yield. When the bank charges you interest on a loan (car loan, credit card, mortgage, etc.), it quotes the interest in APR -- Annual Percentage Rate. So, if you have a savings account that the bank pays you 5% APY and a car loan that you pay the bank 5% APR, are you even with the bank, ignoring the effect of taxes? You guessed it, NO, the bank wins. This is because for the same interest rate, APY is always larger than APR. So the bank wants you to think the interest it pays you is higher than it actually is and it wants you to think the interest you are paying them is smaller than it actually is. Sneaky, huh?

JLP at AllFinancialMatters gave the formula:

APY = (1 + APR ÷ n)ⁿ – 1

where n = the number of compounding periods.

Don't want to use a calculator but let Excel calculate for you? Here's the formula for Excel:

APY = EFFECT(APR, n)

Put =EFFECT(5%, 365) in a cell and you will get 5.127%, which means the APY on a 5% APR loan is actually 5.127%.

The equivalent formula is

APR = n * (1 + APY)^1/n - n

The formula for Excel is:

APR = NOMINAL(APY, n)

Put =NOMINAL(5%, 365) in a cell and y