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.


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


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

Do check out my books

You Can Be Rich Too with Goal-Based InvestingYou can be rich too with goal based investing

My 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 customg solutions for your lifestye! 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 want
My 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 youngearner

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)

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

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)

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.

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


    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


    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.


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


  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

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

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

  14. dear sir iam used to this excel sheet please share me the working url i can download the complete nav

Comments are closed.