Step up SIP value

[sahashumon]

Hi. I have a fixed SIP date for investment and would increase it periodically by fixed percentage every year from April onwards.

I currently use xirr formula but that requires me to input multiple dates in rows.

Is there any formula to calculate sip value after a fixed number of years basis assumed return and assumed step up sip every year from April to march. The sip date will be same date every month.

[XLent]

I don't know the XIRR calculation myself, however you should find you can create an array of dates without needing to physically add them to a range of cells, e.g:

Please Login or Register  to view this content.

would use an array of 10 annual dates, from April 1st 2018 through to April 1st 2027

[MrShorty]

It's not clear to me exactly what you are trying to calculate -- partly because I am not familiar with the "business math" behind these kind of calculations. I know that there is the XNPV() family of functions (like XIRR() see help for XNPV() https://support.office.com/en-us/art...b-d67c16b664b7 ) that requires a list of cash flows and the dates they occur on, because XNPV() does not assume a fixed period. There is the NPV() family of functions (https://support.office.com/en-us/art...b-ac28acf2a568 ) that assumes that the cash flows occur periodically (like monthly), so dates are not needed. Finally, there is the PV() family of functions (https://support.office.com/en-us/art...1-da16e8168cbd ) that assumes that the payment and period are both fixed. It would be up to you to know enough of the financial math side of the problem to know which "type" of present value/future value problem your scenario fits in (assuming it fits into one of these three types) so you can use the appropriate function from the right future value family of functions.