Home › Templates › Expense ratio calculator spreadsheet
Expense Ratio Calculator Spreadsheet
A free investment fee calculator for Excel and Google Sheets. Enter up to six funds or portfolios with their expense ratio, advisory fee, front-end load and account fee, and it shows each one's balance year by year, the fees paid and the full cost of fees against a zero-fee baseline. A second tab works out your portfolio's weighted expense ratio.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up and no add-in: every formula is in the free file.
| Option (fictional) | Balance after 25 years | Fees paid | Cost of fees* | % of baseline | Rank |
|---|---|---|---|---|---|
| Index ETF ER 0.05% | $760,086 | $4,584 | $8,020 | 1.0% | 1 |
| Index mutual fund ER 0.15% | $744,308 | $13,573 | $23,798 | 3.1% | 2 |
| Active fund ER 0.75%, $25/yr account fee | $655,354 | $63,291 | $112,751 | 14.7% | 4 |
| Load fund (A shares) ER 0.85%, load 5.0% | $610,900 | $79,203 | $157,206 | 20.5% | 5 |
| Adviser + index funds ER 0.10%, advisory 1.00% | $610,502 | $87,995 | $157,604 | 20.5% | 6 |
| Robo-style account ER 0.08%, advisory 0.25% | $716,766 | $29,164 | $51,340 | 6.7% | 3 |
| Zero-fee baseline same deposits, no fees | $768,106 | $0 | $0 | 0.0% | — |
*Cost of fees = zero-fee baseline − the option's ending balance: the fees paid plus the growth that money would have earned.
Fees paidGrowth lost on those fees
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, the method in plain words, colour key, limits, sources, disclaimer |
| Compare | Starting balance, monthly contribution, gross return (your assumption), years (1–40); for up to 6 options: name, expense ratio, advisory or wrap fee, front-end load, yearly account fee | An input check; all-in yearly % fee; for each option and a zero-fee baseline: ending balance, money put in, loads, % fees and account fees paid, total fees, growth lost, cost of fees in $ and as % of the baseline, gap to the best option, rank; a 41-row yearly table (year 0 to 40); a line chart of balances and a stacked chart of fees paid vs growth lost |
| Holdings | Up to 50 holdings: ticker or name, market value, expense ratio; optional advisory fee on the account | Total value, weighting of each holding, weighted expense ratio (SUMPRODUCT), yearly cost in $ per holding and in total, all-in % and $, a flag for holdings missing an expense ratio, ICI 2025 averages for reference, a bar chart |
| Example, Example holdings | Already filled with fictional data (change any yellow cell) | The same calculators, so you can see full results before entering your own |
| SEC check | Nothing | The SEC investor bulletin's $100,000 / 4% / 20-year example, with a check block against the figures the SEC states |
How to use it
- Open Compare. Enter the starting balance, the monthly contribution (blank = 0), a gross return before fees and the number of years. The return is an assumption you choose, not a forecast; it is the same for every option so the difference you see is fees alone.
- For each fund or portfolio, type a name in row 12 (this switches the column on), then its expense ratio, any advisory or wrap fee, any front-end sales load and any yearly account fee. Type percentages as 0.5% or 0.005.
- Get the numbers from the fund's prospectus fee table ("Total Annual Fund Operating Expenses") and your adviser's or broker's fee schedule. FINRA's Fund Analyzer also lists fees for mutual funds and ETFs.
- Check that Status (B9) says OK, then read the results (rows 21–33) and the two charts. The highest ending balance is shaded green.
- Optional: on Holdings, list what you own. Copy the weighted expense ratio (B7) into an option on Compare to see what your current mix costs over time.
The SEC example, reproduced
The SEC's Office of Investor Education and Assistance shows how fee levels change a portfolio in its bulletin How Fees and Expenses Affect Your Investment Portfolio (23 July 2025): "the impact of three different fees on a $100,000 investment that grows 4% annually over 20 years." The SEC check tab runs the same inputs through the calculator:
| Annual fee | SEC bulletin: "approximately" | Spreadsheet | Fees paid | Cost vs no fee |
|---|---|---|---|---|
| 0.25% | $208,000 | $208,413 | $7,537 | $10,699 |
| 0.50% | $198,000 | $198,211 | $14,675 | $20,901 |
| 1.00% | $179,000 | $179,213 | $27,832 | $39,899 |
| No fee (baseline) | — | $219,112 | $0 | $0 |
All three round to the SEC's figures. They match when the fee is taken once a year from the balance after that year's growth: $100,000 × (1.04 × (1 − fee))20. The spreadsheet uses that same convention. Going from a 0.25% to a 1.00% fee costs $29,200 on this example, more than the $20,295 of extra fees, because the money paid out also stops compounding.
The formulas, explained
Each year, for each option
- Year 0: a front-end load comes off the starting balance. The SEC's own example: a $10,000 purchase with a 5% front-end load pays a $500 load, so "your remaining $9,500 is used to purchase shares" (Mutual Fund and ETF Fees and Expenses). Each year's contributions pay the same load.
- Growth: balance before fees = last year's balance × (1 + gross return) + 12 × monthly contribution × (1 − load) × (1 + gross return)0.5. Contributions arrive through the year, so they earn about half a year (a mid-year convention).
- % fees: (expense ratio + advisory fee) × balance before fees, taken at year end as in the SEC example. The SEC describes an advisory fee as "an ongoing annual fee based on the value of your portfolio".
- Account fee: a flat dollar fee, never more than what is left in the account.
- Baseline: the same deposits growing at the gross return with no load and no fees. Cost of fees = baseline − option; growth lost = cost of fees − fees paid.
Weighted expense ratio
Weighted expense ratio = SUMPRODUCT(market values, expense ratios) ÷ SUM(market values). Each fund's ratio counts in proportion to how much of the portfolio it is. Multiply by the total value to get the yearly fund expenses in dollars.
Exact formulas from the Compare tab (column B is option 1, row 37 is year 1). In the file each one sits inside IF($B$9="OK", IF(B$18=1, …, ""), "") so the sheet stays blank until the inputs are valid and the option has a name.
| Field | Formula |
|---|---|
| All-in yearly % fee (B17) | =IF(B$18=1,N(B13)+N(B14),"") |
| Year 0 balance after the load (B36) | =$B$5*(1-N(B$15)) |
| Year 1 balance before fees, helper (O37) | =B36*(1+$B$7)+12*N($B$6)*(1-N(B$15))*(1+$B$7)^0.5 |
| Year 1 % fees: expense ratio + advisory (U37) | =O37*N(B$17) |
| Year 1 account fee (AA37) | =MIN(N(B$16),MAX(0,O37-U37)) |
| Year 1 loads on contributions (AG37) | =12*N($B$6)*N(B$15) |
| Year 1 ending balance (B37) | =MAX(0,O37-U37-AA37) |
| Zero-fee baseline, year 1 (H37) | =H36*(1+$B$7)+12*N($B$6)*(1+$B$7)^0.5 |
| Ending balance at your years (B21) | =INDEX($B$36:$B$76,$B$8+1) |
| % fees paid to that year (B24) | =SUMIFS($U$36:$U$76,$A$36:$A$76,"<="&$B$8) |
| Cost of fees vs baseline (B28) | =$H$21-B21 |
| Growth lost on fees (B27) | =B28-B26 |
| Rank (B31) | =RANK(B21,$B$21:$G$21,0) |
| Weighted expense ratio (Holdings B7) | =IF(SUM($B$12:$B$61)>0,SUMPRODUCT($B$12:$B$61,$C$12:$C$61)/SUM($B$12:$B$61),"") |
| Yearly fund expenses in $ (Holdings E5) | =SUMPRODUCT($B$12:$B$61,$C$12:$C$61) |
Worked example from the sample data
The Example tab invests $100,000 today plus $500 a month ($250,000 in total) for 25 years at an assumed 6% gross return. All six options and their fees are fictional. With no fees at all, the money would grow to $768,106.
- Index ETF (0.05% expense ratio): ends at $760,086. Fees paid $4,584; cost of fees $8,020, 1.0% of the baseline.
- Index mutual fund (0.15%): ends at $744,308, $15,778 behind the ETF.
- Active fund (0.75% plus a $25 yearly account fee): pays $63,291 in fees and ends $112,751 below the baseline.
- Load fund (A shares) (5% front-end load on every purchase, 0.85% expense ratio): $12,500 in loads plus $66,703 in expenses. Cost of fees $157,206.
- Adviser + index funds (1.00% advisory fee on top of 0.10% fund expenses, 1.10% all-in): the highest cost here, $157,604, or 20.5% of what the same money could have grown to with no fees.
- Robo-style account (0.33% all-in): cost of fees $51,340.
In each case the growth lost is a large share of the cost: for the adviser option, $87,995 of fees paid plus $69,609 of growth lost. The example says nothing about which option would earn more before fees; advice, active management or a service can be worth paying for. The sheet only shows what each one costs.
What moves the answer
The same example with one input changed at a time (computed with the Python model that is checked against the spreadsheet cell by cell). Longer periods and higher returns make every fee more expensive in dollars.
| One change from the example | Index ETF: cost of fees | Active fund: cost of fees | Adviser + index funds: cost of fees |
|---|---|---|---|
| Example as shipped (6%, 25 years) | $8,020 (1.0%) | $112,751 (14.7%) | $157,604 (20.5%) |
| Gross return 4% instead of 6% | $5,216 (1.0%) | $73,574 (14.1%) | $102,717 (19.7%) |
| Gross return 8% instead of 6% | $12,315 (1.1%) | $172,692 (15.1%) | $241,617 (21.2%) |
| 10 years instead of 25 | $1,136 (0.4%) | $16,870 (6.5%) | $23,924 (9.2%) |
| 40 years instead of 25 | $33,485 (1.7%) | $447,899 (22.6%) | $614,975 (31.0%) |
| No monthly contribution | $5,333 (1.2%) | $74,857 (17.4%) | $103,685 (24.2%) |
Typical fund expense ratios
For context, the Investment Company Institute's report Trends in the Expenses and Fees of Funds, 2025 (25 March 2026) gives these asset-weighted average expense ratios for 2025. The Holdings tab lists them next to your own weighted figure.
| Fund type (US, 2025) | Asset-weighted average expense ratio |
|---|---|
| Equity mutual funds | 0.40% |
| Bond mutual funds | 0.36% |
| Index equity ETFs | 0.14% |
| Index bond ETFs | 0.09% |
| Money market funds | 0.24% |
Averages are not a target. Two funds with similar holdings can differ a lot in cost, so compare the actual funds you can buy. FINRA's tool lets you "select up to three funds to compare"; this sheet takes six, adds advisory and account fees, and is yours to keep.
Common mistakes
- Subtracting the expense ratio from a fund's published return. Fund expenses are already deducted from fund assets, so published returns are net of them. Enter a gross return assumption here, not a fund's past return.
- Adding the 12b-1 fee twice. The 12b-1 fee is part of the expense ratio in the prospectus fee table. Enter the total, not the parts.
- Forgetting fees outside the fund. Advisory, wrap and account fees are charged on top. The SEC notes that some "zero-expense" funds come with other costs, such as wrap program fees.
- Comparing fees on different returns. If one option gets a higher assumed return, the comparison is no longer about cost. Keep one gross return and test a range.
- Using the simple average for a portfolio. A 1% fund that is 5% of the portfolio matters less than a 0.2% fund that is 60%. Use the weighted figure.
- Ignoring loads on regular contributions. A front-end load applies to each new purchase unless a breakpoint discount applies.
Limits
- Annual steps. Funds accrue expenses daily; charging them once a year is a close but not exact match.
- The gross return is a constant assumption. Real returns vary year to year and can be negative.
- Not modelled: back-end loads (CDSC), breakpoint discounts, trading costs inside a fund, commissions, bid-ask spreads, taxes, fee changes over time, tiered advisory fees.
- One shared contribution and starting balance for all options; loads are a flat % of each purchase.
- Recalculation was tested in LibreOffice. The file uses only functions that exist in Excel 2016+ and Google Sheets.
FAQ
How do you calculate the cost of an expense ratio in dollars?
For one year, multiply the amount invested by the expense ratio: $10,000 in a fund with a 0.50% expense ratio costs about $50 that year. Over many years the cost grows with the balance, and every dollar paid in fees also stops earning a return. That is why the spreadsheet compares each option with a zero-fee baseline: the gap is the fees plus the growth they would have earned.
How do I calculate the weighted expense ratio of my portfolio in Excel?
List each holding's market value in one column and its expense ratio in the next. The weighted expense ratio is =SUMPRODUCT(values, expense_ratios)/SUM(values). In the fictional example on the Holdings tab, six funds worth $130,000 cost $231.50 a year in fund expenses, a weighted expense ratio of 0.178%.
How much does a 1% fee cost over 20 years?
The SEC's investor bulletin on fees shows $100,000 growing 4% a year for 20 years. With a 1.00% annual fee it ends at approximately $179,000; with 0.25% at approximately $208,000. The spreadsheet's SEC check tab reproduces those figures ($179,213 and $208,413 before rounding). With no fee at all the same money would reach $219,112.
What is a typical expense ratio for a mutual fund or ETF?
The Investment Company Institute reports asset-weighted averages for 2025 of 0.40% for equity mutual funds, 0.36% for bond mutual funds, 0.14% for index equity ETFs and 0.09% for index bond ETFs. Asset-weighted means large funds count more than small ones. Compare the funds you can actually buy, using the prospectus fee table or FINRA's Fund Analyzer.
Is the expense ratio already taken out of a fund's returns?
Yes. The SEC explains that fund operating expenses are deducted from the fund's assets and therefore lower your returns; you do not get a separate bill. So a fund's published returns are already net of its expense ratio. Advisory fees, wrap fees, account fees and sales loads are usually charged on top, which is why the spreadsheet adds them separately.
Does this expense ratio spreadsheet work in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. It uses only IF, AND, OR, MIN, MAX, SUM, SUMIFS, SUMPRODUCT, COUNT, COUNTIFS, INDEX, MATCH, RANK, ISNUMBER, INT, N, LEN, TRIM, ROUND, which exist in Excel 2016 and later, Google Sheets and LibreOffice. There are no macros.
Related tools and templates
- Investment trackerHoldings, cost basis and returns
- Compound interest spreadsheetHow savings grow over time
- Portfolio rebalancing spreadsheetBack to your target mix
- Retirement planner spreadsheetSavings, returns and withdrawals
- ROI calculatorReturn on an investment
- CAGR calculatorAnnual growth rate between two values
- All templatesFree spreadsheets
Educational template only, not investment advice or a recommendation of any fund, adviser or account. Example data is fictional: the options, fees, balances and the 6% return are made up to show the formulas and are not a forecast. The SEC example uses the SEC's own hypothetical inputs. Fee definitions are from SEC Investor.gov bulletins, fund comparison information from FINRA and average expense ratios from the Investment Company Institute, all retrieved on 6 November 2026. Check each fund's current prospectus before deciding.