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.

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

This is the read me sheet of the 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.

Join our 1500+ Facebook Group on Portfolio Management! Losing sleep over the market crash? Don't! You can now reduce fear, doubt and uncertainty while investing for your financial goals! Sign up for ourlectures on goal-based portfolio managementand join our exclusive Facebook Community. The 1st lecture is free! Did you miss out on the lockdown discount? You can still avail it! Follow instructions in the above link!

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.

Use a since-inception account statement to ensure fund returns and net portfolio returns are accurate

If you encounter error messages, check the transaction dates

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

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.

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

Join our 1500+ Facebook Group on Portfolio Management! Losing sleep over the market crash? Don't! You can now reduce fear, doubt and uncertainty while investing for your financial goals! Sign up for ourlectures on goal-based portfolio managementand join our exclusive Facebook Community. The 1st lecture is free! Did you miss out on the lockdown discount? You can still avail it! Follow instructions in the above link!

About the Author

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

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

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)

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.

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.

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.

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)

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)

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

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.

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

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.

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

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

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?

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?

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

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

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.

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?

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.

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?

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.

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.

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.

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.

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.

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 .

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.

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?

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.

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!

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

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.

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.

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.

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.

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.

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

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

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

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.

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.

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.

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.

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 ?

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

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!

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.

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?

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?

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.

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.

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

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.

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

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.

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

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.

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

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?

Hello Sir, I’m using the April version of the tracker. I added Religare Invesco Tax Plan – Growth via the Lump sum investment option. The dates I entered was 27/11/2015 for an amount of Rs. 20000, entered NAV as 35.08 based on the folio document I had.

Now here are the issues I am seeing. Please correct me if I am doing something wrong. 1. After addition of the details and performing of analysis, the summary page shows the entry for the fund. However CAGR column is empty. No value shows up for that. 2. At this stage, I clicked the Update CAGR for all funds button, after the calculation, the entry for the fund got mangled in the summary page. On checking the corresponding entry sheet for the fund, the values had been replaced by some string in the page.

I faced a similar issue with the DSP Blackrock Tax Saver fund as well.

Hi Pattu, I was wondering how to use the sheet to maintain MF transaction data for funds with both lumpsum and SIP mode of investment. I have existing lumpsum investments maintained in the sheet and want to add a SIP on one of those funds. Is it possible to have it reported in one shot or as 2 separate lines?

The delete button is not working for me. Under the Summary Sheet, right side there is delete button. If I click on the drop-down list, it is empty & doesn’t show the entries you have made. If I press the delete button, I get the following error – ” Please choose the fund record you wish to delete from the sheet below and click the button once again”. I am using Excel 2013 (Win 7).

Been a regular visitor to your site since the past couple of years and have greatly benefited from it. Thank you for all your educational efforts via the blog.

The Automated mutual fund tracker has been especially useful. I was facing an issue with the April 2016 wherein the “Update SIP end date” for a MF does not seem to accept any valid date and always complains that the “Date of SIP closure cannot be done before ‘date of first transaction;” even though the date entered does not violate this condition. So i was planning to use the new version of the tracker. But there is a problem that I face : I see that you have already released 5 versions of the tool and may possibly release more in near future. Is there a way to directly import transactions already captured in previous versions of the tracker rather than manually entering it into every new version of the tracker tool?

It shows error as “Date of 1st transaction entered is not valid business day in which nav is listed.If you are sure date is correct,it may be possible that amfi database does not list the nav of the date of 1st transcation. ”

Even i have tries using different dates and also tried using add new amfi record update that fund but same error.

Hello Sir, I am using this excel sheet for sometime now, but past 1 week i am facing some problem. The sheet updates NAV of all mutual funds, but when i click update CARG, it gives dialog box ” It will take approx 9 min” and after clicking ok ” it says “Relax it will update”. But even after 2 hrs , the sheet becames non responsive. I tried many times and same happens. Kindly advice if you are aware of any such problem.

I downloaded the “Automated” and it looks like a great piece of work. But have still not understood how to use it – so eagerly waiting for a user guide (of some sort) with practical names of portfolios instead of generic names like qlteSIP2, qlte, etc.

Could the excel be enhanced to included STP (I park my surplus in a in a liquid and then set up a Daily/ weekly/ Monthly) STP to an equity or balanced fund of the same MF house.

Hi, When I add a new SIP in summery page it shows only once line. with the first SIP details but it does not update SIPs till date. I did click on “update individual MF STMT” from summery sheet. But it did not do anything. Is this an issue or am I missing something? Can some help?

Hello Sir, I am getting automation error after entering only my first mutual fund. I have an i7 processor and sufficient ram, i am able to enter details and click on generate button, then after few seconds I see the MF that I have entered at the top of a page, but immediately I get an error stating that automation error and Excel becomes non responsive.

Hello Sir, I am getting automation error after entering only my first mutual fund. I have an i7 processor and sufficient ram, i am able to enter details and click on generate button, then after few seconds I see the MF that I have entered at the top of a page, but immediately I get an error stating that automation error and Excel becomes non responsive. I have sent you a video via tweet to show what keeps happening. Please suggest a way forward if possible.

In the V3 version, whenever i click draw portfolio growth curve, i keep getting the below error. “There is a problem with accessing the NAV data. Please check your internet connection”. What should be done to rectify ?

Hello Sir, I’m trying to use your template (V3-Freefincal-Automated-mf-portfolio-financial-goal-tracker-JULY-2018-1.xlsm) . But I’m unable to locate “Capital Gains” worksheet although it is mentioned in the Home tab. Could you please assist ?

Losing sleep over the market crash? Don't! You can now reduce fear, doubt and uncertainty while investing for your financial goals! Sign up for ourlectures on goal-based portfolio managementand join our exclusive Facebook Community. The 1st lecture is free! Did you miss out on the lockdown discount? You can still avail it! Follow instructions in the above link!

Contact Freefincal

SUBMIT A TIP

If you have some information relevant to freefincal you can send it to letters [AT] freefincal.com

Note: We do not accept paid/free guest post requests. We do not work with professional content writers or marketing agencies. We do not participate in link exchanges or brand campaigns Such emails will be discarded.