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

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)

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

About the Author M Pattabiraman author of freefincal.comM. Pattabiraman(PhD) is the author and owner of freefincal.com.  He is an associate professor at the Indian Institute of Technology, Madras since Aug 2006. Pattu” as he is popularly known, 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. Pattu publishes unbiased, promotion-free research, analysis and holistic money management advice. Freefincal serves more than one million readers a year (2.5 million page views) with numbers based analysis on topical issues and has more than a 100 free calculators on different aspects of insurance and investment analysis. He conducts free money management sessions for corporates  and associations(see details below). Previous engagements include World Bank, RBI, BHEL, Asian Paints, TamilNadu Investors Association etc. Contact information: freefincal {at} Gmail {dot} com (sponsored posts or paid collaborations will not be entertained)
Want to conduct a sales-free "basics of money management" session in your office?
I conduct free seminars to employees or societies. Only the very basics and getting-started steps are discussed (no scary math):For example: How to define financial goals, how to save tax with a clear goal in mind; How to use a credit card for maximum benefit; When to buy a house; How to start investing; where to invest; how to invest for and after retirement etc. depending on the audience. If you are interested, you can contact me: freefincal [at] Gmail [dot] com. I can do the talk via conferencing software, so there is no cost for your company. If you want me to travel, you need to cover my airfare (I live in Chennai)

Connect with us on social media

Content Policy

Freefincal has original unbiased, conflict-of-interest-free,  topical reports, reviews, commentary and analysis on all aspects of personal finance like mutual funds, stocks, insurance etc. All guest authors and contributors to the site also do not have any conflict of interest. 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. No promotional content We do not accept sponsored posts and link exchange requests from content writers and agencies. This is our privacy policy Our website is non-profit in nature. The revenue from the advertisement will only be used for hosting charges, domain registration charges, specific plugins necessary for traffic growth and analytics services for search engine optimisation.

Do check out my books

You Can Be Rich Too with Goal-Based Investing

You can be rich too with goal based investingMy first 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 WantGamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you wantMy second 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

The ultimate guide to travel by Pranav Surya

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

Free Apps for your Android Phone

All calculators from our book, “You can be Rich Too” are now available on Google Play!
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)

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.


  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 *