Mutual Fund SIP Returns Calculator

Use this suite of calculators to compute SIP and lump sum returns, compute SIP corpus and determine SIP amount needed for a goal. This represents an upgrade of my understanding of (a) excel functions and (b) the compounding process. The calculators also help illustrate a common error in converting monthly  returns to annual returns.

First some yada yada  (to borrow a phrase fromGoogle toolbar) if you don't mind. Every since the blog got reasonably popular (for my standards) kind readers have called me a 'Excel expert' much to my embarrassment. Nothing is farther from the truth. The truth is  I use Excel because it is the only platform accessible to everyone. These calculators can be written in many platforms routinely used for physics research with much better efficiency. Truth is I am still unfamiliar with many Excel functions. I manage because, thanks to my training, I have realized that all of investment and amortization math stems from a single master equation. All one needs is high school algebra to manipulate the master equation to different situations. The Excel functions like FV, PV, IPMT etc. do just this.

Unfamiliarity with Excel features has certain disadvantages: Until yesterday I didn't know that computing SIP returns can be done in a single step using an Excel function called RATE! I had earlier made a SIP returns calculator in a roundabout way resulting in a 5 Mb file with a minor bug (now corrected)!

The only saving grace is this version (post bug-fix) always returns the correct annual return. Most SIP returns and SIP goal calculators suffer from one flaw: If the assumed annual return is 12% they assume the monthly return is 12%/12 = 1%. This is incorrect. The correct monthly return is = (1+12%)12 -1 = 0.949%. You may think this is a small difference. However when computing SIP corpus for a long tenure and when computing annual SIP returns (long and short tenures) it can make quite a big difference.

That said the correct return can easily be computed from the result of the RATE function. The roundabout method I have used might be of some interest/use to enthusiasts, developers and planners.

So I present two versions of the SIP returns calculator:

Download the SIP-returns-investor-version

Contents: versatile SIP corpus calculator, goal planner, SIP and lump sum returns (CAGR) calculator

Download the SIP-returns-pro-version

Contents: investor version + my SIP CAGR calculator which directly returns the correct annual return

Thanks to Mr. Sukhvinder Sidhu for pointing out a minor bug in the description.

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.

9 thoughts on “Mutual Fund SIP Returns Calculator

  1. pereira

    HI, wonder if you could help! I am new to Mutual funds. I want to switch from one to another with the same fund company but before doing so I want to calculate my gain or loss while switching/. how do I do this?

    Reply
    1. pattu

      Hi Pereira, You could use Excels XIRR calculator to do this.
      List all dates in one column. List all investments in the next column with a minus sign in front. In the last entry, enter date and maturity value.
      Now in another cell type, =XIRR(B1:B100,A1:A00)
      Here A1 to A100 are the dates and B1:100 the values.
      The answer will give you the compounded annual growth rate.
      If gives an error try
      =XIRR(B1:B100,A1:A00,guess)
      guess is something like a 0.1 or -0.1 to help XIRR.

      If that also does not work, let me know I will have a look.

      Reply
  2. Selv Thiru

    Pattu: Your calculator are amazing and been using your calculator for many. In MF SIP calculator do you have any which will provide XIRR value for SIP done for some investment period, stopped and valued after few years. Known data are SIP amount, start date and end date, value as of date.

    Reply
  3. Vivek

    sir, I clicked on above link, but i am unable to download it. The link does not exist. Pl assist me how should I download it. Thank you.

    Reply

Do let us know what you think about the article