I'm building a portfolio on Excel that includes the following data:

  • price at purchase
  • number of shares
  • commission/fees
  • current price

enter image description here

How should I calculate the impact of the Annual Expense Ratio to my P&L?

I understand from many sources on the web as well as questions here on SE that Annual Expense Ratio is deducted from the NAV on a daily basis.

Would I need to calculate the number of days since I purchased the ETF, pro-rate the Annual Expense Ratio and subtract from the Current Value field?

  • The net asset value (NAV) of a traditional (i.e. non-exchange-traded) mutual fund is net of the expense ratio, that is, the fund has deducted its charge for expenses before announcing the NAV at the end of the day, and that is the price at which all transaction requests for the day are executed. For an exchange-traded fund such as, you need to include bid/ask prices unless you want to do all your transactions on the Vanguard website and use the ETF as a traditional mutual fund with a very low expense ratio. Oct 14 '18 at 1:04
  • I'm able to pull via web service the most recent transaction price, would that suffice? Do you mean that then I wouldn't need to have a cell where the AER is calculated and then discounted?
    – porkfolio
    Oct 14 '18 at 11:06

If your objective is to compute returns and form a portfolio, you don't need to explicitly keep track of ETF fund expenses. Fund expenses change the NAV of the fund, which then affects the price people are willing to pay for the ETF. The only way money comes out of your pocket is if the price changes. In other words, fund expenses make the fund prices rise less quickly than the value of the associated benchmark index, and that is the only way they affect you.

With an ETF, you need to worry about (1) price changes (2) dividends paid to you (3) your transactions costs (4) your taxes. That is all. Anything else will be embedded in one of these.

  • thanks for clarifying, I thought that could be the case... and like your explanation. I have therefore been "double dipping" my expenses with ETF and naturally feel good now because the (incorrect) extra expense will be removed from my calculations and of course make me look "richer"... LOL
    – porkfolio
    Oct 14 '18 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.