Automated Mutual Fund Performance Tracker

Free Mutual Fund Performance Tracker that is fully automated and based on Excel.  Financial goals can also be tracked and analyzed.

Would you like to

  • track the performance of your mutual fund (open-ended & closed-ended) investments with minimal inputs?
  • tag financial goals to your mutual fund holdings and track the net portfolio returns?
  • use a tracker that automatically updates financial goal calculators like retirement, child’s education etc. with your fund holdings and provide insights on current value?
  • use a tracker that helps you plan for future redemptions by taking into account lock-periods and exit loads for calculating short-term and long-term capital gains?
  • graphically track your fund holdings and goal corpuses?
  • compare the performance of your funds  with other mutual funds, index funds, ETFs?
  • monitor the history of your fund returns (CAGR)?
  • use a tracker that doubles as a fund analyser for existing holdings or for new purchases?

Interested?  Read on …

History

I started investing in mutual funds on 19th June 2008 (at age 33). Just after the start of the financial crisis. I was clueless about what was happening in the world around me. I started with a small SIP in Sundaram Tax Saver ELSS fund dividend option.  I did not know what a dividend meant until I received one!

Today my monthly investments in equity mutual funds (excluding NPS) have increased by 42 times! In part because I am now comfortable with equity, and in part because I invest more than I spend.

As you can imagine, I not only have a reasonable history of continuous investing, I also have funds tagged with each of my goals.

Direct mutual funds have compounded my troubles! On Jan. 1st 2013, the number of funds I held doubled! HDFC Top 200 became HDFC Top 200 Regular plan and HDFC Top 200 Direct Plan and so on.

The reason for this was, with the exception of the Sundaram ELSSS (done through an LIC agent!! I had asked him ‘where to invest’!) I was investing directly with the AMC! I was going direct (unknowingly!) before trail commission-free direct plans were introduced.

The point is that I found it very difficult to manage my folio. To this day I keep trimming down the folio: Gradually exiting my ELSS SIP (big mistake!), switching regular plans to direct ones gradually.

Up to the middle of 2012, I was using the tracker from Moneysights. Which was quite good. When it shut down, I moved to Moneycontrol, tried the older version of VROnline, tried the free version of Perfios, unsuccessfully tried to install MProfit (it was not free then).

InitiallyI did not know how to even enter a transaction and would often get it wrong. It took a while until I figured it out in MoneyControl. Somehow, I was unhappy with the all trackers I used and always wanted to build one for myself.

Since I didn't know how to do this, I settled down with Moneycontrol, painfully revamping my folio after direct plans came up.

After the blog  came up, I started receiving requests for making one. After some prodding by Mr. Vijay Hegde, I published a manual tracker.  It is ‘manual’ in the sense that one will have to enter the NAV/price corresponding to the date of purchase.

It was an instant hit! I then realised that though there were so many online options available there is a strong demand for Excel based personalised tracking.

Data entry is a pain with online trackers.  You will need to enter the NAV/price or use a ‘get price’ button for many of them.  Correcting and deleting entries was also a pain.

I wanted to make this simpler.  With time, I had a decent idea of what a mutual fund and financial goal tracker should be.

I started work on this in Dec. 2013 soon after I finished the integrated financial plan creator. The tracker has gone through 17 versions and 3 rounds of beta testing by readers. I will acknowledge their individual contributions in a subsequent post.

Some of them simply tore apart the tracker and exposed huge errors. I almost gave up the project twice.  Thankfully I have been able to keep it together and find a way out. All the errors pointed out have been weeded out and all requests for additional features fulfilled.

I am indebted to all beta testers for their time, effort, and inclination.

I herewith release the ‘final’ version in the hope that others will find it useful.  I will be delighted if it encourages goal-based investing.

What is it and what can it do? 

  • The tool enables you to track your mutual fund holdings with minimum user inputs (date and amount of purchase).
  • You can handle a maximum of 125 mutual funds and 10 different financial goals or portfolios.
  • The account statement of each fund holding will be auto-generated and auto-updated at the click of a button.
  • By entering all transactions made wrt a goal, the net portfolio returns of a goal will be calculated.  To get an accurate picture, past holdings will need to be entered.
  • The goal corpus value will be auto-fed to goal planning tools to get insights on the progress of the goal.
  • This is especially useful for retirement, as it will tell you the level of your financial freedom automatically.
  • Two or more mutual funds can be combined to calculate net returns. This will give an idea of the top performers or underperformers.
  • Capital gains associated with future redemptions and  fund switches can be evaluated by taking into account exit loads and lock in periods
  • The evolution of the fund value, investments, and the goal corpus can be graphically evaluated.
  • The CAGR of all holdings is recorded for future reference.
  • The performance of fund holdings can be compared with any other mutual fund (preferably an index fund or ETF) for the same transactions.
  • Closed-ended mutual funds like FMPs can also be tracked.
  • Since it is open-source it can modified as per convenience.

Here are a couple of screenshots of the fund read me and analysis sheets

Mutual Fund Portfolio Tracker

This is the read me  sheet of the tracker

Excel Mutual Fund Portfolio Tracker

The evolution of Mutual fund holdings and goal corpuses can be graphically evaluated in this sheet.

 

Why should I use it when there are so many online trackers available?

  • Well, you do not have to!  I am not selling a product, I see no reason to bend over backwards to convince you.
  • Offline Excel tracking is a choice, just like online trackers. Like everything else in life, there are pros and cons in both.
  • I have used the tracker for the past three months and I have had some great, insights into my goals. Insights that I did not get or perhaps didn't/won’t bother to look for in the online versions. As a user I am quite satisfied and have stopped updating my moneycontrol account. All my holdings are tracked via this template now – skin in the game!

Since I am not a salesman let us gain some perspective by discussing pros and cons of offline  vs. online tracking.

Offline tracking That is, using a software installed in your computer with automated NAV retrieval from a web server.

This allows fast data entry with no ads, no cookies and can in one sense offer a sense of security and privacy. You can freely modify the file in open source versions like mine to suit your requirements (add more features or delete unwanted ones) and make notes for your spouse or partner to follow.

The biggest plus is heightened cognition.  That is you will need to click a button to

  • create account statements
  • update latest value
  • get CAGR
  • get portfolio cagr
  • calculate capital gains
  • plot graphs

You will need to click buttons in online versions too. Howeversometimes too much information is auto-generated. Most users do not appreciate or understand what these numbers mean.

The experience of using Excel Macros is far more enjoyable and more importantly it will make you realize the implications of your actions. It will make you understand how the process works. Just for this feature alone, my vote is for offline tracking.

I feel more in control of my financial goals after using this tracker.

For those want to DIY and looking for a tool that can instill a sense of control and discipline, I will strongly recommend this one at the cost of blowing my own trumpet.

Offline tracking has some cons too. It is slower. When you calculate CAGR it (mine) will take about 10-20 seconds to complete for each fund. This is not a terrible loss of your time and personally, I do not mind too much.

Today the minimum RAM size seems to be about 1 GB. If you have higher RAM, execution will be faster.

Lack of a professional interface.  Things will not look smooth and shiny and inviting to use in an offline version. I am a code writer and not a designer.  I have kept the interface as simple as I can.

All you need to do is to follow the numbered steps. It will take about 10 mins to get used to. Once you do, there is a good chance that you will be hooked.

Perhaps the biggest minus is the lack of additional insights. When I am in Moneycontrol or VRonline I can check market cap of my portfolio and gain insights on the nature of the holdings.  That is not possible in this tracker.

For an investor who has chosen funds with a method in mind, it should not matter much. Others can always use my hugely popular Equity Mutual Fund Portfolio Comparison Tool!

If you are someone who crashes a computer every 6 months, then use an online tracker! If you are someone who is in love with mobile apps, use online trackers.

Fail to see the point of tracking funds on a mobile though. I would like to sit before a device and work with it in peace.

If you hate ads/cookies and love the idea of keeping your holdings in your computer, love the idea of goal-based investing try out this tracker. Do take a backup from time to time!

If you hold just a few mutual funds and intend to keep it that way, this tool would suit you perfectly.

I would like to believe this tracker has at least a couple of features not present in the online versions.  I will leave it to you explore and find them out!

Latest Version

Download the automated mutual fund and financial tracker (Apr. 10 2016)

Download the automated mutual fund and financial tracker (Oct. 15 2014)

Thanks to JD for pointing our a bug in the goal analysis sheet.  As it is a minor bug existing users need not upgrade. Send me an email if you wish to incorporate this change in your present version.

Download the automated mutual fund and financial tracker (Aug. 23 2014)

Thanks to Shaleen for pointing out a minor bug in the 'summary' sheet that pops up if you hold only one fund.

Thanks to Vishal Jain for pointing out an error in the 'goals analysis' sheet.

An erratic bug in inputs sheet corrected thanks to Swapnil Kendhe.

Previous Versions (maintained for record keeping only)

Download the automated mutual fund and financial goal tracker (Apr 27 2014) 

Minor bug corrected in the account statement generator sheet.

Errors pointed out by Mr. Deepak Rao in CAGR calculation of dividends have been rectified. Read the related post: How to calculate returns from Dividend Mutual Funds?

 

Requirements: Excel 2007 or above; Macros should be enabled; Active internet connection. 

  1. Spend a few minutes observing the inputs required
  2. Do not use this sheet without an account statement in hand.
  3. Use a since-inception account statement to ensure fund returns and net portfolio returns are accurate
  4. If you encounter error messages, check the transaction dates
  5. If the dates are correct, check the actual price. If you get the dates and prices right, there is little chance of encountering errors (I hope!).
  6. I have done the best I can to remove all bugs. If you find any, do let me asap and I will correct it and post updates.
  7. The tracker calculated CAGR using XIRR. For holdings less than a year old, the return may appear too large or too small. So take the results seriously only for holdings over one year old.

Support: If you have any issues, please send me an email: freefincal [AT] gmail.com

I will do my best to sort your problems.

Happy Tracking!

I will be delighted if you could let me know if found the tracker useful. I will follow up this post with a couple of other posts on how to use this tracker.

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.

135 thoughts on “Automated Mutual Fund Performance Tracker

  1. Viren Phansalkar

    Sirjee.... will download it right away....Currently I am using VR... i will try this and let you know the outcome
    Thank you....

    Reply
  2. Viren Phansalkar

    Sirjee.... will download it right away....Currently I am using VR... i will try this and let you know the outcome
    Thank you....

    Reply
    1. pattu

      No Si. The loss is all mine. I tried to convert it to 2003 version. Some formulae are a little too complex to convert. If you can get hold of Excel 2007, you can install it along with 2003 and run both. It will be a great favor to me if you could do that.

      Reply
    1. pattu

      No Si. The loss is all mine. I tried to convert it to 2003 version. Some formulae are a little too complex to convert. If you can get hold of Excel 2007, you can install it along with 2003 and run both. It will be a great favor to me if you could do that.

      Reply
  3. A.Sundaram

    Dear Pattu,

    You are embarrassing me-you are showering riches on me and then saying that you will be favoured if I were to partake of it.

    Thanks for your good heart.Certainly I will try to instal Excel 2007-as a gift to myself.

    Reply
  4. A.Sundaram

    Dear Pattu,

    You are embarrassing me-you are showering riches on me and then saying that you will be favoured if I were to partake of it.

    Thanks for your good heart.Certainly I will try to instal Excel 2007-as a gift to myself.

    Reply
  5. Ashish

    Hello sir
    I have broadband connection. But in the first step aftr writing icici pru the list doesn't populate even aftr long waiting. Second thing i have excel 2007 still why compatibility mode is shown on the ribbon. This is my first ever query to you. U r doing a very good job for us all.
    Thanks
    ashish (Rajkot)

    Reply
    1. pattu

      Hi Ashish, The compatibility mode should not be shown. try to save as .xlsx file and try again.

      ICICI Pru should work. i just tried it out.

      Reply
  6. Ashish

    Hello sir
    I have broadband connection. But in the first step aftr writing icici pru the list doesn't populate even aftr long waiting. Second thing i have excel 2007 still why compatibility mode is shown on the ribbon. This is my first ever query to you. U r doing a very good job for us all.
    Thanks
    ashish (Rajkot)

    Reply
    1. pattu

      Hi Ashish, The compatibility mode should not be shown. try to save as .xlsx file and try again.

      ICICI Pru should work. i just tried it out.

      Reply
  7. Guru

    Sh. Pattu,

    I have been playing with this tool from yesterday..
    Using the features of goal track, comparing the performance of each my funds with the other funds etc etc.. the outcome is that I just deleted my MF portfolio from Money control and morning star websites.

    Let me say this has come out extremely better than the beta version i tested..

    Thanks for making mutual fund tracking easier and simple.

    Reply
    1. pattu

      omg! Thank you for such a tremendous show of confidence in the tracker. I am thrilled to read this. Perhaps deleting your online accounts is a bit extreme. The tracking entirely depends on the AMFI data. Sometimes the data is not available and this can be a problem. For example just before I saw your comment I realised HDFC Mid Cap Oppur NAV history is not fully available. In Such cases the NAV will need to be manually entered.

      Reply
  8. Guru

    Sh. Pattu,

    I have been playing with this tool from yesterday..
    Using the features of goal track, comparing the performance of each my funds with the other funds etc etc.. the outcome is that I just deleted my MF portfolio from Money control and morning star websites.

    Let me say this has come out extremely better than the beta version i tested..

    Thanks for making mutual fund tracking easier and simple.

    Reply
    1. pattu

      omg! Thank you for such a tremendous show of confidence in the tracker. I am thrilled to read this. Perhaps deleting your online accounts is a bit extreme. The tracking entirely depends on the AMFI data. Sometimes the data is not available and this can be a problem. For example just before I saw your comment I realised HDFC Mid Cap Oppur NAV history is not fully available. In Such cases the NAV will need to be manually entered.

      Reply
  9. Guru

    I could repose so much faith only because i have about 6 MF's to track and adding 3 more by this month end.. 🙂 So physically verifying / cross checking them is not going be a challenge.
    I have not deleted the accounts as i need them to track my equity. I just deleted MF portfolio.
    I am facing a little problem on Reliance ELSS, will send a separate mail on that.
    Regards

    Reply
  10. Guru

    I could repose so much faith only because i have about 6 MF's to track and adding 3 more by this month end.. 🙂 So physically verifying / cross checking them is not going be a challenge.
    I have not deleted the accounts as i need them to track my equity. I just deleted MF portfolio.
    I am facing a little problem on Reliance ELSS, will send a separate mail on that.
    Regards

    Reply
  11. Viren Phansalkar

    Hello Pattu,

    The excel was magnificient. I need not go to online portal for MF tracking..... However, the problem is i still have to go to online portals to check for equity folio. In any of your future releases, you may think to add another sheet or different excel and package it in a zip. In this new sheet, we just have to add the name of the stock, puchase date, selling date, total brockerages, quantity and it would give us a CAGR or Absolute returns.
    We need not get any real time data as we know at what price we sold it. In this case too, we would need to go to online portals but that would be just to see the current price, so that should not be an issue.
    What say?

    Reply
    1. pattu

      Thanks Viren. I am working on an Excel model for stocks. Perhaps someone else has already done this. I found a google docs template but I cant see to remember where.

      Reply
  12. Viren Phansalkar

    Hello Pattu,

    The excel was magnificient. I need not go to online portal for MF tracking..... However, the problem is i still have to go to online portals to check for equity folio. In any of your future releases, you may think to add another sheet or different excel and package it in a zip. In this new sheet, we just have to add the name of the stock, puchase date, selling date, total brockerages, quantity and it would give us a CAGR or Absolute returns.
    We need not get any real time data as we know at what price we sold it. In this case too, we would need to go to online portals but that would be just to see the current price, so that should not be an issue.
    What say?

    Reply
    1. pattu

      Thanks Viren. I am working on an Excel model for stocks. Perhaps someone else has already done this. I found a google docs template but I cant see to remember where.

      Reply
  13. Ashal Jauhari

    Dear Viren, you may use Google finance for the real time tracking of your stock portfolio.

    thanks

    Ashal

    Reply
  14. Ashal Jauhari

    Dear Viren, you may use Google finance for the real time tracking of your stock portfolio.

    thanks

    Ashal

    Reply
  15. Viren Phansalkar

    Yes, Ashal. Agreed. I was just saying that if Pattu can add another sheet just to maintain list of stocks bought and sold, I have to visit online sites just to see the price and not to check my folio 🙂 ..

    Reply
  16. Viren Phansalkar

    Yes, Ashal. Agreed. I was just saying that if Pattu can add another sheet just to maintain list of stocks bought and sold, I have to visit online sites just to see the price and not to check my folio 🙂 ..

    Reply
  17. Ramamurthy

    I downloaded the File.I have invested in SBI Pharma-Direct-Growth scheme and wanted to monitor the transactions.
    Entered SBI Pharma in the Input field.Nothing happened.Any solution please?

    Reply
    1. pattu

      Sir, some names are listed differently in AMFI. Please do the following:
      Type: SBI MSFU
      and then choose SBI MSFU PHARMA funds from the drop down list.
      Thank you.

      Reply
  18. Ramamurthy

    I downloaded the File.I have invested in SBI Pharma-Direct-Growth scheme and wanted to monitor the transactions.
    Entered SBI Pharma in the Input field.Nothing happened.Any solution please?

    Reply
    1. pattu

      Sir, some names are listed differently in AMFI. Please do the following:
      Type: SBI MSFU
      and then choose SBI MSFU PHARMA funds from the drop down list.
      Thank you.

      Reply
  19. Ramamurthy

    No Luck,Pattu.I typed SBI Magnum and also SBI MSFU.Drop down menu did not open.Can you pl try and let me know?

    Reply
    1. pattu

      I just checked it, and it is listed.
      Against "Type the first few letters of the MF scheme you need here", please type "SBI MSFU"
      Wait for a moment and then click the green cell next to "Then select the scheme you need from this drop-down list"
      You will be able to see the small downward arrow.
      Click on it and scroll down to the bottom to select the fund.

      Reply
      1. Ramamurthy

        Good,Pattu.It works!!!!Thank you.Congrats on a great job done.
        I will try with other funds I have invested and let you know.
        This is a summary of all transactions.Is there any provision to know each transaction detail?

        Reply
  20. Ramamurthy

    No Luck,Pattu.I typed SBI Magnum and also SBI MSFU.Drop down menu did not open.Can you pl try and let me know?

    Reply
    1. pattu

      I just checked it, and it is listed.
      Against "Type the first few letters of the MF scheme you need here", please type "SBI MSFU"
      Wait for a moment and then click the green cell next to "Then select the scheme you need from this drop-down list"
      You will be able to see the small downward arrow.
      Click on it and scroll down to the bottom to select the fund.

      Reply
      1. Ramamurthy

        Good,Pattu.It works!!!!Thank you.Congrats on a great job done.
        I will try with other funds I have invested and let you know.
        This is a summary of all transactions.Is there any provision to know each transaction detail?

        Reply
        1. pattu

          Thank you sir. Each fund will have its own sheet. On the bottom left you will see arrows pointing left and right. If you click the right arrow the sheets will scroll from right to left. You can see the fund account statement sheet.

          Reply
      1. pattu

        Ashish, I just checked it with ICICI and it works. Sometimes the list may not populate. Just retype and hit enter. It should work. You should briefly see "processing" message at the bottom right of the Excel screen after you hit enter. Please let me know how it went.

        Reply
  21. vignesh

    Pattu,

    You and ashal are my inspiration (.in streamlining my own family finance.Then and there by your guidance I am able to learn many things )

    I have used many calculators in your portal and suggested to many of my colleagues also.

    I learnt a lot mathematically from your excel sheets. (I would like to meet for my stock investing learning !! During our last discussion you are saying that you are learning abt markets and stock investing ! Will Drop a mail for you separately for this)

    1. Went to Purchase Direct mode(This move is because of you and our forum members). all Mutual Funds. (Tracking was an issue. But I was satisfied with VR). Now i will take up the testing of your excel sheet.

    2. Streamlined My own finance towards goal based investing.

    Still Working on the Father s Retirement Planning and Working on clearly maintaining the Documents.

    query:

    1. How do you maintain your annual Investments documents apart from having online ?

    Will test it Rigorously and wishing you all the Success and will try to give this link to as many people as i can.

    Thanks

    Vignesh

    Reply
  22. vignesh

    Pattu,

    You and ashal are my inspiration (.in streamlining my own family finance.Then and there by your guidance I am able to learn many things )

    I have used many calculators in your portal and suggested to many of my colleagues also.

    I learnt a lot mathematically from your excel sheets. (I would like to meet for my stock investing learning !! During our last discussion you are saying that you are learning abt markets and stock investing ! Will Drop a mail for you separately for this)

    1. Went to Purchase Direct mode(This move is because of you and our forum members). all Mutual Funds. (Tracking was an issue. But I was satisfied with VR). Now i will take up the testing of your excel sheet.

    2. Streamlined My own finance towards goal based investing.

    Still Working on the Father s Retirement Planning and Working on clearly maintaining the Documents.

    query:

    1. How do you maintain your annual Investments documents apart from having online ?

    Will test it Rigorously and wishing you all the Success and will try to give this link to as many people as i can.

    Thanks

    Vignesh

    Reply
    1. pattu

      Thank you very much for your kinds words of support and encouragement, dear Vignesh. Yes I am learning about stocks but I haven't progressed much. I have all folio numbers written in a notebook and all bank passbooks, PPF, post office accounts of my mother etc.
      I use a password manager. It has a security password. I used the one my wife uses so that she will remember it if i am not able to open the accounts for some reason.

      Reply
  23. Ashish

    Sir
    Finally it worked and i didn't know how my two hours passed working with it. The fault was all mine.
    Thanks

    Reply
  24. shankar

    hi Pattu
    just downloaded your xls and had a quick look . By any chance do you support import of mutual fund transactions from cams report and does this support capital gains calculation based on transactions for income tax purposes ?

    any suggestions from you or the experienced folks in this portal for other softwares which can help here if pattu's tool does not support this ?

    Thanks for a great job . your portal is my first site for personal finance reading .

    Thanks
    Shankar

    Reply
    1. pattu

      Hi Shankar. Thank you. If you have your excel from CAMs you can easily cut and paste to the tracker. Open a new entry sheet from the Lump sum input sheet, copy paste the dates column and amounts column separately. Mention the type of transaction.
      For redemptions NAV/price is mandatory for others the tracker will do it for you.

      I have a capital gains sheet. It will give the capital gains corresponding to a proposed redemption and not for those in the past.

      Reply
  25. Ramamurthy

    Sorry to bother you.
    I have invested in SBI Pharma both in SIP mode as well as a few lumpsom additional purchase.
    Under one Fund name can I have the result or should I have two fund names one to track only the SIP instalments and the other to track Additional random purchases?If so how to enter the transactions pertaining to additional purchases?

    Reply
    1. pattu

      No bother at all. There are two ways to do this.
      1) Integrate all transactions into one record. First create the SIP record. Then in the summary page, select the fund and update holdings. Choose to enter new transactions. Here you can enter all lump sum transactions, redemptions, dividends etc.

      2)Keep the SIP and Lumpsum separate for monitoring purposes. SIP record is created via inputs sheet and lump sum transactions via the lump sum input sheet.

      Reply
  26. Parshwadeep

    Hi Pattu, Not able to view SBI Emerging Businesses Fund (G) in the Input sheet list after selecting SBI in cell E2.

    Regards
    Parsh

    Reply
    1. pattu

      Hi, This is the listed name of the fund: SBI MSFU EMERGING BUSINESSES FUND
      So simply type SBI and scroll down the list until you get this.

      Reply
      1. Parshwadeep

        Thanks . Really good site with all information on calculators :). I will likely end up troubling you with more queries in future :).

        Reply
  27. Deepak

    Hi Pattu,

    I am getting the error about accessing NAV data. My net connection is fine (I am accessing this site with the same connection). Any idea what the problem might be?

    Thanks,
    Deepak

    Reply
    1. pattu

      Hi Deepak, just checked the recent version again. It seems to be working fine but a bit on the slower side. Perhaps a problem with AMFI server. Kindly check again. Else send me the file and I will have a look. However, another user sent his file but it worked fine in my comp!

      Reply
      1. Deepak

        Hi Pattu,
        Thanks for the quick response! I checked the code as well. When I reconstructed the URL (from debugger) it works fine in my browser. However, in macro code, it immediately jumps to the error message. Not sure what the problem is. I am not familiar with accessing of internet data through macros. Do I have to enable some Excel add-in?
        Regards,
        Deepak

        Reply
        1. pattu

          No you dont have to enable an add-ins. In the VBA code, kindly comment out the error message and run it again. You will not be able to find out the exact error. Kindly send me a screenshot or the file itself.

          Reply
  28. Suman Kumar Sud

    I have been trying to use the tracker after downloading the whole of today. But not successful. As after feeding the MF scheme name the drop down list does not appear. Plz advise.

    Reply
  29. Pattabiraman Murari

    Please see message on the top of the file. You will have to enable content or enable macros for the file to work. Once you do that,
    please try the following. type HDFC against, "Type the first few letters of the MF scheme "
    Hit enter.
    Wait a few moments. Click the green cell against, "select the scheme you need from this drop-down list"
    you will see a small grey square a downward arrow in the centre.
    Click on it to see the list.

    Reply
  30. Prashant Kulkarni

    Hi pattu sir,
    I just started using the sheet and it is really an useful sheet to maintain all your records. Appreciate your help!
    I am facing one issue with my SIP transaction. I have 2 SIP's of same fund(QLTE--Which I shortlisted after going through your another post 🙂 )which are started on different dates. But since first transaction is same for the both SIP's(input sheet), even though it happened only once, first transaction is being added into both SIP's. Since first transaction is mandatory,I have to enter this in both SIP's. So, I was wondering how not to add first transaction in one of the SIP.

    Reply
    1. pattu

      Thank you. In one SIP, let the identical transaction be the first (so it will be counted here). In the other SIP, assume it starts from the second SIP transaction so that common transaction will not be counted twice. Hope this is clear.

      Reply
  31. Prashant Kulkarni

    hi pattu sir,
    thanks for the tracker. It's really very useful to track all our SIP records. Appreciate your help!
    I started using it for SIP records and during that,I am facing issue with the first transaction in inputs sheet. I have 2 SIP's of different dates in same fund(QLTE-Which I shorstlisted after going through your other post) but for both SIP's, first transaction date is same. Due to this, first transaction amount is getting added into the 2 SIP's. So any inputs to remove first transaction detail from one of the SIP?
    Thanks in advance.

    Reply
  32. Shaleen

    Dear Pattu,

    I searched for a long time for an online tool which could give me an IRR calculation on my MF investments. Couldnt find one. Found your excel tool and impressed by the sheer complexity of it. However am facing some startup challenge.I was trying the tab: Input for lumpsum investments. Step 1 works. But when I press on Step 2 to add transaction details, I get an error message: Cannot run the macro " !Macro 9. The macro may not be available in this workbook or all macros may be disabled.Since step 1 worked, my presumption is that macros are working. In any case double checked by going to the trust centre for excel, it has option " Disable macro with a notification". However I didnt receive any notification?Can you pl help, Eager to try this model.
    Appreciate the help. Regards,Shaleen

    Reply
  33. Shaleen

    Dear Pattu, Was able to solve the issue on Step 2 not working. It was antivirus which was disabling the macro. Was able to get though it. In Step 1, Chose HDFC Top 200. Was able to get this in the drop down menu. Got to the entry sheet, I put the date of transaction, maintained the "Purchase" entry and entered the amount. Post that went to summary sheet and pressed " Update funds with latest NAV". Got an error. "Run Type error 13. Type mismatch". Can you pl help. Thanks, Shaleen

    Reply
    1. pattu

      Thanks for pointing this out. Will send you a mail to get rid of this bug. Shall update the sheet and post it with a credit to you.

      Reply
      1. Jig

        Dear Pattu, it is still same. I got it with " error-9" after the same steps mentioned by shaleen. I have downloaded the latest version only after shaleen clarify the bug.

        Reply
          1. JD

            Dear,
            I downloaded the same at office PC and there might be the issue. I downloaded again on my home PC and its working fine.Well after initial working i found some things to be clarified.
            I
            In goal analysis future value of Crnt Invsmtnt which should be compounded value of my current investment at goal ending date. but actually it is showing same as my current investment. Please correct me if i take it as wrong way.
            How i should cover the STP between the two funds. like liquid to equity and vice versa?
            In Goal Analysis sheet, first graph for MF is working fine but bottom one only showing retirement goal and no other goal is visible there.

            Well i will dig more and put commennts further if came across any bug. but i am more than happy right now to get such fantastic tool. i have prepared the money control PF but i am very lazy to update there. 🙂
            Thanks & Regards

          2. pattu

            Checked the future value of the current investment, and it seems fine to me. Please see the formula of the cells.

            STPs will have to handled via lump sum entry sheet only and manually updated from summary sheet.

            Second graph in goal analysis sheet has been corrected. Will update the new version with a credit to you. Many thanks.

  34. Akshay Maini

    Firstly I must commend you for all your effort and work you have put in your excel sheets. Great going and it's a lot of help and inspiration.
    Secondly I just wanted to mention that since your have made such an elaborate automated MF Goal tracker - it would be a nice addition to have the options of weekly SIPs in it as well as opposed to just monthly. I for one have Weekly SIPs running and am entering all that data manually under the Lumpsum option. Additionally when you delete an MF transaction - there are blank rows left over in the Goal Tracker Tab - which if there happen to be a lot may eventually end up occupying necessary row.
    But other than that presently I haven't found any bugs yet. Thanks for your effort.

    Reply
  35. Pattabiraman Murari

    Thank you. It is possible to include a weekly SIP. I will try and do that when I get some time. Reg. the goal tracker option, I agree with you. It can become painful. Let me see if I can rectify this. Many thanks for your feedback.

    Reply
  36. Pushkar

    Hi,
    Is there any better way to get data from old version of excel tool to new one ? Or its just copy pasting it ?

    Thanks

    Reply
    1. pattu

      This is a pain. I agree. There is a way to move the sheets but one must still make some changes in the summary sheet. This is difficult for users to do it though.

      Reply
      1. Pushkar

        Next time onwards, if there is any change in script of tracker, and if change is easy to implement, you may describe what modifications are needed, so data transfer can be avoided. And ppl can update their own tracker to latest version.

        Also, can we include R.D and F.D in this tracker ? Because they will be used mostly for short term goals and can be mapped to goals in sheet with ease.

        Thanks.

        Reply
          1. Pushkar

            Here, you dont have to include all FD details from all banks.
            Just a blank cell for bank name, and some more cells for interest rate, duration, amount and frequency.

            Something like "http://allbankingsolutions.com/fdcal.htm"

            Or, we can just add another sheet, where user will input all these details(no formula) and let it map to goals in same sheet.

            Thanks

  37. Ravish Byndoor

    Dear Sir,
    Thanks for the nice work, deleting a wrong mutual fund entry is difficult , Can you Please explain in steps.

    Reply
    1. pattu

      Go to the summary sheet. Notice the green cell above the delete record button. Click on the green cell. You will notice a small grey square on the right. Click that, scroll up or down to select your mf entry. Once you select, click the delete button.

      Reply
  38. Himanshu

    Hello Sir

    I am a frequent reader of your informative posts in AIFW and here. I am planning to start investing in Mutual funds , SIP. However I am totally unaware of the terms , the nittty gritty and the hidden points one needs to be aware of in context of Indian market. Is there any post for starters in your site ? Any learning path you have ?

    Eagerly awaiting your response

    Reply
  39. Meena Shivram

    Hello Sir,

    I have started reading your blog recently (got introduced to it via subramoney) and had downloaded the latest version of the automated MF financial goal tracker tool. I have been investing in Mutual funds since 2004 mainly through SIPs and some lump sum investments mainly when I do switches from one fund to another in the same fund house. The tool is very useful but the data entry in my case was painful as I had my SIPs in the same fund in different durations. For each SIP, I have to create a new entry.

    Also I have a suggestion. In the option to Update Individual MF statements in the Summary Tab, it only allows you to enter a new transaction. How can I edit a transaction which is wrongly entered? I think this option should be allowed as some human error is unavoidable during data entry. Here you have to delete the entire transaction before adding the new one.Still the tool is great could be because personally I am very comfortable with Excel....

    The one advantage of this tool is the ability to link your investments to the goals and track this on a regular basis. Believe me, this is a very good idea and not available in many of the online tools like VR online or Money control

    Reply
    1. pattu

      Thank you. To edit an incorrect transaction, please navigate to the account statement sheet of the fund and make necessary changes. If you do this, you woll realize many tricks that you can do 🙂

      Reply
  40. Sriram

    Hello Mr.Pattu,

    The tracker is extremely useful - thank you. I have a nagging issue though - I have a few equity funds with Dividend reinvestment option - how do I enter these dividends? If I enter them as "Dividend" transactions with the Update option, there is no effect. If I enter these as a "Purchase" instead, the amount matches, however CAGR goes for a toss. Any suggestions? A manual workaround is fine!

    Reply
    1. freefincal

      Sorry for the late response. This is a bit of a problem. When you enter only dividends then for the XIRR calculation, they will be treated as reinvested. So you cannot enter both the div and reinvestment transaction.
      Suggest you enter only purchases and reinvestments without the dividend information and see how this works.

      Reply
      1. Sriram

        Hello, Sorry for my really late response for this one. Gave it another go today. The only scenario where I get an accurate value for the current value is when I treat dividends as "Purchases". When I enter dividends as "Dividends", I am not sure if they are treated as re-investments - it looks like they are treated as dividends that are redeemed. Can we add another option for dividend re-investment?

        Reply
        1. freefincal

          For Div reinv, best to treat div as purchases as you have done. For plan dividends, they can treated as such as they will deemed as reinvested. Will think more about this to see how it can treated.

          Reply
  41. Krupesh

    Hi, I am not able to update to any automated sheet like nav seeker, rolling return it is always giving me the error Runtime error1004. Please help

    Reply
  42. raag

    Pattu Sir, thanks for this tool. But, unfortunately, when I entered three rows for one MF in lump sum investment entry sheet, only the first row was recognized and recorded in the summary sheet!

    Reply
    1. raag

      Ignore my previous comment please. Actually, i had entered date in dd/mm/yyyy format whereas my computer's regional setting was English(US). No error was showing up though. After changing my computer's regional setting to English(India), the lumpsum investment show up fine. The NAV being shown is incorrect though. Where and how do you pull the latest NAV?

      Reply
          1. raag

            Sir, I meant, where i can update NAV manually if the latest NAV being shown is wrong even after clicking that orange button?

  43. Anonymous

    Great work pattu sir... really good to work with, please include STP option as well if possible from one to other. Also I see some of the fund house like L and T not there, if possible include them as well. thanks a lot for all the work done.... Warm regards, Sai.

    Reply
  44. Raag

    Pattu Sir: I deleted some columns for some dates in "Returns History" worksheet. Now, I get an error (Run-time error '1004') whenever I click on "Update funds with latest NAV" orange button in Summary sheet. Is there a way to fix this? A way to get the "Returns History" to start from scratch. Thanks.

    Reply
  45. Raman

    Hi Pattu, have you by any chance tested this tracker / folio mgr on Windows Office 2013 / Office 365? I seem to be using O365 and this sheet does not seem to be working (or maybe i'm missing something. Please do tell me if i'm missing a step (or 10 😉 )
    So I downloaded the sheet, filled the fund name in "Input for Lumpsum Investments" tab (Did Step1) > Step 2 > Go to the generated "entry-sheet", fill date and amount > ??? > i was hoping this would show up in "Summary", but nothing happened :-/

    Reply
  46. Anand Vaidya

    The file is xlsb - XLS Binary? I use LibreOffice on Linux (that's right!) and can't open the file. I guess the application is strictly Excel on Windows only?

    PS: I have built up an (ugly) spreadsheet for my own use, but I thought yours could be cooler...

    Reply
  47. Mukund

    Hi Pattabi,

    Thanks for the wonderful calculator. Couple of queries.
    1. For a closed-ended fund, is there a way to enter dividends? Example: ICICI Pru Growth Series Fund - Regular Dividend.
    2. I am unable to find Motilal Oswal MOSt Focused Multicap 35 Reg-G even after I retrieve the existing AMFI database. There are just 2 funds under Motilal being retrieved.

    Thanks.

    Reply
  48. Jig

    Its looks it is no more updating the details if we are continuing using the previous MS office. Or may be due to some other reason but i am forced to stop using the same as it is not updated.
    Earlier i communicated this but you said it is working fine at your end so don know why exactly happening this.
    How i can find such great tool as online version? Or do you have planning to make App based on this Portfolio and MF tracking tool?
    Please update

    Reply
    1. freefincal

      I am afraid it is still working fine for me. I use it every day. I intend to offer a simple goal tracking app soon. I would suggest you use perfios

      Reply
  49. Mohit

    Hi,
    First of all thanks for maintaining such a wonderful and insightful blogs.
    Recently I am searching for a mutual fund tracker for tracking my mutual fund investments and after some research zeroed down to this. However I am facing problem related to one of ICICI mutual fund i.e. ICICI Prudential Value Discovery Fund. I don't find this fund in the list and when I try "Retrieve existing AMFI database" in the Add new AMFI record tab it says connection to internet failed. Can you please help.

    Thanks

    Reply
  50. Ashwin

    Dear Sir,

    Please accept my sincere vote of thanks for your blog and your financial calculators . I look forward to a long term of wealth creation with your blogs .

    Regards

    Reply
  51. Ramesh S

    I have many regualr, direct & different AMCs mutual funds folios. I have also many MFs holding jointly with my daughter !
    Can any one help me how to start with Automated-mf-portfolio-financial-goal traker?
    (I have already down loading the tracker).

    Reply
  52. Ram

    A great tool indeed!! I have been using it for sometime now. Got a query though. If I use One MF for more than one goals, Does it has the provision to support it?

    Reply
  53. Rohit Dighe

    Hi Pattu,
    this calculator is really nice, but in my case i need to change 2 things.
    1. For direct investment SIP dates were different every month, how can i change them in report and see the difference in summary page.
    2. I had missed one SIP due to KYC compliance issue, how can i remove that one and add new entry.

    Thanks
    Rohit

    Reply
  54. Sarbjit

    Can someone let me know how to track NPS investments with this tool. There is a note in the workbook that it's possible. However I can't find how to do it.

    Reply
  55. Raghu

    Hi Pattu,
    I am using the April 2016 version of this tracker sheet. Not sure if this a bug or a conscious choice. If there are any redemptions in a fund, the portfolio value of the MF holding is as per the current number of units but the cost of this holding is reported as the total acquisition cost (not excluding redemptions).

    Reply
  56. K R Balakrishnan

    Your article is excellent. I was searching for this type of one. Today first time I down loaded your tracker April 2016 version and tried for couple of my MF Investment. Can you please guide me how to get auto update of Dividend payments for any MF investment?

    Reply

Do let us know what you think about the article