Is this a good solution to Excel's table header weaknesses?

[JenniferMurphy]

I am helping a friend plan for retirement. She wanted to get some idea of how much she could withdraw from her retirement savings to help the grandkids without putting her own needs at risk. I started to put together a table using the FV (future value) function, but ran into a problem. I set up her current retirement account balance as one constant and the annual interest rate as another. The table would then have rows of withdrawal amounts and columns of years. The Year 1 appreciation field is to show what amount would keep the retirement balance constant.

I got it to work, but only at the expense of a complex and error-prone formula to extract the number of years from the headers.

Future Value.xlsx
	C	D	E	F	G	H	I	J
3	$100,000	Initial balance
4	4.00%	Annual appreciation rate
5	$4,000	Year 1 appreciation
6
7	Annual Withdrawal	Year 1	Year 3	Year 7	Year 10	Year 15	Year 20	APR
8	$0	$104,000	$112,486	$131,593	$148,024	$180,094	$219,112	+4.00%
9	$2,000	$102,000	$106,243	$115,797	$124,012	$140,047	$159,556	+2.00%
10	$4,000	$100,000	$100,000	$100,000	$100,000	$100,000	$100,000	=0.00%
11	$5,000	$99,000	$96,878	$92,102	$87,994	$79,976	$70,222	-1.00%
12	$6,000	$98,000	$93,757	$84,203	$75,988	$59,953	$40,444	-2.00%
13	$7,000	$97,000	$90,635	$76,305	$63,982	$39,929	$10,666	-3.00%
Future Value

Cell Formulas
Range	Formula
C5	C5	=InitBal1*AnnualRate1
D8:D13	D8	=LET(n,RIGHT(TableFV1[[#Headers],[Year 1]],LEN(TableFV1[[#Headers],[Year 1]])-5),FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))
E8:E13	E8	=LET(n,RIGHT(TableFV1[[#Headers],[Year 3]],LEN(TableFV1[[#Headers],[Year 3]])-5),FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))
F8:F13	F8	=LET(n,RIGHT(TableFV1[[#Headers],[Year 7]],LEN(TableFV1[[#Headers],[Year 7]])-5),FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))
G8:G13	G8	=LET(n,RIGHT(TableFV1[[#Headers],[Year 10]],LEN(TableFV1[[#Headers],[Year 10]])-5),FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))
H8:H13	H8	=LET(n,RIGHT(TableFV1[[#Headers],[Year 15]],LEN(TableFV1[[#Headers],[Year 10]])-5),FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))
I8:I13	I8	=LET(n,RIGHT(TableFV1[[#Headers],[Year 20]],LEN(TableFV1[[#Headers],[Year 10]])-5),FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))
J8:J13	J8	=[@[Year 1]]/InitBal1-1
C9	C9	=Year1Appr1/2
C10	C10	=Year1Appr1

Named Ranges
Name	Refers To	Cells
'Future Value'!AnnualRate1	='Future Value'!$C$4	C5, D8:I13
'Future Value'!InitBal1	='Future Value'!$C$3	C5, D8:J13
'Future Value'!Year1Appr1	='Future Value'!$C$5	C9:C10

I tried a number of other solutions, most of which either didn't work or were worse than the problem. I finally came up with this. It uses a helper row, which raises the problem of explaining the need for that row. I could hide it or change the character color to white. But it works and the formulas inside the table are much more readable and less error prone.

Future Value.xlsx
	C	D	E	F	G	H	I	J
3	$100,000	Initial balance
4	4.00%	Annual appreciation rate
5	$4,000	Year 1 appreciation
6		1	3	7	10	15	20
7	Annual Withdrawal	Year 1	Year 3	Year 7	Year 10	Year 15	Year 20	APR
8	$0	$104,000	$112,486	$131,593	$148,024	$180,094	$219,112	+4.00%
9	$2,000	$102,000	$106,243	$115,797	$124,012	$140,047	$159,556	+2.00%
10	$4,000	$100,000	$100,000	$100,000	$100,000	$100,000	$100,000	=0.00%
11	$5,000	$99,000	$96,878	$92,102	$87,994	$79,976	$70,222	-1.00%
12	$6,000	$98,000	$93,757	$84,203	$75,988	$59,953	$40,444	-2.00%
13	$7,000	$97,000	$90,635	$76,305	$63,982	$39,929	$10,666	-3.00%
Future Value

Cell Formulas
Range	Formula
C5	C5	=InitBal1*AnnualRate1
D6:I6	D6	=LET(txt,OFFSET(D6,1,0),RIGHT(txt,LEN(txt)-5))
D8:I13	D8	=FV(AnnualRate1,D$6,[@[Annual Withdrawal]],-InitBal1,0)
J8:J13	J8	=[@[Year 1]]/InitBal1-1
C9	C9	=Year1Appr1/2
C10	C10	=Year1Appr1

Named Ranges
Name	Refers To	Cells
'Future Value'!AnnualRate1	='Future Value'!$C$4	C5, D8:I13
'Future Value'!InitBal1	='Future Value'!$C$3	C5, D8:J13
'Future Value'!Year1Appr1	='Future Value'!$C$5	C9:C10

Is there a better way?

 

[Cubist]

=TEXTAFTER(TableFV1[[#Headers],[Year 1]],"Year ")

 

[JenniferMurphy]

=TEXTAFTER(TableFV1[[#Headers],[Year 1]],"Year ")

That's great. When I first tried it, it didn't automatically update when I copied it across the header row. I had to manually go in and update the header text in the expression. I thought this would be better:

=TEXTAFTER(OFFSET(D6,1,0),"Year ")

But with your code, I can move that row up above the table so I don't have to hide it when printing the table.

Thanks

 

[zero269]

Hello Jennifer,
You can use @Cubist recommendation to replace your entire "n" variable in your "Year #" columns. See NEW v1 formula to see how I used to with your original formula... and then you can mark Cubist's post as the Solution.

=TEXTAFTER(TableFV1[[#Headers],[Year 1]],"Year ")

OLD:

=LET(
n,RIGHT(TableFV1[[#Headers],[Year 1]],LEN(TableFV1[[#Headers],[Year 1]])-5),
FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))

NEW v1:

=LET(
n, TEXTAFTER(TableFV1[[#Headers],[Year 1]],"Year "),
FV(AnnualRate1,n,[@[Annual Withdrawal]],-InitBal1,0))

That's great. When I first tried it, it didn't automatically update when I copied it across the header row. I had to manually go in and update the header text in the expression.

Because you want to lock your Annual Withdrawal column, you need to update the Table Reference so you can now drag the formula from D8 to I8 and then down to the last row.

To lock the column reference, change [@[Annual Withdrawal]] to [@[Annual Withdrawal]:[Annual Withdrawal]]. See NEW v2 formula below…

I deal with mostly Tables myself, so this was something that plagued me… and only recently learned how to lock them in.

Reference: Excel Table absolute reference for cells, columns, and rows

NEW v2:

=LET(
n, TEXTAFTER(TableFV1[[#Headers],[Year 1]],"Year "),
FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))

I assume this is still a "work in progress" as you have formulas in C9-C10, but static values in C8, C11-C13.
I'd recommend using the Accounting Number Format for your Table so the dollar signs are lined up on the left making the numbers more easily readable.

Best regards…

VBA Testing.xlsm
	C	D	E	F	G	H	I	J
3	$ 100,000	Initial balance
4	4.00%	Annual appreciation rate
5	$ 4,000	Year 1 appreciation
6
7	Annual Withdrawal	Year 1	Year 3	Year 7	Year 10	Year 15	Year 20	APR
8	$ -	$ 104,000	$ 112,486	$ 131,593	$ 148,024	$ 180,094	$ 219,112	4%
9	$ 2,000	$ 102,000	$ 106,243	$ 115,797	$ 124,012	$ 140,047	$ 159,556	2%
10	$ 4,000	$ 100,000	$ 100,000	$ 100,000	$ 100,000	$ 100,000	$ 100,000	0%
11	$ 5,000	$ 99,000	$ 96,878	$ 92,102	$ 87,994	$ 79,976	$ 70,222	-1%
12	$ 6,000	$ 98,000	$ 93,757	$ 84,203	$ 75,988	$ 59,953	$ 40,444	-2%
13	$ 7,000	$ 97,000	$ 90,635	$ 76,305	$ 63,982	$ 39,929	$ 10,666	-3%
14
15
16		Year 1	Year 3	Year 7
17		1	3	7
Future Value

Cell Formulas
Range	Formula
C5	C5	=InitBal1*AnnualRate1
D8:D13	D8	=LET( n, TEXTAFTER(TableFV1[[#Headers],[Year 1]],"Year "), FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))
E8:E13	E8	=LET( n, TEXTAFTER(TableFV1[[#Headers],[Year 3]],"Year "), FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))
F8:F13	F8	=LET( n, TEXTAFTER(TableFV1[[#Headers],[Year 7]],"Year "), FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))
G8:G13	G8	=LET( n, TEXTAFTER(TableFV1[[#Headers],[Year 10]],"Year "), FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))
H8:H13	H8	=LET( n, TEXTAFTER(TableFV1[[#Headers],[Year 15]],"Year "), FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))
I8:I13	I8	=LET( n, TEXTAFTER(TableFV1[[#Headers],[Year 20]],"Year "), FV(AnnualRate1,n,TableFV1[@[Annual Withdrawal]:[Annual Withdrawal]],-InitBal1,0))
J8:J13	J8	=[@[Year 1]]/InitBal1-1
D17	D17	=TEXTAFTER(TableFV1_2[[#Headers],[Year 1]],"Year ")
E17	E17	=TEXTAFTER(TableFV1_2[[#Headers],[Year 3]],"Year ")
F17	F17	=TEXTAFTER(TableFV1_2[[#Headers],[Year 7]],"Year ")

Named Ranges
Name	Refers To	Cells
AnnualRate1	='Future Value'!$C$4	C5, D8:I13
InitBal1	='Future Value'!$C$3	C5, D8:J13