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)

 

Create a "from start to finish" financial plan with this free robo advisory software template


Free Apps for your Android Phone

Install Financial Freedom App! (Google Play Store)


Install Freefincal Retirement Planner App! (Google Play Store)


Find out if you have enough to say "FU" to your employer (Google Play Store)


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.

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

  1. 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 )

      1. Hi Pattu,

        Great Work 🙂
        Can i have a “Excel-based Mutual Fund Portfolio Tracker” with 20 sheets?

  2. 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 !

    1. 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.

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

    1. 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.

  4. 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

    1. 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.

  5. 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.

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

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

  7. 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 !

    1. 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.

  8. 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

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

  9. many many thanks for great job . How to entry bonus of uti ulip fund into transaction sheet . plzz help me , thank u

  10. Hello sir i am using

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

    Following MFS need your attention, what do i do to rectify thigngs

    Birla Sun Life Advantage Fund – Growth – Direct Plan
    NAV IN EXCEL 1123.23 WHICH IS WRONG
    ACTUAL NAV IS 413.35

    Icici Prudential Multicap Fund – Direct Plan – Growth
    NAV IN EXCEL IS 575.26
    ACTUAL NAV IS 263.79

    Mirae Asset India Opportunities Fund – Direct Plan – Growth
    NAV IN EXCEL IS 1089.69
    ACTUAL NAV IS 43.825

    Raj

  11. Sir i am having problem with date, you know how it says As on 04/09/17 NAV is 41.57 (This on top left hand corner. This date is coming as 14/01/16. This is coming after enabling Macros & connecting to internet. Check at your end

Do let us know what you think about the article