Are these retirement calculations correct?

[JenniferMurphy]

I am helping a friend plan for retirement. She wants to know how much she can give away (to charities and the grandkids) and still have enough to live on. I found that Excel has the FV (Future Value) function. I used it to create the table in the mini-sheet below. It looks like it is correct, but I would be grateful for any comments, suggestions, or corrections.

In particular, I would be interested in any suggestions for a better implementation than what I have done to extract the number of years from the column headings.

Thanks

Donations & RMD.xlsx
	C	D	E	F	G	H	I
2		$10,000	Initial balance
3
4	Appreciation Rate	Appreciation Amount	Annual Withdrawal	Year 1	Year 3	Year 5	Year 10
5	6%	$600	$400	$10,200	$10,637	$11,127	$12,636
6	6%	$600	$600	$10,000	$10,000	$10,000	$10,000
7	6%	$600	$800	$9,800	$9,363	$8,873	$7,364
8	6%	$600	$1,000	$9,600	$8,727	$7,745	$4,728
9	8%	$800	$600	$10,200	$10,649	$11,173	$12,897
10	8%	$800	$800	$10,000	$10,000	$10,000	$10,000
11	8%	$800	$1,000	$9,800	$9,351	$8,827	$7,103
12	8%	$800	$1,200	$9,600	$8,701	$7,653	$4,205
Sally

Cell Formulas
Range	Formula
F5:F12	F5	=LET(n,RIGHT(Table1820[[#Headers],[Year 1]],LEN(Table1820[[#Headers],[Year 1]])-5),FV([@[Appreciation Rate]],n,[@[Annual Withdrawal]],-Balance0,0))
G5:G12	G5	=LET(n,RIGHT(Table1820[[#Headers],[Year 3]],LEN(Table1820[[#Headers],[Year 3]])-5),FV([@[Appreciation Rate]],n,[@[Annual Withdrawal]],-Balance0,0))
H5:H12	H5	=LET(n,RIGHT(Table1820[[#Headers],[Year 5]],LEN(Table1820[[#Headers],[Year 5]])-5),FV([@[Appreciation Rate]],n,[@[Annual Withdrawal]],-Balance0,0))
I5:I12	I5	=LET(n,RIGHT(Table1820[[#Headers],[Year 10]],LEN(Table1820[[#Headers],[Year 10]])-5),FV([@[Appreciation Rate]],n,[@[Annual Withdrawal]],-Balance0,0))
D5:D12	D5	=Balance0*[@[Appreciation Rate]]

Named Ranges
Name	Refers To	Cells
Balance0	=Sally!$D$2	D5:D12, F5:I12

 

[alansidman]

Jennifer
Thought you might like to see a retirement calculator that I built about 15 years ago. You may be able to modify this to help with your project.

Planning for Retirement Worksheet

Attached is a worksheet that I developed with a friend. It answers the question for US taxpayers whether you have saved enough for retirement. The Input form is self explanatory. For those of you in other countries, I am sure you could adapt this to your particular environment.

www.excelforum.com

 

[JenniferMurphy]

Jennifer
Thought you might like to see a retirement calculator that I built about 15 years ago. You may be able to modify this to help with your project.

Planning for Retirement Worksheet

Attached is a worksheet that I developed with a friend. It answers the question for US taxpayers whether you have saved enough for retirement. The Input form is self explanatory. For those of you in other countries, I am sure you could adapt this to your particular environment.

www.excelforum.com

I downloaded the file. I noticed that it is an .xls, not .xlsx file. That's probably not a problem. But when I tried to run it, I first got a Protected View warning. After I clicked on Enable Editing, I got a Security Risk warning that macros were blocked.

It looks like you did a lot of work and it includes a lot of options. Here's the list:

This is way more than my friend needs or can really understand. But thanks for the effort.

 

[MARK858]

After I clicked on Enable Editing, I got a Security Risk warning that macros were blocked

You'll get that warning with every file that you download that contain macro's (it's the same as when you downloaded XL2BB and Unblocking is the same procedure as you used then)

 

[FormulasOnly]

[In general, not just Excel]
I suggest to care for the expected cost (i.e. every 10 years a new fridge, freezer, washing machine, dish washer, TV, ...) as well as for the unexpected: what is the necessary capital to protect yourself with AA or AAA safety against bankruptcy?

 

[JenniferMurphy]

[In general, not just Excel]
I suggest to care for the expected cost (i.e. every 10 years a new fridge, freezer, washing machine, dish washer, TV, ...) as well as for the unexpected: what is the necessary capital to protect yourself with AA or AAA safety against bankruptcy?

Yes. Of course, comprehensive retirement planning involved a lot more than this one table encompasses. She knows that. This table is just to help her see how much she can spend each year and how that will affect the balance in her retirement savings. My purpose in this thread was to see if anyone has any comments on the math and, especially, the way I used the headings to drive the calculations.

Thanks

 

[JenniferMurphy]

After reviewing alansidman's sheet and doing some experimenting, I came up with the table in the minisheet below. It will calculate the remaining balance (future value) after a number of years. I'd appreciate any comments, especially:

• I do not like the way I extract the number from each heading to get the number of years. Is there a better way?
• Is there a simpler way that the mind-boggling expression in the year columns?

Maybe the solution is a form. I'm going to look into that.

Future Value.xlsx
	C	D	E	F	G	H
2	$10,000	Initial balance
3	4.00%	Annual appreciation rate
4	$400	Year 1 appreciation
5
6	Annual Withdrawal	Year 1	Year 3	Year 5	Year 10	Annual Trend
7	$0	$10,400	$11,249	$12,167	$14,802	+4.00%
8	$200	$10,200	$10,624	$11,083	$12,401	+2.00%
9	$400	$10,000	$10,000	$10,000	$10,000	=0.00%
10	$700	$9,700	$9,064	$8,375	$6,398	-3.00%
11	$1,000	$9,400	$8,127	$6,750	$2,796	-6.00%
12	$1,200	$9,200	$7,503	$5,667	$395	-8.00%
Future Value

Cell Formulas
Range	Formula
C4	C4	=Balance0*AnnualRate
D7:D12	D7	=LET(n,RIGHT(TableFV[[#Headers],[Year 1]],LEN(TableFV[[#Headers],[Year 1]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
E7:E12	E7	=LET(n,RIGHT(TableFV[[#Headers],[Year 3]],LEN(TableFV[[#Headers],[Year 3]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
F7:F12	F7	=LET(n,RIGHT(TableFV[[#Headers],[Year 5]],LEN(TableFV[[#Headers],[Year 5]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
G7:G12	G7	=LET(n,RIGHT(TableFV[[#Headers],[Year 10]],LEN(TableFV[[#Headers],[Year 10]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
H7:H12	H7	=[@[Year 1]]/Balance0-1

Named Ranges
Name	Refers To	Cells
'Future Value'!AnnualRate	='Future Value'!$C$3	C4, D7:G12
'Future Value'!Balance0	='Future Value'!$C$2	C4, D7:H12

Future Value.xlsx
	C	D	E	F	G	H
2	$10,000	Initial balance
3	4.00%	Annual appreciation rate
4	$400	Year 1 appreciation
5
6	Annual Withdrawal	Year 1	Year 3	Year 5	Year 10	Annual Trend
7	$0	$10,400	$11,249	$12,167	$14,802	+4.00%
8	$200	$10,200	$10,624	$11,083	$12,401	+2.00%
9	$400	$10,000	$10,000	$10,000	$10,000	=0.00%
10	$700	$9,700	$9,064	$8,375	$6,398	-3.00%
11	$1,000	$9,400	$8,127	$6,750	$2,796	-6.00%
12	$1,200	$9,200	$7,503	$5,667	$395	-8.00%
Future Value

Cell Formulas
Range	Formula
C4	C4	=Balance0*AnnualRate
D7:D12	D7	=LET(n,RIGHT(TableFV[[#Headers],[Year 1]],LEN(TableFV[[#Headers],[Year 1]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
E7:E12	E7	=LET(n,RIGHT(TableFV[[#Headers],[Year 3]],LEN(TableFV[[#Headers],[Year 3]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
F7:F12	F7	=LET(n,RIGHT(TableFV[[#Headers],[Year 5]],LEN(TableFV[[#Headers],[Year 5]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
G7:G12	G7	=LET(n,RIGHT(TableFV[[#Headers],[Year 10]],LEN(TableFV[[#Headers],[Year 10]])-5),FV(AnnualRate,n,[@[Annual Withdrawal]],-Balance0,0))
H7:H12	H7	=[@[Year 1]]/Balance0-1

Named Ranges
Name	Refers To	Cells
'Future Value'!AnnualRate	='Future Value'!$C$3	C4, D7:G12
'Future Value'!Balance0	='Future Value'!$C$2	C4, D7:H12