Step up SIP calculator

There is no direct formula, but you can calculate the returns on a step-up SIP using a spreadsheet.

Here’s a sample case. You want to start an SIP by investing 10000 per month for 15 years. You want to increase the monthly amount by 10% every year. You expect a compound growth rate of 12% per annum.

Here is how the workings will look like on a spreadsheet.

Step 1: In cell A1, write Principal. In front of it, in B1, write 10,000 In cell A2, write Expected Rate of Return. In front of it, in B2, write this: “=12%/12”. In cell A3, write Period. In front of it, B3, write this: “=15*12”. You divide the expected return by 12 and you multiply the period by 12 because you are investing every month. A year has 12 months and your investment is expected to grow every month. In cell A4, write Step-up rate. In front of it, in B4, write 10%.

Step 2: Name column D as Months in Cell D1. Plot serial numbers from 0 to 180 in the cells D2 to D182.

Step 3: Name column E as SIP Amount in Cell E1. In E2, in front of 0 from Column D, write “=B1”. It should show 10,000, as you wrote that number in B1. In E3, write “=E2” and drag it down all the way to E181. E182, which is in front of D182, should be left empty. All the cells from E2 to E181 should show 10,000.

Step 4: Do this step carefully. Refer to the numbering in column D and in front of every multiple of 12, in column E, edit the formula to add “*(1+10%)”.

For example, in front of 12, in Cell E14, the existing formula from Step 3 will be “=E13”. Edit it to this: =E13*(1+10%).

In front of 24, In Cell E26, replace the formula to =E25*(1+10%). Do this for every multiple of 12 until 168 in Cell E170.

Step 5: Name Column F as Investment Value in F1. Leave F2 empty. In F3, write this: “(E2+F2) *(1+$B$2)”. Drag this column down to F182.

What you are basically doing with any cell between F3 and F182 in the fifth step is adding the investment value so far with the new investment of the month and multiplying it by the assumed monthly growth rate.

The value you will see in F182 is the expected future value of your step-up SIP investment. In this sample case, the future value will be 8683849.