The Most Authentic Guide on Personal Finance and Investments

Exclusive Personalized Financial Mentoring... at less than Rs.30/day... Email Today!!

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.

Total Pageviews