Seeking a solution for using Sequence function with a dynamic Step argument or one that would allow adjusting for future cash flows that are uneven or could be adjusted in any future periods.

Question

How can I setup a truly dynamic series of future cash flows using the Sequence function which would allow me to override or manually adjust a cash amount in any future period?
I understand this is not possible as the Step argument is fixed and only allows for growth in fixed increments. However, there must be a way to work around this. My first thought is to leverage another array w/in the step function somehow or maybe an index or array within the Start argument? I've been searching the web for a solution I could implement but have not come across one yet. Any and all suggestions are welcome. Thanks!

Answer 1

Hi @Schwantes, Steve,

Thank you for posting your question in the Microsoft Q&A forum.
Based on your sharing, it's right that the Sequence function is not suitable for generating a series of adjustable future cash flows because the Step argument is fixed.
As a workaround, I suggest you can try using the Sequence function for generating periods primarily and using a separate array for cash flow values. This is the setup I tested based on your request.

• Cell B1 is for Initial CF value, cell B2 is for Growth rate, cell B3 is the total Periods.
• Column F is the override period and column G will be override CF according to the period in column F
• First, I used Sequence to create the time periods in column A using the formula =SEQUENCE(B3,1,1,1)
• Next, I calculated the initial CF based on the CF formula: CF x (1 + GrowthRate)^(n-1). The formula I used is =B1*(1+B2)^(A6#-1)
• For the override CF column, the formula is =IFERROR(XLOOKUP(A6#,F:F,G:G,#N/A),B6#). Then with any data you input into the column F and G, it will be automatically replaced in the according CF in the table.

I hope the above information is helpful. You can try workaround and let us know how it went or if you need help with any steps. I'm here to support you and make sure everything is back to work properly. 

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.