Include the Final Period in an Excel Dynamic Array Formula
Question details
The user needs a capital-budgeting array to include the initial period and the final-period working-capital change.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters the number of periods and expects a dynamic array covering period 0 through the final period.
- Observed behavior
- The formula returns an unexpected number of rows and omits the final-period working-capital change.
Check that the period count in B10 is a positive whole number and that the cells below the formula are empty. Confirm that your Excel version supports SEQUENCE and dynamic arrays.
Generate Period 0 Through the Final Period With SEQUENCE
Use this approach when the period count excludes the initial investment period but the output must include it.
A project with n periods needs n+1 rows when period 0 is included. The example below isolates the final-period working-capital change; it does not calculate the rest of the cash flow.
Enter the number of project periods in cell B10. For example, enter 5 to produce periods 0 through 5.
In an empty cell, enter =SEQUENCE(B10+1,,0,1). The formula spills a list from 0 through the value in B10.
In another empty cell, enter =IF(SEQUENCE(B10+1,,0,1)=B10,-15500,0). This returns -15500 in the final row and 0 in the other rows; replace -15500 with the applicable value or cell reference.
Add the resulting working-capital array to the corresponding cash-flow array in your existing formula, rather than replacing the other cash-flow components.

Include the Final Period in WPS Spreadsheets
In a WPS Spreadsheets version that supports SEQUENCE and dynamic arrays, generate period 0 through the final period and add the working-capital change to your cash-flow calculation.
- 1. Open the workbook: Open your capital-budgeting workbook in WPS Spreadsheets and locate the cell containing the period count.
- 2. Generate the period list: If the period count is in B10, enter =SEQUENCE(B10+1,,0,1) in an empty cell. Check that the list spills from 0 to the value in B10.
- 3. Identify the final-period value: In another empty cell, enter =IF(SEQUENCE(B10+1,,0,1)=B10,-15500,0), replacing -15500 with your applicable working-capital value.
- 4. Add it to the cash flows: Add the working-capital array to your existing cash-flow formula, then use File > Save As to save the workbook in XLSX format.

Frequently Asked Questions
Why does the formula need B10+1 rows?
If B10 contains the number of project periods, SEQUENCE(B10+1,,0,1) includes period 0 as well as periods 1 through B10.
Why does Excel show a #SPILL! error?
The output range may contain data or another obstruction. Select the formula cell and clear the cells in its intended spill range, then check the formula again.
How can I change the final-period amount?
Replace -15500 in the IF formula with your amount or a cell reference. Check whether your cash-flow convention requires a positive or negative value.
What if SEQUENCE is not available in my spreadsheet application?
Use a version that supports SEQUENCE, or enter 0 and 1 in adjacent cells and drag the fill handle to extend the period list manually. Add a final-period IF formula alongside it and extend the range when the period count changes.




