Assuming income and spending both occur at the beginning of a time period (e.g., pension check arrival and bill paying for the month occur on the 1st of the month), one can use the formulas for
Future value of an annuity due and
Future value of a growing annuity due to get an analytic solution.
P = Pension amount
S = Spending amount for the first time period
r = investment rate of return (including inflation)
i = inflation rate of spending increase
n = number of time periods
The cumulative pension amount is P*((1+r)^(n+1)-(1+r))/r.
The cumulative spending amount (if r<>i) is S*(1+r)*((1+r)^n-(1+i)^n)/(r-i).
The cumulative spending amount (if r=i) is S*n*(1+r)^n.
Equating cumulative pension to cumulative spending, and solving for S, we get
S=P*(r-i)*((1+r)^n-1)/(r*((1+r)^n-(1+i)^n)) if r<>i
S=P*((r+1)^n-1)/(n*r*(1+r)^(n-1)) if r=i
One can put the following into cells A1:B5 of a spreadsheet
r 0.07
i 0.03
n 16
P 50000
S =IF(B1=B2,B4*((1+B1)^B3-1)/(B3*B1*(1+B1)^(B3-1)),B4*((1+B1)^B3-1)*(B1-B2)/(B1*((1+B1)^B3-(1+B2)^B3)))
Then the following into cells A7:D10
Time Excess Income Ending balance Next spending
1 =B4-B5 =B8*(1+$B$1) =B5*(1+B2)
2 =($B$4-D8) =(C8+B9)*(1+$B$1) =D8*(1+$B$2)
3 =($B$4-D9) =(C9+B10)*(1+$B$1) =D9*(1+$B$2)
...and then copy the last two rows down as far as one wishes.
The "ending balance" column should be ~0 for whichever time row is chosen in cell A3.
E.g., for the inputs shown above, the initial spending for time periods of 1 to 9 is
1 $50,000
2 $49,286
3 $48,594
4 $47,925
5 $47,277
6 $46,650
7 $46,043
8 $45,456
9 $44,888
Using different assumptions may lead to different answers.