Excel base mutual fund portfolio tracker

Would you like to spend next 5 minutes learning how to create an excel sheet to track your mutual fund portfolio?

click here to download mutual fund portfolio tracker excel sheet I have created and play with it.

NOTE: I have updated the sheet to fix a formula error, download it again if you need to.

We will use 2 simple excel features to achieve this - web queries and vlookup()
[click here to learn more about web queries in excel]

  1. First, lets put a tabular format for our portfolio: We can have fund name, # of units, purchase NAV (Net Asset Value, the cost of unit for your when you bought it), purchase date, total value at purchase (units * purchase NAV), current NAV (we will pull this data from internet), value as of now (units * current NAV), Profit / loss amount and profit / loss % as our table columns. Once you learn how to do this, you can add more columns depending on what / how you want to track your MF portfolio.

    When you finish creating the table, it would look something like this:
    excel spreadsheet mf portfolio tracker table format

  2. Next, we will use web-queries to load the fund-names and the corresponding latest NAVs in a separate sheet. I have queried Association of Mutual Funds India [AMFI] - Latest Mutual fund NAV page since all my investments are in India. If you are in US or some other country you can query corresponding fund house / financial info aggregator sites (like google finance) to get the data. Remember to set “Refresh data on file open” on to get fresh data whenever you open the your tracker excel sheet.

    Since AMFI returns data in a text file with ; as delimiter, I had to parse the fund names and navs out of it using a combination of search(), left() and mid(). I will not get in to the details of how its done since you may have to process your data differently depending on source.

    Finally when the processing is done, we will have a table in the second sheet with all fund names and latest navs.

  3. Now, all we have to do is create lookup formulas (well just vlookup()) to get the latest NAV to our tracker table based on the entered fund name.
    • Assuming the fund name in which you invested is in cell “c1″,
    • Assuming the fund data is in table “sheet1!c1:d6000″ with “column c” containing the fund name and “column d” containing latest NAV,

    The formula for latest NAV can look like this:

    =vlookup(c1,sheet1!c1:d6000,2,false)

    Remember to use false for last parameter since fund names may not be sorted in alphabetical order on your source web page.

    Now we will repeat this formula for all the rows in latest nav column. I have built my portfolio tracker to track 20 funds at a time. Also, you can simplify formulas using named ranges.

  4. Finally we will write formulas for,

    current value = latest nav * units held
    profit/loss = current value - purchase value
    profit/loss % = “profit/loss” / purchase value

    You can add some conditional formatting to beautify the table (like turning text blue for profits and red for losses etc.)

  5. Thats all, you have now created a real-time mf portfolio tracker. It would look something like this when done:
    mutualfund portfolio tracker excel sheet
    You can do the same for stock portfolios, commodities etc. You just need a web source that gives you latest data and five minutes of free time

Feel free to download mutual fund portfolio tracker excel sheet I have created and play with it.

Few ideas on how you can enhance this:

  • Add graphs to see visually how the funds are doing
  • Build some VBA to store previous NAV values of your funds so that you can see historical dates
  • Instead of doing plain % of profit / loss, compute realistic growth of your funds using date of purchase, risk free rate of return etc.

Did you enjoy reading this post? If so, give me some love

Subscribe to Pointy Haired Dilbert - Chandoo.org - RSS FeedSave to Delicious by bookmarking this post Stumble this post  

Consider subscribing to my newsletter every weekday I will send you one email with hot excel tips, technology tidbits or business insights that can make you more productive. Each mail will have unsubscribe link so you can stop receiving the mails at any time you wish.