ER Comparison Spreadsheet Help

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

ER Comparison Spreadsheet Help

Post by Ducks » Fri May 23, 2008 2:38 pm

I found this page on how to make an Excel spreadsheet to compare expense ratios in different funds:

http://www.oreilly.com/pub/h/1889

The only thing is that the very last line -- "Earnings lost due to fees and loads paid" -- there are no instructions on how to do the formula, and I can't figure out what formula they used. Anybody know?

Also - anything else you could think to add to this spreadsheet?

I'd be happy to share it when it's completed.
Getting our Ducks in a row since 2008.

User avatar
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA
Contact:

Post by mattman22 » Sat May 24, 2008 7:47 pm

I tried all different ways to try and calculate that number, but I couldn't get it. Sorry! I was thinking it was the amount that could have been gained on the total expenses if they had seen a return, but that doesn't seem to be the case.

Hopefully someone else sees it.

User avatar
Topic Author
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Post by Ducks » Mon May 26, 2008 11:32 pm

Matt - thanks for looking into this for me. Since neither one of us can figure it out... I'm going to assume that this last line is inconsequential. ;)

I'm planning on using this spreadsheet to convince my husband's company that ERs really do make a difference, and they really should get a better plan. The "Investing Hack" spreadsheet assumes an initial investment of $10,000, and no further contributions. I'd like to design the spreadsheet to more closely mimic what happens with the 401(k) or other retirement investments. For example, my husband's company matches 50% up to 6% of salary. I would imagine most people who contribute contribute about 6% to take advantage of the match. So the Investment Data area of the spreadsheet might look something like this:

Investor's Starting Age: 25
Investor's Retirement Age: 60
Years Until Retirement: (60-25)
Initial Annual Salary: $X
Initial Annual Investment: (6%*$X)
Company Match: (50%*((6%*$X))
Total Annual 401(k) Investment: $Y
Annual 401(k) increase %: (COLA?)
Expected Return (annualized):

And then the same info from "Expenses" on down as shown in the Hacks spreadsheet.

I think this tool might be really helpful for those of us (most of us!) in crappy 401(k) plans. Imagine being able to give TPTB a spreadsheet directly comparing your company's crappy "lifestyle" plans to Vanguard's .2% target retirement plans, and showing just what the numbers mean to the bottom line. It'd be a sales tool, absolutely. The problem is, I can't figure out how to get Excel to tell me what I want to know. Any ideas on how to make this work?

Also - I saw in the other thread that you are working on an AA Spreadsheet. I have one that you are welcome to take a look at. If you PM me your email address, I will mail it to you. Us Excel geeks gotta stick together. ;)
Getting our Ducks in a row since 2008.

User avatar
Topic Author
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Post by Ducks » Tue May 27, 2008 1:22 pm

In case anybody else wants to fiddle with this spreadsheet, I've uploaded a draft to my webspace. It is an Excel 2007 file with the .xlsx extension. If there is interest I can re-format it for other versions of Excel.

Fund Comparison Spreadsheet
Getting our Ducks in a row since 2008.

User avatar
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA
Contact:

Post by mattman22 » Tue May 27, 2008 6:23 pm

This is very useful. Great tool!

However, does it add the 6% of salary per year to the final value? It doesn't seem to that for me. All it calculated was the return on the initial investment.

Matt

User avatar
Topic Author
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Post by Ducks » Tue May 27, 2008 7:05 pm

Not yet. I haven't quite figured out how to make the math work. :oops:

I'm frustrated with Excel 2007. It's sooo different from the OfficeXP version, and I'm having trouble figuring out the nuances of the new program. Every time I try to fix the formula I get caught up in formatting issues and start mentally spewing expletives at the screen. I'll go back to it again after a break.
Getting our Ducks in a row since 2008.

Post Reply