I’m trying to figure out CAGR for NIFTY for past 5 years if invested in the form of monthly SIP,
If 10k is invested every month in NIFTY since past 5 years(say from 1st Jan 2014), total investment would be 6Lakhs. How much would’ve returned ?
I’m not an expert at this. Just did layman calculations, I got values from Google Finance Sheet function and filtered values for every 4 weeks(rough assumption 1month = 4 weeks), but I’m unable to figure out XIRR function properly at the end to calculate return. Any ideas ?
From 2014 to 2018 was Strong Bull market. Nifty went from 5k to 11k. This may not be repeated for next 5 years.
You need to backrest from 2005 to 2018 - this includes extreme bear and bull periods, gives you more wider view
Open an excel sheet.
Step1: In column A, enter the transaction dates on the left side
Step 2: In column B, enter SIP figure of 5000 as a negative figure as it’s an outflow
Step 3: Against the redemption date (Column A), enter redemption amount (Column B) (31000)
Step 4: In the box below 31000, type in:
=XIRR (B1:B7, A1:A7)*100 and hit enter
XIRR, or the return on ones SIP investments, comes to 11.88 per cent.
You should consider the closing price of Monthly candle for calculation purpose.
Anyways, if you wanted the returns calculation you may consider NIFTY price in terms of Units with a base of 10K which is your per month invested capital, like what ETFs/MFs do. Like when NIFTY price was 9K, you get 1.111111111111111 units and so on. Keep accumulating the values of NIFTY at a particular day of the month, and check the accumulated units value in current day NIFTY spot price. Hope it’s clear.