Published on

See this if you're having trouble printing code examples

O'Reilly Book Excerpts: Online Investing Hacks

*Editor's note: Bonnie Biafore offers 100 tips and tricks to help you get a better handle on your finances in her new book, *Online Investing Hacks*. In this sample excerpt, Bonnie walks you through the steps you should consider in order to pick the best brokerage (or broker) for your trading style, and your pocketbook.
*

**Commissions and fees can take a big bite out of your investment returns, so
you should consider your typical trading patterns to determine the most costeffective
brokerage.**

Related Reading Online Investing Hacks |

Every time you buy or sell an investment, you pay brokerage commissions. If you trade frequently or make purchases with only a few dollars, commissions can consume a big percentage of your returns. It makes sense to find the brokerage that executes your trades at the best possible price, but commission schedules are a complicated business. Brokerages charge different commissions based on a number of criteria. It's almost impossible to capture all the subtleties of brokerage commissions, because each company calculates commissions a little differently. However, you can build a spreadsheet to estimate your annual brokerage commissions based on the methods and frequency of your trades.

Brokerages often charge different commissions depending on whether you place your trades online, use a touch-tone phone system, or speak to a broker. In some cases, market orders cost less than limit orders [Hack #54]. Many brokerages offer discounted commissions for investors who trade frequently— typically a minimum of 72 trades a year. For trades that represent a large number of shares or high dollar values, commissions are often calculated using a minimum commission plus a small percentage of the trade amount. In addition, bond and option commissions have their own schedule of fees. In many cases, brokerages charge the same minimum commission whether you buy one or nine bonds, and only switch to a sliding scale when you reach ten bonds or options contracts.

The spreadsheet in Figure 9-1 offers a rudimentary comparison of the commissions you might pay to different brokerages. The calculations take into account the number of market and limit orders you estimate that you make in a typical year (cells B2 and B3), the number of bonds and options contracts you might purchase (cells B5 and B6), and the percentage of trades you place online (cell E2), using a touch-tone phone system (cell E3), or speaking directly to a broker (cell E4).

*Figure 9-1. Create a spreadsheet to determine which brokerage's commission schedule
works for you.*

The spreadsheet displays na (not applicable) in the value cells and highlights the cells with red if a brokerage doesn't handle a type of trade or a type of investment, as in the case of ShareBuilder [Hack #57], which offers only online trades and only for stocks. If you like a service the brokerage provides, such as ShareBuilder's ultralow commissions for trades placed on Tuesdays, you can set up accounts with two brokers.

This spreadsheet oversimplifies the calculations for active trader discounts, because it compares the total annual trades you estimate to the annual number of trades required by a brokerage. Unfortunately, most brokerages determine the active trader discount quarterly, so the commissions shown in the spreadsheet might be lower than the ones you pay.

To get an idea of how to build formulas to calculate commissions, dissect the one for online limit orders. The formula in Example 9-1 uses nested IF functions to incorporate the active trader discount and to display na if the brokerage doesn't handle the trade type.

Example 9-1. A formula to estimate commissions for online limit orders```
online limit commission paid (cell C13)
=IF(B13="na","na",IF(total<B$10,B13,B13+B$11)*limit_num*online)
```

The outer `IF`

function in Example 9-1 checks whether the commission rate for
the brokerage is na. If it is, the function propagates na to the cell that represents
the commission paid (C13). Otherwise, the commission you pay is
based on the number of trades and the charge for the type of trade and access.

The `IF`

function in Example 9-2 calculates the commission rate for the third
parameter in Example 9-1. It checks whether the total number of stock
trades is less than the active trader minimum (cell B10). If the number of
trades is less than the minimum, the function selects the standard commission
rate (cell B13). Otherwise, it adds the active trader discount (cell B11)
to the standard commission rate.

`commission rate = IF(total<B$10,B13,B13+B$11)`

The formula in Example 9-3 summarizes the calculation for the third parameters of the IF function in Example 9-1. It multiplies the commission rate by the number of trades for the type of trade and by the percentage of trades performed using the type of access. The cells for the number of trades that you estimate use named ranges. The named range for cell B2 is market_num. The named range for cell B3 is limit_num. Likewise, the percentages for types of access use named ranges: cell E2 is online, cell E3 is telephone, and cell E4 is broker.

Example 9-3. Multiplying the commission by the trade and access type`commission paid = commission rate * limit_num * online`

Bond and option commissions typically fall into one of three categories:

- Zero
- If you don't purchase any bonds or options, of course you won't pay any commission.
- Minimum
- Many brokerages charge a minimum for ten or fewer bond or option contracts. You pay the same amount whether you buy one or ten.
- By number
- After ten bonds or options, you pay a dollar amount per bond or contract.

To accomplish this rate schedule, the bond and option commission formula looks like the one in Example 9-4. Whether you purchase zero bonds or more than ten bonds, the commission you pay is equal to the commission rate per bond multiplied by the number of bonds. However, if the number of bonds is between zero and ten, you pay the minimum. The interior IF function uses a nested OR function to check for a number of bonds equal to zero or greater than ten. In this spreadsheet, the commission equals the commission rate for a block of ten bonds multiplied by the number of bonds and then divided by ten to obtain the commission per bond. If the brokerages that you are considering calculate their commissions using another formula, you'll have to modify the formulas in the spreadsheet cells accordingly.

Example 9-4. A formula for calculating both the minimum commission and a commission by number of bonds or options```
bond commission (cell C18)
=IF(B18="na","na",IF(OR(bond_num=0,bond_num>10),B18*bond_num/10,B18))
```

Unless you can accurately predict how many trades you will make of each type and then accurately model the commission calculations for each brokerage you evaluate, it's impossible to compare commissions to the penny. Even a spreadsheet provides only a rough guide to your expenses. In addition, you'll notice that several of the brokerages in Figure 9-1 charge similar commissions. So, how do you put this information to use? It depends on how you trade.

If you place only four trades each for $10,000, the difference between BrownCo ($40) and Schwab ($119.80) is only $79.80—a mere 2/10 of a percent of the dollars that you're investing. In this case, the commission rates aren't a concern. Choose the broker that offers the services you want.

However, if you are just starting out and want to invest a few dollars each month in several different stocks, most of the commissions would overwhelm the dollars invested. As illustrated in Figure 9-2, an investor who places 6 market orders a month for a total of 72 trades in a year can face a huge difference in commissions. By buying companies on a regular schedule with Share- Builder, the investor pays only $152 in commission. BrownCo charges a very reasonable $400 for trades placed whenever you want. At the other end, the investor would pay $2,276 to Schwab for those 72 market orders.

When you trade somewhere in between these two examples and all the brokerages in your spreadsheet satisfy your service requirements, look for the brokerage with the lowest total commission. In this case, it's a good idea to add a few more rows to the spreadsheet to account for fees for maintenance, transferring assets, and issuing certificates. In some cases, high ancillary fees offset low commissions.

*Figure 9-2. Finding the winner when you trade frequently.*

*
Bonnie Biafore
writes about project management, personal finance, and investing. She's the author of Project 2007: The Missing Manual, as well as Online Investing Hacks, QuickBooks 2008: The Missing Manual, and Quicken 2008: The Missing Manual. Bonnie Biafore's blog can be found at http://projectsinpractice.com.
*

*
*

View catalog information for Online Investing Hacks

Return to O'Reilly Network.

Copyright © 2009 O'Reilly Media, Inc.