NIFTY as a SIP - CAGR returns

General
1

I’m trying to figure out CAGR for NIFTY for past 5 years if invested in the form of monthly SIP,

Example:
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 ?

Here’s the sheet:

2

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

3

sure. once I get to know how to do it for 5 year or even lesser period, it can be applied to any time period.

But I was wondering if anyone knows how to do this calculation.

5
6

Open an excel sheet.
:diamonds: Step1: In column A, enter the transaction dates on the left side
:diamonds: Step 2: In column B, enter SIP figure of 5000 as a negative figure as it’s an outflow
:diamonds: Step 3: Against the redemption date (Column A), enter redemption amount (Column B) (31000)
:diamonds: 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.

Source

7

Thanks,I saw this on ET already. This will work if I know 31000 is my return. My issue is based on Nifty’s monthly numbers, how do I know it’s 31,000 ?

8

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.