Calculation Issues

Include the Final Period in an Excel Dynamic Array Formula

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user needs a capital-budgeting array to include the initial period and the final-period working-capital change.

Include the Final Period in an Excel Dynamic Array Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the period count

Enter the number of project periods in cell B10. For example, enter 5 to produce periods 0 through 5.

2
Generate the periods

In an empty cell, enter =SEQUENCE(B10+1,,0,1). The formula spills a list from 0 through the value in B10.

3
Place the final-period change

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.

4
Incorporate the result

Add the resulting working-capital array to the corresponding cash-flow array in your existing formula, rather than replacing the other cash-flow components.

Generate Period 0 Through the Final Period With SEQUENCE
Check the sign: Use the sign required by your cash-flow convention. A working-capital recovery may be positive even if the example value is negative.
Calculate in WPS Spreadsheets

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. 1. Open the workbook: Open your capital-budgeting workbook in WPS Spreadsheets and locate the cell containing the period count.
  2. 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. 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. 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.
Generate the initial and final periods from a single period count.Use an IF condition to identify the final-period working-capital change.Open and save Microsoft Excel XLSX files.Work with a familiar spreadsheet interface.
microsoft office alternative - wps office

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.