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.

Download Excel template (.xlsx) 117 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 6 Nov 2026

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.

Preview: the Example tab. Six fictional options, each investing $100,000 plus $500 a month for 25 years at an assumed 6% gross return.
$768,106zero-fee baseline after 25 years
$8,020cost of fees, Index ETF (0.05% a year)
$157,604cost of fees, Adviser + index funds (1.10% a year)
$149,584gap between the best and worst ending balance
Option (fictional)Balance after 25 yearsFees paidCost of fees*% of baselineRank
Index ETF
ER 0.05%
$760,086$4,584$8,0201.0%1
Index mutual fund
ER 0.15%
$744,308$13,573$23,7983.1%2
Active fund
ER 0.75%, $25/yr account fee
$655,354$63,291$112,75114.7%4
Load fund (A shares)
ER 0.85%, load 5.0%
$610,900$79,203$157,20620.5%5
Adviser + index funds
ER 0.10%, advisory 1.00%
$610,502$87,995$157,60420.5%6
Robo-style account
ER 0.08%, advisory 0.25%
$716,766$29,164$51,3406.7%3
Zero-fee baseline
same deposits, no fees
$768,106$0$00.0%—

*Cost of fees = zero-fee baseline − the option's ending balance: the fees paid plus the growth that money would have earned.

$0k$25k$50k$75k$100k$125k$150k$175kIndex ETF$8kIndex mutual fund$24kActive fund$113kLoad fund (A shares)$157kAdviser + index funds$158kRobo-style account$51k

Fees paidGrowth lost on those fees

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, the method in plain words, colour key, limits, sources, disclaimer
CompareStarting 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 feeAn 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
HoldingsUp to 50 holdings: ticker or name, market value, expense ratio; optional advisory fee on the accountTotal 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 holdingsAlready filled with fictional data (change any yellow cell)The same calculators, so you can see full results before entering your own
SEC checkNothingThe SEC investor bulletin's $100,000 / 4% / 20-year example, with a check block against the figures the SEC states

How to use it

  1. 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.
  2. 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.
  3. 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.
  4. Check that Status (B9) says OK, then read the results (rows 21–33) and the two charts. The highest ending balance is shaded green.
  5. 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 feeSEC bulletin: "approximately"SpreadsheetFees paidCost 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

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.

FieldFormula
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.

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 exampleIndex ETF: cost of feesActive fund: cost of feesAdviser + 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 funds0.40%
Bond mutual funds0.36%
Index equity ETFs0.14%
Index bond ETFs0.09%
Money market funds0.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

Limits

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

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.