O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  

Buy the book!
Online Investing Hacks
By Bonnie Biafore
June 2004
More Info

Calculate the Damage of Mutual Fund Expenses
Calculate the hard cash you pay to own a fund to determine whether its expenses are worth the price
[Discuss (0) | Link to this hack]

Mutual funds with high expenses can put a serious hurt on your investment returns . But how do you determine just how bad the damage is? Some web sites include estimated annual fees and compare a fund's costs to the average for the fund category, but that's just the beginning. The bottom line is how much more money would you have, if you didn't have to pay those darned fees? If you're trying to decide between several funds with similar investment philosophies, comparing your ending balance after several years, taking into account fund return and expenses, can help. You can use built-in Excel functions to calculate the potential cost of ownership and your bottom line return.

To calculate the true cost of mutual fund expenses, you need only a few items of data, shown in highlighted cells at the top of the spreadsheet in . You must enter the value of your initial investment and the number of years you expect to own the fund. Whether you're trying to decide which fund to buy or looking to replace a fund you already own, enter the number of dollars that you're ready to invest as the initial investment (cell B2 in the example) to obtain the most accurate picture of costs and their effect. If your mutual funds are for retirement, you might hold them for decades. If you invest in funds to pay for your kids' college education, your timeframe might be five to ten years.

The expected annualized return in cell B4 is how much you expect the fund value to increase on average for each year that you own the fund. A higher annual return makes your investment grow, which in turn increases your fund costs. What number should you use? It depends on how conservative you are. Because very few funds beat the market over the long term, you can use a long-term average return for a comparable index, such as the ten-year return for the Russell Mid-cap index for a fund that invests in mid-cap stocks. However, if you think a fund will beat the market, because it has for a long time or you just have an inkling, you can use the fund's long-term annual return, such as the ten-year return or, even better, its annualized return since its inception, which is from the first day the fund existed. The best place to find these numbers is at the fund web site, which typically includes annual returns for the fund for one year, three years, five years, ten years, and since inception, if that's longer than ten years. Web sites often show returns for comparable indexes or fund categories for similar time periods.

Figure 1. Build a spreadsheet that calculates mutual fund expenses and shows their effect on your investment

You can type in the values for the four types of mutual fund expenses in cells B7 through B10 or capture fund expenses automatically . The expenses fall into three categories: annual expenses, up-front expenses, and expenses you pay when you sell. If a fund charges a fee with a name you don't recognize, find out when you pay the fee. If it's an annual expense, add it to the expense ratio. If you pay it up front or when you sell, add it to the front-end sales load number or the deferred sales load percentage, respectively.

Calculating Mutual Fund Costs and Return

Now, let's see how these expenses affect investment return by walking through the calculations for Intermediate Values and Results in the spreadsheet in .

Front-end sales loads are painful because they take money out of your investment before you even buy any shares. At least they're easy to calculate, as shown in Examples and .

The actual return (annualized) in cell B15 is the return that you receive after subtracting the annual expenses you pay. It's not as simple as subtracting the total expense ratio from the expected annualized return, and here's why: at the end of the first year, your investment increases by the expected annualized return (1 + expected return) but is then reduced by the expense ratio (1 - total expense ratio). shows the formula for your investment value at the end of the year.

The actual return is the percentage increase over your investment at the beginning of the year, as shown in .

By substituting the formula in into the actual return equation in and dusting off your algebra skills, you will end up with the formula for actual return in .

To calculate your investment value at the end of the holding period but before you pay any deferred fees or loads, multiply your funds invested by (1 + actual return) for every year you own the fund, which is the equivalent of the Excel formula in .


In Excel, the ^ operator performs the same calculation as the POWER function, but uses the format (1 + Actual Return)^Holding Period.

You must subtract any deferred sales load or redemption fees to obtain the true final value of your investment. The deferred sales load depends on whether your fund increased or decreased in value. If the fund increased, you pay the deferred sales load percentage on your initial investment. If the fund decreased, the deferred sales load is based on the final investment value. You can use the Excel MIN function to multiply the deferred sales load percentage by the smaller of the two numbers, as shown in .

Finally, to determine the true cost of mutual fund fees, compare the true final value you receive from a fund to the money you would have if you invested your entire initial investment and achieved the expected annual return. You calculate the investment value with no fees subtracted (cell B22) like the final value before subtracting the deferred sales load in , except that you use the expected return instead of the actual return, as shown in Examples and .


If you check the example in , you'll notice that a 2.5 percent front-end load, a 0.5 percent deferred load, and an annual expense ratio of 1 percent leads to total mutual fund costs equal to 12 percent of the investment value you would receive if you paid no fees.

Using a Geometric Series to Calculate Regular Deductions or Contributions

Although the investment value changes each year, you calculate expenses by multiplying the balance by the total expense ratio. Each year, you multiply the previous balance by the total expense ratio (er), which means that you can apply a geometric series to the problem. A geometric series calculates the results of adding up powers of numbers, such as:

Geometric Series = 1 + z + z2 + z3 + ... + zn

However, you can transform this calculator nightmare into a formula, which might not be any easier to read but is easier to calculate when you make Excel do it for you.

Suppose you want to calculate a geometric series for n years, starting with an investment P, using an annual growth rate r, deducting a percentage for expenses er each year. By simplifying the annual multiplier (1 + r)*(1 - er) to the geometric series term z, the basic formula for annual fees paid is:

Fees(n) = P * (1 + r) * (er) * (zn - 1)/(z - 1)

You can learn more about the math behind a geometric series at http://www.moneychimp.com/articles/finworks/fmgeom.htm.

You can also calculate the loads and fees you must pay. The loads paid are straightforward because they occur only once—you add up the front-end load and deferred sales loads. The annual fees are a little different, because the fund company applies the expense ratio percentage to the value of your investment at the end of every year.

You must consider the increase in value from a mutual fund's return before you deduct the expenses, so the formulas for calculating your annual and total fees are shown in Examples and . To simplify the calculations, this example assumes that the fund increases by the same amount each year.

There's one last cost of mutual funds that you won't ever see in mutual fund cost estimates: the earnings you lose due to fees and loads. When you pay fees and loads, that's money that you could have invested to make more money. The only way to see this hidden cost is to calculate it in Excel using the formula in or the SEC Online Calculator.

Using the SEC Online Mutual Fund Cost Calculator

If you don't want to build a spreadsheet to calculate mutual fund costs, you can use the SEC's Online Cost Calculator at http://edgar.sec.gov/investor/tools/mfcc/mfcc-int.htm. If you plan to evaluate several mutual funds, the data entry is tedious but very easy. Click the Run the JavaScript SEC Cost Calculator link. You can read the SEC's introduction to the effect of mutual fund expenses or go directly to the calculations by clicking the "Click here to skip the introduction" link. Type or select one value on each page of the calculator and press Enter to progress to the next page. The calculator requires the number of years you plan to own the fund, your initial investment in dollars, the type of fund, the average annual return, the name of the fund to identify the results when you print them, the sales charge, the deferred sales charge, whether the fund converts to one class of share to another, and the total annual operating expenses.

--Bonnie Biafore and Amy Crane, with finer points of mathematics provided by Jim Thomas

O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.