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.
Register for the Hyderabad DIY Investor Workshop Nov 27th 2016
Check out the latest mutual fund returns listing
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/- (Rs. 359/- at Amazon.in). Read more about the book and pre-order now! |
It is very useful article for the advisors. Thanks
Thank you. Do check out the other mutual fund resources and stay tuned for more updates!
many thanks !
welcome! stay tuned!
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?
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.
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.
The information presented is very precise and quite helpful. Highly appreciate your efforts!
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.