Home Investment in india Lengthy-standing XIRR bug in freefincal MF and Inventory Portfolio Tracker

Lengthy-standing XIRR bug in freefincal MF and Inventory Portfolio Tracker

Lengthy-standing XIRR bug in freefincal MF and Inventory Portfolio Tracker


We’re glad to announce a long-standing bug within the Freefincal Google Sheets Mutual Fund, and Inventory Tracker has been resolved. This bug just isn’t a components or coding error on the sheet however resulting from an inherent deficiency in Google Sheets.

The newest variations of the trackers have been up to date with this decision. Current tracker customers (greater than 1000) have been intimated with detailed steps to replace their sheets. Anybody who has missed the intimation can use this sheet and observe the steps talked about.

The issue: The device can accommodate practically 3000 transactions for XIRR calculation. The XIRR is computed for the general portfolio, asset class, and mutual fund or inventory.

So if a single mutual fund has 100 entries, then the XIRR calculation for that fund could have hundreds of cells with zeros. That is, in precept, not an issue, because the XIRR subroutine can deal with it.

For some purpose, it fails to compute when the variety of transactions turns into only a handful. Generally it really works, and generally it doesn’t. This isn’t an error from our aspect. That is how the XIRR subroutine works.

This was an issue for a lot of customers for only one mutual fund they bought not too long ago. We lastly managed to kind this out.

The answer:

The same old XIRR components is like this


Column AL represents values and AM dates. There are 1000 entries in every column. The -0.1 is a guess worth helpful if the return is unfavorable. We modified this to


The Filter perform retrieves solely non-zero values in each columns to compute XIRR. This isn’t a assure the XIRR would compute, nevertheless it not less than eliminates the empty cell error. Additionally, customers should realise that XIRR is sensible solely when the funding is not less than one 12 months outdated or if most systematic investments are over a 12 months outdated.

Key options of the freefincal mutual fund and inventory tracker:

  1. A personal, all-in-one-place tracker that’s impartial of our computer systems.
  2. Quick and functionally superior to our earlier Excel-based trackers.
  3. Utterly open-source with no hidden formulae. It may be personalized at will.
  4. Simple to share with our life companions as a part of our continuity kits. See: The best way to assist our life accomplice handle cash in our absence.
  5. Up to date with present NAV on opening the sheet.
  6. New SIP transactions will likely be routinely obtainable (see screenshots and directions beneath). In case you have a number of SIPs, this sheet might be duplicated to take care of separate data.
  7. Whole fairness and debt fund worth might be plotted routinely on a every day/weekly/month-to-month foundation (no must maintain the sheet browser tab open!)
  8. Total portfolio annualized return (fairness + debt), total fairness and debt annualized returns obtainable.
  9. A fundamental goal-analysis sheet for ballpark estimation of monetary objective progress. The present funding worth is auto-updated for revised estimates.
  10. Shortly discover the NAV for any fund since third April 2005 to enter lump sum transactions.
  11. The sheet comes with two monetary objective dashboards: one for retirement and one for youngsters’s future. It may be duplicated or customised for different targets.
  12. These with a unified portfolio (similar investments for a number of targets) can use the sheet.
  13. Two variants can be found: One for mutual funds and one other for mutual funds and shares. The beforehand printed inventory tracker is built-in with this sheet. It offers the annualised return of particular person shares and of the general portfolio after making an allowance for all company actions like dividends, splits and so forth., precisely as per SEBI norms avoiding the errors made by many inventory portfolio trackers.
  14. All future bug updates and model updates will likely be made obtainable to customers. To make sure you obtain these updates, add this electronic mail handle to your contact checklist: pattu {AT} freefincal {DOT} com.
  15. Inexpensive! The MF tracker prices solely Rs. 500. The Mutual fund and Inventory Tracker Prices solely Rs. 800. This can be a one-time buy.
  16. NEW! An add-on module for plotting and evaluating your mutual fund portfolio with a benchmark since inception is now obtainable! This module solely prices Rs. 200 further! That is additionally a one-time buy.
  17. We’re open to function ideas if they’d assist many customers.
  18. 100% pores and skin within the sport: we’ll use this sheet to trace all our mutual fund and inventory investments. For examples, see: Portfolio Audit 2022: The annual overview of my goal-based investments and My Inventory Portfolio Evaluation: Might 2023
  19. For assist and suggestions contact: freefincal {AT} Gmail {Dot} Com
  20. Get this sheet totally free! => A brand new device to match 5 MF portfolios & discover overlapping shares


(1) Retirement dashboard  (click on to view zoom-out photographs)

Retirement dashboard screenshot of Freefincal MF and Stock Portfolio Tracker
Retirement dashboard screenshot of Freefincal MF and Inventory Portfolio Tracker
  • Every funding in a fund must be recognized by a singular nickname
  • Two folios held in the identical fund should be given totally different nicknames.
  • Every fund’s AMFI Code (black arrow above) should be entered. See the video on how to do that. Directions are additionally obtainable within the sheet.
  • The blue arrow exhibits the fairness portfolio’s total XIRR (annualised return).

SIP Transactions

The screenshot beneath exhibits the SIP transactions web page. All transitions will likely be auto-generated when you enter the SIP particulars, together with the fund nickname and AMFI code.

SIP Transactions page in the Freefincal MF and Stock Portfolio Tracker

SIP Transactions web page within the Freefincal MF and Inventory Portfolio TrackerThese transactions ought to be copied to the actual objective sheet: Retirement or Little one and so forth.

The SIP transaction will likely be auto-updated on this sheet from the following month. There will likely be a message in “purple” (see screenshot) on the SIP and the retirement web page. Solely the brand new transaction must be copied into the retirement web page.

Within the case of a number of SIPs, you may duplicate the SIP web page to trace every concurrently and hyperlink every SIP with the corresponding objective.

Discover NAV Sheet

You should use this sheet to seek out the NAV for any dates on or after third April 2006 for lump sum investments or redemptions.

Screenshot of Find NAV Page in the Freefincal MF and Stock Portfolio Tracker
Screenshot of Discover NAV Web page within the Freefincal MF and Inventory Portfolio Tracker

Aim Evaluation Sheet

A fundamental objective progress evaluation sheet is included. It has many simplistic assumptions, and the outcomes ought to solely be handled as a ballpark estimate. We advocate utilizing our robo-advisory device for correct evaluation. See: The best way to use the freefincal robo advisory device to trace the progress of our monetary targets.

Goal Analysis Sheet
Aim Evaluation Sheet

Portfolio Chart Replace

MF portfolio Value Chart
MF portfolio Worth Chart

MF portfolio Worth Chart within the Freefincal MF and Inventory Portfolio Tracker. A easy set off might be set as much as replace the general fairness and debt portfolio values every day, weekly, or month-to-month.

The consumer must arrange this set off. It can solely take just a few seconds. See screenshots within the set off part beneath and likewise see the video. Please observe these easy directions to arrange the set off inside 30 seconds!

Video guides

Mutual Fund Portfolio Monitoring

The best way to benchmark your mutual fund portfolio

Inventory Portfolio Monitoring

Portfolio Visualisation (add-on module)

This exhibits the evolution of my son’s future portfolio in contrast with Nifty 50 TRI. For fund particulars, see: Classes from investing for my son’s future for the final 12+ years

Since inception mutual fund portfolio for my son's future compared with Nifty 50 TRI
Since its inception, the mutual fund portfolio for my son’s future in contrast with Nifty 50 TRI.

Please observe the video on arrange the portfolio visualization module.

NOTE: You should buy sheets with this module already arrange and prepared to be used (choices A and C beneath). Then You solely must see arrange the triggers on this video.

Get the freefincal mutual fund and inventory tracker!

After buy, you’ll be directed to make a replica of the Google Sheet. Please make a replica after which proceed getting into transactions.

(A) Mutual Fund monitoring + Inventory monitoring + Portfolio visualisation for mutual funds: Click on to pay Rs. 1000 and get the freefincal mutual fund and inventory tracker with portfolio visualization for MFs

(B) Mutual Fund monitoring + Inventory monitoring:  Click on to pay Rs. 800 and get the freefincal mutual fund and inventory tracker

(C) Mutual Fund monitoring + Portfolio visualisation: Click on to pay Rs. 700 and get the freefincal mutual fund tracker

(D) Mutual Fund monitoring: Click on to pay Rs. 500 and get the freefincal mutual fund tracker

(E) Have already got the MF tracker or MF+ inventory tracker? Get the Mutual fund portfolio visualization module (add on for Google sheets tracker) for Rs. 200 solely (one-time buy)

Do share this text with your folks utilizing the buttons beneath.

🔥Get pleasure from large reductions on our programs and robo-advisory device! 🔥

Use our Robo-advisory Excel Device for a start-to-finish monetary plan! Greater than 1000 buyers and advisors use this!

New Device! => Monitor your mutual funds and shares investments with this Google Sheet!

  • Comply with us on Google Information.
  • Do you have got a remark in regards to the above article? Attain out to us on Twitter: @freefincal or @pattufreefincal
  • Be part of our YouTube Neighborhood and discover greater than 1000 movies!
  • Have a query? Subscribe to our publication with this type.
  • Hit ‘reply’ to any electronic mail from us! We don’t provide customized funding recommendation. We are able to write an in depth article with out mentioning your title you probably have a generic query.

Get free cash administration options delivered to your mailbox! Subscribe to get posts through electronic mail!

Discover the location! Search amongst our 2000+ articles for data and perception!

About The Creator

Pattabiraman editor freefincalDr M. Pattabiraman(PhD) is the founder, managing editor and first creator of freefincal. He’s an affiliate professor on the Indian Institute of Know-how, Madras. He has over 9 years of expertise publishing information evaluation, analysis and monetary product growth. Join with him through Twitter or Linkedin, or YouTube. Pattabiraman has co-authored three print books: (1) You might be wealthy too with goal-based investing (CNBC TV18) for DIY buyers. (2) Gamechanger for younger earners. (3) Chinchu Will get a Superpower! for teenagers. He has additionally written seven different free e-books on numerous cash administration subjects. He’s a patron and co-founder of “Price-only India,” an organisation selling unbiased, commission-free funding recommendation.

Our flagship course! Study to handle your portfolio like a professional to attain your targets no matter market situations! Greater than 3000 buyers and advisors are a part of our unique group! Get readability on plan on your targets and obtain the required corpus it doesn’t matter what the market situation is!! Watch the primary lecture totally free!  One-time fee! No recurring charges! Life-long entry to movies! Scale back worry, uncertainty and doubt whereas investing! Discover ways to plan on your targets earlier than and after retirement with confidence.

Our new course!  Improve your revenue by getting individuals to pay on your expertise! Greater than 700 salaried workers, entrepreneurs and monetary advisors are a part of our unique group! Discover ways to get individuals to pay on your expertise! Whether or not you’re a skilled or small enterprise proprietor who desires extra shoppers through on-line visibility or a salaried individual wanting a aspect revenue or passive revenue, we’ll present you obtain this by showcasing your expertise and constructing a group that trusts you and pays you! (watch 1st lecture totally free). One-time fee! No recurring charges! Life-long entry to movies!   

Our new e-book for teenagers: “Chinchu will get a superpower!” is now obtainable!

Both boy and girl version covers of Chinchu gets a superpower
Each boy and woman model covers of Chinchu will get a superpower.

Most investor issues might be traced to an absence of knowledgeable decision-making. We have all made dangerous selections and cash errors once we began incomes and spent years undoing these errors. Why ought to our kids undergo the identical ache? What is that this e-book about? As dad and mom, what wouldn’t it be if we needed to groom one skill in our kids that’s key not solely to cash administration and investing however to any side of life? My reply: Sound Choice Making. So on this e-book, we meet Chinchu, who’s about to show 10. What he desires for his birthday and the way his dad and mom plan for it and educate him a number of key concepts of determination making and cash administration is the narrative. What readers say!

Feedback from a young reader after reading Chinchu gets a Superpower (small version)
Suggestions from a younger reader after studying Chinchu will get a Superpower!

Should-read e-book even for adults! That is one thing that each mother or father ought to educate their children proper from their younger age. The significance of cash administration and determination making primarily based on their desires and desires. Very properly written in easy phrases. – Arun.

Purchase the e-book: Chinchu will get a superpower on your baby!

The best way to revenue from content material writing: Our new e book for these desirous about getting aspect revenue through content material writing. It’s obtainable at a 50% low cost for Rs. 500 solely!

Need to examine if the market is overvalued or undervalued? Use our market valuation device (it can work with any index!), otherwise you purchase the brand new Tactical Purchase/Promote timing device!

We publish month-to-month mutual fund screeners and momentum, low volatility inventory screeners.

About freefincal & its content material coverage Freefincal is a Information Media Group devoted to offering unique evaluation, stories, evaluations and insights on mutual funds, shares, investing, retirement and private finance developments. We accomplish that with out battle of curiosity and bias. Comply with us on Google Information. Freefincal serves greater than three million readers a 12 months (5 million web page views) with articles primarily based solely on factual data and detailed evaluation by its authors. All statements made will likely be verified from credible and educated sources earlier than publication. Freefincal doesn’t publish any paid articles, promotions, PR, satire or opinions with out information. All opinions offered will solely be inferences backed by verifiable, reproducible proof/information. Contact data: letters {at} freefincal {dot} com (sponsored posts or paid collaborations is not going to be entertained)

Join with us on social media

Our publications

You Can Be Wealthy Too with Aim-Primarily based Investing

You can be rich too with goal based investingRevealed by CNBC TV18, this e-book is supposed that can assist you ask the precise questions and search the right solutions, and because it comes with 9 on-line calculators, you may also create customized options on your life-style! Get it now.

Gamechanger: Overlook Startups, Be part of Company & Nonetheless Dwell the Wealthy Life You Need Gamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you wantThis e-book is supposed for younger earners to get their fundamentals proper from day one! It can additionally make it easier to journey to unique locations at a low price! Get it or reward it to a younger earner.

Your Final Information to Journey

Travel-Training-Kit-Cover-new That is an in-depth dive evaluation into trip planning, discovering low-cost flights, finances lodging, what to do when travelling, and the way travelling slowly is healthier financially and psychologically with hyperlinks to the online pages and hand-holding at each step. Get the pdf for Rs 300 (on the spot obtain)




Please enter your comment!
Please enter your name here