Retirement Planning

[singcbl]

Hi there,
I am planning for my retirement and needed to know how much cash I need to set aside in a special account at the beginning. This special account generates up to 6% interest rate on the first $30,000, up to 5% interest rate on the next $30,000 and the rest of the deposit at 4% interest rate per annum. I intend to have a monthly drawdown of $2,500 over a period of 22 years until the special account is fully depleted.
Thank you all for any help you can offer to solve my question.

 

[JamesCanale]

Solved by iteration of at A10

MrExcelPlayground11.xlsx
	A	B
1	$30,000.00	6%
2	$30,000.00	5%
3		4%
4
5	$2,500.00	22
6		264
7	$660,000.00
8
9
10	$425,619.41	$0.02
11	$424,613.14	1
12	$423,603.52	2
13	$422,590.53	3
14	$421,574.17	4
15	$420,554.41	5
16	$419,531.26	6
17	$418,504.70	7
18	$417,474.71	8
19	$416,441.30	9
20	$415,404.43	10
21	$414,364.12	11
22	$413,320.33	12
23	$412,273.06	13
24	$411,222.31	14
25	$410,168.05	15
26	$409,110.27	16
27	$408,048.98	17
28	$406,984.14	18
29	$405,915.75	19
30	$404,843.81	20
31	$403,768.28	21
32	$402,689.18	22
33	$401,606.48	23
34	$400,520.16	24
35	$399,430.23	25
36	$398,336.67	26
37	$397,239.45	27
38	$396,138.59	28
39	$395,034.05	29
40	$393,925.83	30
41	$392,813.91	31
42	$391,698.29	32
43	$390,578.96	33
44	$389,455.88	34
45	$388,329.07	35
46	$387,198.50	36
47	$386,064.16	37
48	$384,926.04	38
49	$383,784.13	39
50	$382,638.41	40
51	$381,488.87	41
52	$380,335.50	42
53	$379,178.29	43
54	$378,017.21	44
55	$376,852.27	45
56	$375,683.45	46
57	$374,510.72	47
58	$373,334.09	48
59	$372,153.54	49
60	$370,969.05	50
61	$369,780.62	51
62	$368,588.22	52
63	$367,391.85	53
64	$366,191.48	54
65	$364,987.12	55
66	$363,778.75	56
67	$362,566.34	57
68	$361,349.90	58
69	$360,129.40	59
70	$358,904.83	60
71	$357,676.18	61
72	$356,443.43	62
73	$355,206.58	63
74	$353,965.60	64
75	$352,720.48	65
76	$351,471.22	66
77	$350,217.79	67
78	$348,960.18	68
79	$347,698.38	69
80	$346,432.38	70
81	$345,162.15	71
82	$343,887.69	72
83	$342,608.98	73
84	$341,326.01	74
85	$340,038.77	75
86	$338,747.23	76
87	$337,451.39	77
88	$336,151.23	78
89	$334,846.73	79
90	$333,537.89	80
91	$332,224.68	81
92	$330,907.09	82
93	$329,585.12	83
94	$328,258.73	84
95	$326,927.93	85
96	$325,592.69	86
97	$324,253.00	87
98	$322,908.84	88
99	$321,560.21	89
100	$320,207.07	90
101	$318,849.43	91
102	$317,487.26	92
103	$316,120.55	93
104	$314,749.29	94
105	$313,373.45	95
106	$311,993.03	96
107	$310,608.01	97
108	$309,218.37	98
109	$307,824.09	99
110	$306,425.17	100
111	$305,021.59	101
112	$303,613.33	102
113	$302,200.37	103
114	$300,782.71	104
115	$299,360.32	105
116	$297,933.19	106
117	$296,501.30	107
118	$295,064.63	108
119	$293,623.18	109
120	$292,176.93	110
121	$290,725.85	111
122	$289,269.94	112
123	$287,809.17	113
124	$286,343.53	114
125	$284,873.01	115
126	$283,397.59	116
127	$281,917.25	117
128	$280,431.97	118
129	$278,941.74	119
130	$277,446.55	120
131	$275,946.37	121
132	$274,441.19	122
133	$272,931.00	123
134	$271,415.77	124
135	$269,895.49	125
136	$268,370.14	126
137	$266,839.71	127
138	$265,304.17	128
139	$263,763.52	129
140	$262,217.73	130
141	$260,666.79	131
142	$259,110.68	132
143	$257,549.38	133
144	$255,982.88	134
145	$254,411.15	135
146	$252,834.19	136
147	$251,251.97	137
148	$249,664.48	138
149	$248,071.69	139
150	$246,473.60	140
151	$244,870.18	141
152	$243,261.41	142
153	$241,647.28	143
154	$240,027.77	144
155	$238,402.87	145
156	$236,772.54	146
157	$235,136.79	147
158	$233,495.57	148
159	$231,848.89	149
160	$230,196.72	150
161	$228,539.05	151
162	$226,875.84	152
163	$225,207.09	153
164	$223,532.79	154
165	$221,852.89	155
166	$220,167.40	156
167	$218,476.30	157
168	$216,779.55	158
169	$215,077.15	159
170	$213,369.07	160
171	$211,655.30	161
172	$209,935.82	162
173	$208,210.61	163
174	$206,479.64	164
175	$204,742.91	165
176	$203,000.38	166
177	$201,252.05	167
178	$199,497.89	168
179	$197,737.88	169
180	$195,972.01	170
181	$194,200.25	171
182	$192,422.58	172
183	$190,638.99	173
184	$188,849.46	174
185	$187,053.95	175
186	$185,252.47	176
187	$183,444.98	177
188	$181,631.46	178
189	$179,811.90	179
190	$177,986.27	180
191	$176,154.56	181
192	$174,316.74	182
193	$172,472.80	183
194	$170,622.71	184
195	$168,766.45	185
196	$166,904.00	186
197	$165,035.35	187
198	$163,160.47	188
199	$161,279.34	189
200	$159,391.93	190
201	$157,498.24	191
202	$155,598.23	192
203	$153,691.89	193
204	$151,779.20	194
205	$149,860.13	195
206	$147,934.67	196
207	$146,002.78	197
208	$144,064.46	198
209	$142,119.67	199
210	$140,168.40	200
211	$138,210.63	201
212	$136,246.33	202
213	$134,275.49	203
214	$132,298.07	204
215	$130,314.07	205
216	$128,323.45	206
217	$126,326.19	207
218	$124,322.28	208
219	$122,311.69	209
220	$120,294.39	210
221	$118,270.37	211
222	$116,239.61	212
223	$114,202.07	213
224	$112,157.75	214
225	$110,106.61	215
226	$108,048.63	216
227	$105,983.79	217
228	$103,912.07	218
229	$101,833.44	219
230	$99,747.89	220
231	$97,655.38	221
232	$95,555.90	222
233	$93,449.42	223
234	$91,335.92	224
235	$89,215.37	225
236	$87,087.75	226
237	$84,953.05	227
238	$82,811.22	228
239	$80,662.26	229
240	$78,506.14	230
241	$76,342.82	231
242	$74,172.30	232
243	$71,994.54	233
244	$69,809.52	234
245	$67,617.22	235
246	$65,417.61	236
247	$63,210.67	237
248	$60,996.37	238
249	$58,774.69	239
250	$56,544.59	240
251	$54,305.19	241
252	$52,056.46	242
253	$49,798.36	243
254	$47,530.86	244
255	$45,253.90	245
256	$42,967.46	246
257	$40,671.49	247
258	$38,365.96	248
259	$36,050.81	249
260	$33,726.02	250
261	$31,391.55	251
262	$29,047.35	252
263	$26,692.58	253
264	$24,326.05	254
265	$21,947.68	255
266	$19,557.42	256
267	$17,155.20	257
268	$14,740.98	258
269	$12,314.68	259
270	$9,876.26	260
271	$7,425.64	261
272	$4,962.77	262
273	$2,487.58	263
274	$0.02	264
Sheet21

Cell Formulas
Range	Formula
B6	B6	=B5*12
A7	A7	=B6*A5
B10	B10	=A274
A11:A274	A11	=A10+MIN(A10,$A$1)*($B$1/12)+MAX(0,MIN(A10-$A$1,$A$2))*($B$2/12)+MAX(0,A10-$A$1-$A$2)*($B$3/12)-$A$5

 

[singcbl]

Solved by iteration of at A10

MrExcelPlayground11.xlsx
	A	B
1	$30,000.00	6%
2	$30,000.00	5%
3		4%
4
5	$2,500.00	22
6		264
7	$660,000.00
8
9
10	$425,619.41	$0.02
11	$424,613.14	1
12	$423,603.52	2
13	$422,590.53	3
14	$421,574.17	4
15	$420,554.41	5
16	$419,531.26	6
17	$418,504.70	7
18	$417,474.71	8
19	$416,441.30	9
20	$415,404.43	10
21	$414,364.12	11
22	$413,320.33	12
23	$412,273.06	13
24	$411,222.31	14
25	$410,168.05	15
26	$409,110.27	16
27	$408,048.98	17
28	$406,984.14	18
29	$405,915.75	19
30	$404,843.81	20
31	$403,768.28	21
32	$402,689.18	22
33	$401,606.48	23
34	$400,520.16	24
35	$399,430.23	25
36	$398,336.67	26
37	$397,239.45	27
38	$396,138.59	28
39	$395,034.05	29
40	$393,925.83	30
41	$392,813.91	31
42	$391,698.29	32
43	$390,578.96	33
44	$389,455.88	34
45	$388,329.07	35
46	$387,198.50	36
47	$386,064.16	37
48	$384,926.04	38
49	$383,784.13	39
50	$382,638.41	40
51	$381,488.87	41
52	$380,335.50	42
53	$379,178.29	43
54	$378,017.21	44
55	$376,852.27	45
56	$375,683.45	46
57	$374,510.72	47
58	$373,334.09	48
59	$372,153.54	49
60	$370,969.05	50
61	$369,780.62	51
62	$368,588.22	52
63	$367,391.85	53
64	$366,191.48	54
65	$364,987.12	55
66	$363,778.75	56
67	$362,566.34	57
68	$361,349.90	58
69	$360,129.40	59
70	$358,904.83	60
71	$357,676.18	61
72	$356,443.43	62
73	$355,206.58	63
74	$353,965.60	64
75	$352,720.48	65
76	$351,471.22	66
77	$350,217.79	67
78	$348,960.18	68
79	$347,698.38	69
80	$346,432.38	70
81	$345,162.15	71
82	$343,887.69	72
83	$342,608.98	73
84	$341,326.01	74
85	$340,038.77	75
86	$338,747.23	76
87	$337,451.39	77
88	$336,151.23	78
89	$334,846.73	79
90	$333,537.89	80
91	$332,224.68	81
92	$330,907.09	82
93	$329,585.12	83
94	$328,258.73	84
95	$326,927.93	85
96	$325,592.69	86
97	$324,253.00	87
98	$322,908.84	88
99	$321,560.21	89
100	$320,207.07	90
101	$318,849.43	91
102	$317,487.26	92
103	$316,120.55	93
104	$314,749.29	94
105	$313,373.45	95
106	$311,993.03	96
107	$310,608.01	97
108	$309,218.37	98
109	$307,824.09	99
110	$306,425.17	100
111	$305,021.59	101
112	$303,613.33	102
113	$302,200.37	103
114	$300,782.71	104
115	$299,360.32	105
116	$297,933.19	106
117	$296,501.30	107
118	$295,064.63	108
119	$293,623.18	109
120	$292,176.93	110
121	$290,725.85	111
122	$289,269.94	112
123	$287,809.17	113
124	$286,343.53	114
125	$284,873.01	115
126	$283,397.59	116
127	$281,917.25	117
128	$280,431.97	118
129	$278,941.74	119
130	$277,446.55	120
131	$275,946.37	121
132	$274,441.19	122
133	$272,931.00	123
134	$271,415.77	124
135	$269,895.49	125
136	$268,370.14	126
137	$266,839.71	127
138	$265,304.17	128
139	$263,763.52	129
140	$262,217.73	130
141	$260,666.79	131
142	$259,110.68	132
143	$257,549.38	133
144	$255,982.88	134
145	$254,411.15	135
146	$252,834.19	136
147	$251,251.97	137
148	$249,664.48	138
149	$248,071.69	139
150	$246,473.60	140
151	$244,870.18	141
152	$243,261.41	142
153	$241,647.28	143
154	$240,027.77	144
155	$238,402.87	145
156	$236,772.54	146
157	$235,136.79	147
158	$233,495.57	148
159	$231,848.89	149
160	$230,196.72	150
161	$228,539.05	151
162	$226,875.84	152
163	$225,207.09	153
164	$223,532.79	154
165	$221,852.89	155
166	$220,167.40	156
167	$218,476.30	157
168	$216,779.55	158
169	$215,077.15	159
170	$213,369.07	160
171	$211,655.30	161
172	$209,935.82	162
173	$208,210.61	163
174	$206,479.64	164
175	$204,742.91	165
176	$203,000.38	166
177	$201,252.05	167
178	$199,497.89	168
179	$197,737.88	169
180	$195,972.01	170
181	$194,200.25	171
182	$192,422.58	172
183	$190,638.99	173
184	$188,849.46	174
185	$187,053.95	175
186	$185,252.47	176
187	$183,444.98	177
188	$181,631.46	178
189	$179,811.90	179
190	$177,986.27	180
191	$176,154.56	181
192	$174,316.74	182
193	$172,472.80	183
194	$170,622.71	184
195	$168,766.45	185
196	$166,904.00	186
197	$165,035.35	187
198	$163,160.47	188
199	$161,279.34	189
200	$159,391.93	190
201	$157,498.24	191
202	$155,598.23	192
203	$153,691.89	193
204	$151,779.20	194
205	$149,860.13	195
206	$147,934.67	196
207	$146,002.78	197
208	$144,064.46	198
209	$142,119.67	199
210	$140,168.40	200
211	$138,210.63	201
212	$136,246.33	202
213	$134,275.49	203
214	$132,298.07	204
215	$130,314.07	205
216	$128,323.45	206
217	$126,326.19	207
218	$124,322.28	208
219	$122,311.69	209
220	$120,294.39	210
221	$118,270.37	211
222	$116,239.61	212
223	$114,202.07	213
224	$112,157.75	214
225	$110,106.61	215
226	$108,048.63	216
227	$105,983.79	217
228	$103,912.07	218
229	$101,833.44	219
230	$99,747.89	220
231	$97,655.38	221
232	$95,555.90	222
233	$93,449.42	223
234	$91,335.92	224
235	$89,215.37	225
236	$87,087.75	226
237	$84,953.05	227
238	$82,811.22	228
239	$80,662.26	229
240	$78,506.14	230
241	$76,342.82	231
242	$74,172.30	232
243	$71,994.54	233
244	$69,809.52	234
245	$67,617.22	235
246	$65,417.61	236
247	$63,210.67	237
248	$60,996.37	238
249	$58,774.69	239
250	$56,544.59	240
251	$54,305.19	241
252	$52,056.46	242
253	$49,798.36	243
254	$47,530.86	244
255	$45,253.90	245
256	$42,967.46	246
257	$40,671.49	247
258	$38,365.96	248
259	$36,050.81	249
260	$33,726.02	250
261	$31,391.55	251
262	$29,047.35	252
263	$26,692.58	253
264	$24,326.05	254
265	$21,947.68	255
266	$19,557.42	256
267	$17,155.20	257
268	$14,740.98	258
269	$12,314.68	259
270	$9,876.26	260
271	$7,425.64	261
272	$4,962.77	262
273	$2,487.58	263
274	$0.02	264
Sheet21

Cell Formulas
Range	Formula
B6	B6	=B5*12
A7	A7	=B6*A5
B10	B10	=A274
A11:A274	A11	=A10+MIN(A10,$A$1)*($B$1/12)+MAX(0,MIN(A10-$A$1,$A$2))*($B$2/12)+MAX(0,A10-$A$1-$A$2)*($B$3/12)-$A$5

James,
Thank you for your solution to my query. It is of great help to me.

 

[singcbl]

After looking into the detail of the solution provided by James, I wonder if there is an easier way to get the answer.