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

Published: May 13, 2013 at 6:13 pm

Last Updated on

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.


  • 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)

Join our 1300+ Facebook Group on Portfolio Management! Losing sleep over the markt crash? Don't! You can now reduce fear, doubt and uncertainty while investing for your financial goals! Sign up for our lectures on goal-based portfolio management and join our exclusive Facebook Community

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)


Do share if you found this useful
Join our 1300+ Facebook Group on Portfolio Management! Do not lose sleep over your bleeding portfolio: Learn how to reduce fear, doubt and uncertainty while investing for financial goals! Sign up for our lectures on goal-based portfolio management and join our exclusive Facebook Community

Hate ads but would like to support the site? Subscribe to our ad-free newsletter and get beautifully formatted full articles delivered to your inbox!

About the Author

Pattabiraman editor freefincalM. Pattabiraman(PhD) is the founder, managing editor and primary author of freefincal. He is an associate professor at the Indian Institute of Technology, Madras. since Aug 2006. Connect with him via Twitter or Linkedin Pattabiraman has co-authored two print-books, You can be rich too with goal-based investing (CNBC TV18) and Gamechanger and seven other free e-books on various topics of money management. He is a patron and co-founder of “Fee-only India” an organisation to promote unbiased, commission-free investment advice.
He conducts free money management sessions for corporates and associations on the basis of money management. Previous engagements include World Bank, RBI, BHEL, Asian Paints, Cognizant, Madras Atomic Power Station, Honeywell, Tamil Nadu Investors Association. For speaking engagements write to pattu [at] freefincal [dot] com

About freefincal & its content policy

Freefincal is a News Media Organization dedicated to providing original analysis, reports, reviews and insights on developments in mutual funds, stocks, investing, retirement and personal finance. We do so without conflict of interest and bias. We operate in a non-profit manner. All revenue is used only for expenses and for the future growth of the site. Follow us on Google News
Freefincal serves more than one million readers a year (2.5 million page views) with articles based only on factual information and detailed analysis by its authors. All statements made will be verified from credible and knowledgeable sources before publication.Freefincal does not publish any kind of paid articles, promotions or PR, satire or opinions without data. All opinions presented will only be inferences backed by verifiable, reproducible evidence/data. Contact information: letters {at} freefincal {dot} com (sponsored posts or paid collaborations will not be entertained)

Connect with us on social media

Our Publications

You Can Be Rich Too with Goal-Based Investing

You can be rich too with goal based investingThis book is meant to help you ask the right questions, seek the right answers and since it comes with nine online calculators, you can also create custom solutions for your lifestyle! Get it now. It is also available in Kindle format.

Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You Want

Gamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you wantThis book is meant for young earners to get their basics right from day one! It will also help you travel to exotic places at low cost! Get it or gift it to a young earner

Your Ultimate Guide to Travel


This is a deep dive analysis into vacation planning, finding cheap flights, budget accommodation, what to do when traveling, how traveling slowly is better financially and psychologically with links to the web pages and hand-holding at every step. Get the pdf for Rs 199 (instant download) 

Free Apps for your Android Phone

Comment Policy

Your thoughts are the driving force behind our work. We welcome criticism and differing opinions.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.


  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.


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

  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


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

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


    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

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

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


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

  11. Brilliant work on the spreadsheet. Quick question though. Nav2 tab does not update the ‘Scheme Name’. Reading the macro and the .txt file, it seems there are 8 attributes but for some reason the Scheme name is not getting picked up by the macro. Any advice?

  12. sir please check , excel is not pulling out correct date from AMFI website, it stopped at 14/12/2017. Please advise

Leave a Reply

Your email address will not be published. Required fields are marked *