Increment a Calculation Automatically with ROW or COLUMN Step Ladders

One base and one step control every scenario

B2 is the base rate and B3 is the additive step. Starting at D2, the row offset is zero, so the first rate equals the base. Each following row adds one step. A step of 0.005 means 0.5 percentage points (50 basis points), not a 0.5% relative increase.

This is an arithmetic sensitivity ladder, not compounded growth. The optional cost column is principal × scenario rate for one simplified period, without amortization, fees, timing or compounding. It is not a loan payment quote.

Negative and zero steps are valid for a scenario ladder. Rate cells retain full precision; apply percentage formatting for display. Only the illustrative cost is rounded to two decimals.

Vertical ladder: enter in D2 and fill to D6

The base is returned in the first output row. The dollar signs keep base, step and anchor fixed when copying.

=IF(COUNT($B$2:$B$3)<>2,"Enter numeric base and step",$B$2+(ROW(D2)-ROW($D$2))*$B$3)

Illustrative simple-period cost: E2 filled down

Uses the unrounded rate in column D and a numeric nonnegative principal in B4.

=IF(COUNT(D2,$B$4)<>2,"Enter numeric rate and principal",IF($B$4<0,"Principal must be nonnegative",ROUND($B$4*D2,2)))

Horizontal ladder: D8 filled right to H8

COLUMN replaces ROW; the first output column is D. This generates the same scenario sequence horizontally.

=IF(COUNT($B$2:$B$3)<>2,"Enter numeric base and step",$B$2+(COLUMN(D8)-COLUMN($D$8))*$B$3)

Further reading

• Microsoft Support — ROW function