The Most Authentic Guide on Personal Finance and Investments


Words of Wisdom : "A successful man is one who can lay a firm foundation with the bricks others have thrown at him." ~ David Brinkley

Simple trick to calculate SIP returns in excel

Systematic Investment Planning (SIP) is by far the best way to invest in mutual funds... especially equity mutual funds.

However, many investors often get confused when it comes to calculating how much they have actually earned from their SIP investment.

Simply dividing the total gains by the total investment is incorrect. It does not account for the different no. of days that each SIP amount has been invested for.

However, using excel sheet and a very simple strategy — known as Weighted Average Method — you can calculate the exact returns of your SIPs in just a matter of minutes.


A self-explanatory example of the same is given below.


InvestmentAmountNAVUnitsCurrentGain orNo. ofWeight

DateInvested
AllottedValueLossdays


ABCD=B/CE=D*MF=E-BG=L-AH=B*G

10-Jan-14     5,00015.56321.337      5,039           39      110550000

12-Feb-14     5,00015.24328.084      5,144         144        77385000

10-Mar-14     7,50015.63479.846      7,524           24        51382500

11-Apr-14     5,00014.89335.796      5,265         265        1995000


   22,500
1465.063    22,972         472
1412500



Current DateL30-Apr-14

Current NAVM15.68






Weighted Average No. of Days= Total H / Total BX      62.78



Average returns
= Total F / Total BY        2.10%
Weighted Average Returns= Y / X * 365
      12.20%


In fact, this weighted average methodology can be applied not only to your SIPs, but also to other such irregular investments.

You Learn A Lot By READING... And Even More By SHARING.

Share Button

Ignorance is like a SIGNED BLANK CHEQUE... anyone can MISUSE it.

Subscribe via Email
Books by Sanjay Matai
[Click on the Pic for more info on my books.]
Powered by Blogger.

How to overcome the fears about your financial future?

Will I earn enough money, to send my children to the best of colleges? Sandwiched between multiplying expenses on my growing kids and eld...

Total Pageviews