Using Excel’s future value (fv) function to make a case for ETF’s

Excel function tutorials

In recent months, we have often had discussions about ETF’s and they are gaining a lot of traction all around the world as a better way to manage finances. There are many differences between ETF’s and more traditional mutual funds but by far the most important one is the difference in fees for the investor. The most important fee when discussing long term investments is without any doubt the MER, the management fees that will be charged to the investor every year until he decides to get out of the investment. ETF’s usually charge a fee between 0.05% and 1% while mutual funds typically charge between 1-3%. It’s probably say to say that the average difference between the two is close to 1.50% in annual fees.

Is It That Simple?

Not quite no as another difference between the two is the fees that are charged when entering and exiting your position. Since ETF’s are traded on an exchange, investors end up paying commissions while for mutual funds it is a bit more complex as some funds charge for entry, exit and those fees can depend on the length of the investment among many other criteria. I would however say that in general the entry fee might be more important on mutual funds. Is it significant? Not if you end up paying less every year for a decade, two or even more. While the difference will vary quite a bit from one investor to another, I think it’s fair to assume that in general an investor using ETF’s over mutual funds will end up saving 1% or so on average per year.

Is 1% That Much Of A Difference?

I hear the question loud and clear but I think that instead of discussing the importance of fees over time, their effect on the compound return, I thought I would do an example in excel. I will paste the detailed tables to start off and then the much more simple use of the “future value” function. Let’s start with assumptions.

Judy has $100,000 in investments, expects to generate 6% per year, will invest $12,000 per year and will retire 35 years from now. How much money does she expect to have by then? Here is a simplified spreadsheet:

Over $2.1 million! Impressive isn’t it? Now let’s assume she could keep the same return with ETF’s but save 1% annually on fees, here is the result:

Over $2.7 million, a difference of over $600,000!!! That is an incredible difference and is a great example of why those 1% of fees matter a lot more than you could possibly imagine. I don’t think I need to go much deeper into the reasons why but i did want to show off a much easier way to compare the two by using Excel’s Future Value (FV) function. It’s easy, much cleaner and easier to play around. Here is an example of the same comparison using the FV function:

Only the two last lines have formulas as they use the FV function. The formula in B7 is:

B7: = FV(B3,B5,-B2,-B1)

And I calculated the same with an added 1% of return by doing the following:

B9: = FV(B3+0.01,B5,-B2,-B1)


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


One Feedback on "Using Excel’s future value (fv) function to make a case for ETF’s"

Why It Makes Sense To Build Your Own ETF Portfolio «

[…] had published a post on one of our main blogs recently where we calculated the impact of saving 1% in fees annually. Of course, depending on how much money you have to invest, the impact of 1% will be significantly […]