Withdrawing balances from Bank Accounts

[Rayliew]

Hi there,

Thank you for reading my post. I needed some help here.

Inside the spreadsheet are two bank accounts with different balances, Bank A = $180,000 and Bank B = $150,000. I am looking to withdraw them every year from age 70, starting from Bank A first then to Bank B, until the balance in Bank B depletes to $0.

I have also included a note in the spreadsheet.

Thank you.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will do it for you this time.)

https://www.mrexcel.com/forum/excel-...-accounts.html

[AliGW]

In your short time here you have already broken three of our rules. Please take a moment to read our rules before posting again: https://www.excelforum.com/forum-rul...rum-rules.html

Thank you.

[joeu2004]

If this is a homework assignment, I take issue with some of the specifications -- or your interpretation of them.

If it's a real-life problem, I would like to take a step back and restate the problem slightly. I feel the need to do that because some of your constraints seem to be inconsistent or incomplete.

For example, on the one hand, you say that you want 10 years of withdrawals starting at age 70, and on the other hand, you say that the "last withdrawal year" is 80. And indeed, you filled in 11 years of withdrawals in column K.

I believe you want to find the amount of the initial withdrawal at age 70 such that after 10 withdrawals (at age 79, not 80), the ending balances of two sources of funds are zero.

Withdrawals increase at an annual inflation rate of 3%. You want to deplete one source of funds ("Bank A") first, then a second source of funds ("Bank B"). The initial balances are $180,000 in Bank A and $150,000 in Bank B. Bank A earns an annual yield of 3%. Bank B earns an annual yield of 2.5%. Withdrawals occur at the beginning of the year.

(For simplicity, initially, I ignore the requirement that the minimum balance of Bank A is $30,000. We can return to that requirement later, after you confirm my general understanding of the problem.)

Off-hand, I don't know if there is an algebraic solution to (my understanding of) the problem.

Obstensibly, I would use Solver.

However, I'm having trouble with coercing Solver to do the right thing. I suspect that is because of a "non-continuous" element (MAX function) of my construction of formulas. But before I work on that further (or find an algebraic solution), I want to be sure you agree with my understanding of the problem.

In the meantime, I do the work of Solver manually, using a binary search (interpolation) to find an initial withdrawal that makes the end balances (nearly) zero in the following model.

Please confirm that the following model is essentially what you want. Also, see Sheet2 in the attached file.

	A	B
1	Current age	60
2	Bank A balance	$180,000
3	Interest for Bank A	3.00%
4	Bank B balance	$150,000
5	Interest for Bank B	2.50%
6	First withdrawal age	70
7	Last withdrawal age	80
8	Annual increase in withdrawal	3.00%
9	Initial withdrawal	$42,720

	C	D	E	F	G	H	I	J	K
10	Year	Age	Beginning balance of Bank A (1) (1)+(2)+(3)	Drawdown from Bank A (2)	Interest credited to Bank A (3) ((1)+(2))*B3	Beginning balance of Bank B (4) (4)+(5)+(6)	Drawdown from Bank B (5)	Interest credited to Bank B (6) ((4)+(5))*B5	Total drawdown (7) (2)+(5)
11	1	60	$180,000	$0	$5,400	$150,000	$0	$3,750	$0
12	2	61	$185,400	$0	$5,562	$153,750	$0	$3,844	$0
13	3	62	$190,962	$0	$5,729	$157,594	$0	$3,940	$0
14	4	63	$196,691	$0	$5,901	$161,534	$0	$4,038	$0
15	5	64	$202,592	$0	$6,078	$165,572	$0	$4,139	$0
16	6	65	$208,669	$0	$6,260	$169,711	$0	$4,243	$0
17	7	66	$214,929	$0	$6,448	$173,954	$0	$4,349	$0
18	8	67	$221,377	$0	$6,641	$178,303	$0	$4,458	$0
19	9	68	$228,019	$0	$6,841	$182,760	$0	$4,569	$0
20	10	69	$234,859	$0	$7,046	$187,329	$0	$4,683	$0
21	11	70	$241,905	-$42,720	$5,976	$192,013	$0	$4,800	-$42,720
22	12	71	$205,160	-$44,002	$4,835	$196,813	$0	$4,920	-$44,002
23	13	72	$165,994	-$45,322	$3,620	$201,733	$0	$5,043	-$45,322
24	14	73	$124,292	-$46,681	$2,328	$206,777	$0	$5,169	-$46,681
25	15	74	$79,939	-$48,082	$956	$211,946	$0	$5,299	-$48,082
26	16	75	$32,813	-$32,813	$0	$217,245	-$16,711	$5,013	-$49,524
27	17	76	$0	$0	$0	$205,547	-$51,010	$3,863	-$51,010
28	18	77	$0	$0	$0	$158,401	-$52,540	$2,647	-$52,540
29	19	78	$0	$0	$0	$108,507	-$54,116	$1,360	-$54,116
30	20	79	$0	$0	$0	$55,750	-$55,740	$0	-$55,740
31	21	80	$0			$11

Please Login or Register  to view this content.

Let me know if you really want 11 withdrawals (last at age 80) instead of 10. It requires a small tweak to the formulas.

Also, let me know if there is need for, and what the reason is for, a minimum balance of $30,000 in Bank A. My guess: the yield rate changes below that amount. Although the current model satisfies that requirement, it is only by coincidence. The requirement adds some complexity to the model; but it is not prohibitive.

Methodogy: Modify B9 until H31 is as close to zero (or positive) as possible.

As a guess (yours), set B9 to $60,000; note that H31 is negative. Then set B9 to $30,000; H31 is positive. So try a value in between, namely $45,000; H31 is negative. Then try a value between $45,000 and $30,000, namely $37,500; H31 is positive. So try a value in between $45,000 and $37,500, namely $41,250; H31 is positive. Then try a value between $45,000 and $41,250, namely $43,125; H31 is negative. And so forth.

Note that with $42,720 in B9, H31 is positive, and with $42,721 in B9, H31 is negative. So $42,720 is the largest initial integer withdrawal. Obviously, I could have continued to interpolate between $42,720 and $42,721, starting with $42,720.50, until H31 is nearly zero.

-----

Aside: Practical note.... In real-life, I would deplete the source of funds with the lowest yield rate first; in this case, Bank B. See Sheet3 in the attached file. Assuming no minimum balances, the initial withdrawal is $43,230 instead of $42,720; and a total of $5847 more in withdrawals for the 10-year period.

[Johnson1992]

Edited post: Pls ignore this. Thank you.

[Rayliew]

Hi Joeu,

Thank you very much for your response, I truly appreciate that!!

Yes, this is the model i am looking for.

I have overlooked the age. The last withdrawal age should be 79 and thus number of withdrawals is 10.

Also, the minimum balance of $30,000 in Bank A is meant for illustration. Sorry for the confusion made to you.

I tried to work around the "annual increase in withdrawal" e.g 0%,1%,5% etc, and none resulted to a $0 value.

Could you please tweak the formula such that regardless of the % or a flat zero, it will result to the last withdrawal at age 79 a $0 value? Also, i am curious, is there a way for the formula to automatically calculate the draw-down value instead of manually input the value such as $42,720 in cell B9?

Thank you!!

[joeu2004]

I tried to work around the "annual increase in withdrawal" e.g 0%,1%,5% etc, and none resulted to a $0 value.

You're welcome! I don't understand why you would alter the withdrawal increase rate. You should choose a rate that meets your needs or expectations. Typically, that is the applicable inflation rate.

Could you please tweak the formula such that regardless of the % or a flat zero, it will result to the last withdrawal at age 79 a $0 value?

That is what the formulas (per se) already do. The only thing to tweak is the initial withdrawal in B9.

I already explained the algorithm for tweaking that amount: a binary search. Was my explanation unclear?

Moreover, you indicated that the input numbers (especially the initial bank balances) are examples, not the real ones. So I think there is little point in me tweaking the initial withdrawal. Instead, it is important that you learn how to tweak the amount yourself.

PS.... But I think anything more than an integer estimate of the initial withdrawal is a waste of time. IMHO, the ending balance (age-80 beginning balance) of $11 is sufficiently close to zero. In the real world, things will change over the course of 10 years that will invalidate any future forecast.

For the example, start with a value between $42,720 and $42,721. Typically, we use the midpoint (average); that would be $42,720.50.

To make this process easier, change B9 to the formula =AVERAGE(42720,42721). Also, it would be prudent to change the format of B9 and H31 so they display at least 2 decimal places.

That results in an age-80 beginning balance of $3.99. Since that is positive, we need to raise the initial withdrawal (because we know that the higher initial withdrawal of $42,721 results in a negative age-80 balance). So, replace the smaller AVERAGE parameter (on the left) with the exact average that was previously calculated (42720.50).

In order to capture the exact calculated average, not simply the displayed value, I do the following. Beware: this is error-prone.

1. Select B9.
2. Click the formula in the Formula Bar.
3. Press f9 to display and highlight the "exact" value (actually, it is rounded to 15 significant digits).
4. Press ctrl+c to copy the text
5. Press Esc to restore the AVERAGE formula. (This very important.)
6. Double-click the AVERAGE parameter that you want to replace (42720, in this case).
7. Press ctrl+v to paste the previous average.
8. Finally, press Enter to calculate a new average.

In this case, the new calculated average is $42,720.75, resulting in an age-80 balance of $0.69. Again, that is positive. So replace the smaller AVERAGE parameter (on the left) with the exact new average (42720.75) in order to raise the average.

The new age-80 balance is -$0.96, a negative value. So this time, replace the larger AVERAGE parameter (on the right) with the exact average in order to lower the average.

Repeat this procecss until the age-80 balance in H31 is as close to zero as you require.

-----

I derive an initial withdrawal of $42,720.802734375 (and a negative balance that rounds to $0.00) or $42,720.8022460937 (and a positive balance that rounds to $0.00). Both are $42,720.80 in the real world, which results in a real-world age-80 balance of $0.03.

Because of real-world constraints, it is not realistic to expect an age-80 balance of exactly zero. And speaking of real-world constraints, arguably there should be some explicit rounding to 2 decimal places in the model that I presented.

is there a way for the formula to automatically calculate the draw-down value instead of manually input the value such as $42,720 in cell B9?

That is the "algebraic solution" that I alluded to previously.

I don't expect that there is such a formula. (At least, a formula that does not depend on circular references and iterative evaluation, which I do not recommend.) In any case, I do not have the time now to explore the possibility.

However, I believe we could create a user-defined VBA function. I might find the time to do that today. If not me, perhaps someone else will provide it.

PS.... On second thought, before I do anything more, I would appreciate it if you would explain the context. Is this a homework assignment?

[Rayliew]

Hi Joeu,

I am a financial planner, so this is not a homework assignment. Often, i need to present spreadsheet like this to my clients for illustration. As for the withdrawal increase rate, this is for 1) flexibility and 2) keeping up with the inflation rate (as you have already mentioned) 3) certain clients has desire to increase their withdrawal amount annually to keep up with their retirement needs.

As for why I needed cell B9 to calculate the draw-down value automatically is because I needed to show my clients the value instantly, rather than working around with the figures, which may take some time.

[JeteMc]

Perhaps this will help based on the file that Joeu2004 attached to post #4.
1. In cell C9 estimate the initial withdrawal using: =ROUND(SUM(B2*(1+B3)^(B6-D11),B4*(1+B5)^(B6-D11))/(B7-B6)^(1+B8),0)
2. Type the value displayed in cell C9 into cell B9
3. Select the Goal Seek from the Data tab, Forecast pane
4. Type H31 into the Set Cell window
5. Type 0 (zero) into the To Value window
6. Type B9 into the By Changing Cell window
7. Select OK and OK
Let us know if you have any questions.

[Rayliew]

Hi JeteMc,

Thank you! They worked perfectly!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.