Excel-based Mutual Fund Portfolio Returns Tracker (Version 1)

Use this excel file to track returns from your SIP and lump sum mutual fund holdings. I have had several requests for making something like this. Didn't do much about it until Mr. Vijay Hegde sent me his tracker file for inputs. I got inspired by it and decided to build one from the ground up with no resemblance to existing trackers.

UPDATE: March 2014: Automated Mutual Fund and Financial Goal Tracker is now available for download.

Features/How to Use (File contains step-by-step instructions)

  • Like all excel trackers this also can source daily NAV information from the AMFI website. I would like to think the resemblance ends there.
  • I have focused on 'average return' or technically known as 'Compounded Annual Growth Rate' (CAGR) of the holdings. The idea is to 'input' minimum information and explicitly discourage users from updating NAV everyday!
  • Each MF holding can be (or has to be!) entered in a separate worksheet. The file has a total of 10 such sheets: 3 of one kind (Pure SIP) and 7 of another (Mixed).
  • Pure SIP: If you have a SIP in a growth MF with no redemption and lump sum transaction history and would like to keep it that way in the future then lets call it a Pure SIP. The tracker file can handle 3 such SIPs (you can make more yourselfor I can help you do it). The advantage of such a Pure SIP is that transactions that occurred in the past need not be entered. You would need to know the value of your holding on some day and the corresponding NAV value. All future transactions (NAV value) will have to be logged. You can do it once a month using an SMS alert from the AMC or distributor or once in a few months using the account statement.

mft

  • A more intelligent way of using a Pure SIP sheet is not to enter any SIP transactions! Every few months one could enter the current value and get the CAGR. The CAGR calculation assumes SIP transactions are separated by 30 days. This separation depends on randomly occurring non-business days and can range from 28-33 days. So while Excel's XIRR tool is the correct one to use, Excels' Rate gives a very close answer.
  • Mixed: A Mixed MF holding is one in which all kind of transactions have occurred in the past and is likely to occur in the future. That is a SIP combined with occasional lump sum investments, redemption's dividends etc. The tracker file can handle 7 such investments. All transactions (past/future) have to be entered for getting the correct CAGR. There is an option not to enter past returns, but it will not yield the correct CAGR.
  • You can use the file in many ways. For example if you have a SIP in a growth MF and make occasional lump sum investments you choose to either enter all the transactions in a Mixed sheet or enter SIP details in a Pure SIP sheet and lump sum investments in a Mixed sheet.
  • Thus the focus is on computing CAGR and minimizing data entry and (advice against) constant monitoring. So it is an offline tracker with occasional online data query.
  • A summary sheet with gives the % equity and debt holdings and the average weighted CAGR return is also provided. The holdings break up can be used to check if rebalancing is necessary or not.

Why Version 1? Hopefully future versions will include a number of features (some suggested by Vijay Hegde) like

  • auto-obtain historical NAV for a particular date
  • auto-obtain SIP value
  • comparison with benchmarks (suggested by Vijay) to assess fund performance.
  • FIFO logic for units (suggested by Vijay) redeemed for capital gain calculations
  • Anything else that you can think of.

Statutory Warning: Refreshing NAV everyday and staring at MF holdings can be injurious to your fiscal health 

I would be delighted to hear your feedback. Suggestions for improvement are welcome.

UPDATE: March 2014: Automated Mutual Fund and Financial Goal Tracker is now available for download.

Version 1.2 Download the Mutual Fund Excel Tracker (Dec 2013)

CAGR calculation for dividends has been modified. Dividends are no longer assumed to be reinvested. If you have reinvested the dividends you will have explicit enter this as a new transation. Not comfortable about calulating CAGR this way, although everyone seems to be following this. Need to think/read more about this.

Note: For dividend transaction enter the dividend rate (eg. Rs. 2 per unit) in the NAV entry.

Version 1.1 Download the Mutual Fund Excel Tracker (Oct 2013)

(includes guess option for XIRR and more MF transactions. Thanks to feedback from Anil Kumar)

Note: For dividend transaction enter the dividend rate (eg. Rs. 2 per unit) in the NAV entry.

Version 1.0 Download the Mutual Fund Excel Tracker (May 2013)

 

Install Financial Freedom App! (Google Play Store)

Install Freefincal Retirement Planner App! (Google Play Store)

book-footer

Buy our New Book!

You Can Be Rich With Goal-based Investing A book by  P V Subramanyam (subramoney.com) & M Pattabiraman. Hard bound. Price: Rs. 399/- and Kindle Rs. 349/-. Read more about the book and pre-order now!
Practical advice + calculators for you to develop personalised investment solutions

Thank you for reading. You may also like

About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete  the entire comment or remove the links before approving them.

24 thoughts on “Excel-based Mutual Fund Portfolio Returns Tracker (Version 1)

  1. SS.Kadam@nic.co.in

    Dear Pattu,

    Received your mf portfolio tracker. however, you have not made any provision for adding more sheets in the workbook. Please also let us know the procedure for adding more mutual funds and sheets.

    regards,

    Sudhir S. Kadam A.M., please do not print this mail unless absolutely necessary. ( Save Trees Save Environment )

    Reply
  2. vandhana

    I am yet to use this fully. One quick doubt - is there any way to keep the number of funds dynamic? maybe use a macro to generate as many sheets as required or keep individual sheets for each fund and then use a macro to collate the data? - most ppl would have accumulated a whole bunch of MFs over a period of time ! Will come bac with more comments after I fully input all my data and see how it works !

    Reply
    1. pattu

      More fund sheets will make the file bully and slow. Can do this. will have a single sheet for discontinued holdings. Will look forward to your feedback. Thanks.

      Reply
  3. Shivanand

    Thank you for nice calculator. But 'Refresh Nav Values' do not refresh the navs. It clears out all the navs. Can you please check.

    Reply
    1. pattu

      HI Shivananad,

      AMFI, in the last couple of days changed their address from http://www.AMFI.com to portal.AMFI.com. Hence the trouble. I have corrected this now and have reuploaded the file. Please have a look. Thank you very much for pointing this out.

      Reply
  4. rakesh

    Dear Pattu,
    is there any website or software , which will take care of my MF portfolio online, i.e. import all MF transactions including daily dividend / dividend reinvestment of mutual funds
    or is there any possible in excel to add auto reinvestment of dividend in MF units

    Regrads
    Vishal

    Reply
    1. pattu

      I think Mprofit does this. Perhaps even Perfios/moneycontrol.
      As for excel, I just checked my latest tracker. It can be done, however since the dividend rate will vary auto-entry may not work. In any case if you share with your account statement, just the entries alone copied in an excel sheet, I will see what I can do.

      Reply
  5. S.Vaithyanathan

    Recently I was wondering how to calculate the capital gains wrt FD Vs MF. I landed in this freefincal. Extremely good. Please keep up this work. Sometimes for a lay investor like me alpha, beta are fearsome. But the step-by-step approach is exemplary and we can understand.

    Reply
  6. Subroto

    How do I enter fresh mutual funds could not enter MORGAN STANLEY MULTI ASSET FUND – PLAN A – QTRLY DIV PAYOUT

    Reply
    1. pattu

      I will be releasing an automated version of this tracker in the next few days. Stay tuned! You can enter it there.

      Reply
  7. Michael Kelly

    Hi. Thanks for the excellent work on the Mutual Fund tracker. Its all great but I wonder if its possible to input Hong Kong based MF's rather than just Indian. I plan on adding various MF's from around the world but it seems that your tracker is only for Indian funds. Or am I missing something here? I would appreciate your reply. Thanks !

    Reply
    1. pattu

      Yes it is only for Indian MFs. However it is fully open source. Suggest you check out some relevant resource related to HK mfs and modify the code.

      Reply
  8. Robin Sie

    HI,
    Excellent work to get all the MF data and track I will like to track funds from US I am not a pro but can you tell me were I can make change from http://www.AMFI.com to Fundlibrary.com to get the data.

    Thanks
    Robin

    Reply
    1. freefincal

      Let me see if I can retrieve them. Point me to urs which give you NAV history of each fund and each days NAV

      Reply

Do let us know what you think about the article