Automated Mutual Fund Performance Tracker

Published: March 19, 2014 at 9:47 am

Last Updated on

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 

USE THIS =>  July 2019 Version of the fund tracker

Download the automated mutual fund and financial goal tracker July 2018

Download the automated mutual fund and financial goal tracker June 2018

Download the automated mutual fund and financial tracker Dec 19th, 2017 Changed AMFI daily NAV update url, thanks to Anish Mohan. Existing users MUST follow instructions here for getting NAV updates.

Download the automated mutual fund and financial tracker Oct 10th 2017  Changed the way dividends are factored in XIRR calculation.

Download the automated mutual fund and financial tracker Aug 15th 2017 Thanks to Raghu Gopalan for spotting a bug.

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.

Do share if you found this useful
Share your thoughts on this topic at the  Reddit freefincal_user_forum

Reach your financial goals like a pro! Join our 1600+ Facebook Group on Portfolio Management! 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. The 1st lecture is free!
Want to check if the market is overvalued or undervalued? Use our market valuation tool (will work with any index!) or you buy the new Tactical Buy/Sell timing tool!
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 investingPublished by CNBC TV18, this 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

Travel-Training-Kit-Cover-new 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 Rs 199 (instant download)
Free android apps