Problem about Financial Planning

[issacnewton]

Homework Statement

Following is the statement of the problem.
Dear Financial Adviser,
My spouse and I are each 62 and hope to retire in three years. After retirement we will
receive $7,500 per month after taxes from our employers’ pension plans and $1,500 per
month after taxes from Social Security. Unfortunately our monthly living expenses are
$15,000. Our social obligations preclude further economies.
We have $1,000,000 invested in a high-grade, tax-free municipal-bond mutual fund.
The return on the fund is 3.5% per year. We plan to make annual withdrawals from the
mutual fund to cover the difference between our pension and Social Security income and
our living expenses. How many years before we run out of money?

Luxury Challenged
Marblehead, MA
You can assume that the withdrawals (one per year) will sit in a checking account (no
interest). The couple will use the account to cover the monthly shortfalls.

Homework Equations

Compound interest formula, Annuity

The Attempt at a Solution

Since the couple has $7500 from the pension plans and $1500 from social security, the monthly shortfall is
$15000 - (7500+1500) = $6000. So the total shortfall for the entire year is $12 \times 6000 = 72000$. So this amount will be withdrawn each year from the invested fund. Now let $r = 0.035$ be the interest earned on the amount invested in the mutual fund. Let $P = 1,000,000$ be the amount invested. At the end of the first year, this will grow to $P(1+r)$. They will withdraw $72000. Remaining amount, $P(1+r) - 72000$, will grow to $(P(1+r) - 72000)(1+r)$ at the end of the second year. Again $72000 will be taken out. And this will go on till they run out of the money. So I am thinking of using a computer program to see how long does it take. Here is my little program in python 3

rate = 0.035
withdraw = 72000
amount = 1000000

counter = 1

while counter <= 20:
    amount = (1+ rate) * amount - withdraw
    print("The amount at the end of year %d is  $%.2f " % (counter, amount) )
    counter +=  1

And the output is

The amount at the end of year 1 is $963000.00
The amount at the end of year 2 is $924705.00
The amount at the end of year 3 is $885069.67
The amount at the end of year 4 is $844047.11
The amount at the end of year 5 is $801588.76
The amount at the end of year 6 is $757644.37
The amount at the end of year 7 is $712161.92
The amount at the end of year 8 is $665087.59
The amount at the end of year 9 is $616365.66
The amount at the end of year 10 is $565938.45
The amount at the end of year 11 is $513746.30
The amount at the end of year 12 is $459727.42
The amount at the end of year 13 is $403817.88
The amount at the end of year 14 is $345951.50
The amount at the end of year 15 is $286059.81
The amount at the end of year 16 is $224071.90
The amount at the end of year 17 is $159914.42
The amount at the end of year 18 is $93511.42
The amount at the end of year 19 is $24784.32
The amount at the end of year 20 is $-46348.23

Which tells us that in 20 years, the couple will run out of the money. Is this correct ? Now this problem is from
the Principles of Corporate Finance book and they don't mention anything about computer program to solve this problem. So is there another approach to solve this ?

Thanks

 

[scottdave]

There are formulas. You could search for amortization or sinking fund. There are functions in Excel which will do this also.
Update:
I re-read their letter. Note that they are retiring in 3 years, so for those years, no withdrawals. I think they would withdraw at the beginning of the first retirement year, to cover that year's shortfalls.

The following talks about the different formulas used for different situations with periodic payments. http://www.math.utep.edu/Faculty/cmmundy/Math 1320/Worksheets/Sinking Funds & Annuities.pdf
The sinking fund, which I mentioned, is actually for putting aside some every month (or year) to save up for a future value.

 

[scottdave]

I personally don't see anything wrong with using a spreadsheet or writing a program to come up with the solution, as long as you can show how you did it.

 

[Chestermiller]

It looks like there are a couple of typos in the original post.

You are going to have the sum of a geometric progression involving the 7200, with a ratio of (1+r). Do you know how to find the sum of a geometric progression? You don't need loan amortization tables to solve this.

 

[issacnewton]

scott, I will go through that pdf you attached. Thanks. Chester, yes I can sum the geometric progressions. The problem here is to sum them till the amount becomes negative. So I don't know how to do it analytically. So I used computer program. And which typos you mention ? Actually 12 * 6000 = 72000. You are writing 7200, where does it come from ? Thanks

 

[Chestermiller]

scott, I will go through that pdf you attached. Thanks. Chester, yes I can sum the geometric progressions. The problem here is to sum them till the amount becomes negative. So I don't know how to do it analytically. So I used computer program. And which typos you mention ? Actually 12 * 6000 = 72000. You are writing 7200, where does it come from ? Thanks

Typo. Have you examined the equation you get by summing the series?

 

[scottdave]

scott, I will go through that pdf you attached. Thanks. Chester, yes I can sum the geometric progressions. The problem here is to sum them till the amount becomes negative. So I don't know how to do it analytically. So I used computer program. And which typos you mention ? Actually 12 * 6000 = 72000. You are writing 7200, where does it come from ? Thanks

You can derive those formulas by summing up the future values. When I took that class, the prof. walked through, starting out with (Future Value) = (Present Value)*(1 + r)ⁿ. Then go from there. It was pretty cool.

 

[Chestermiller]

You can derive those formulas by summing up the future values. When I took that class, the prof. walked through, starting out with (Future Value) = (Present Value)*(1 + r)ⁿ. Then go from there. It was pretty cool.

It can also be done using "discounted cash flow" by setting the net present value (NPV) equal to zero.

 

[issacnewton]

Ok. Let $C=72000$. So amount left at the end of the first year is $P(1+r) - C$, the amount left at the end of the second year will be $P(1+r)^2 - C(1+r)-C$. So the amount left at the end of $t$ years will be $$ x = \left[P(1+r)^t - C(1+r)^{t-1}-\cdots -C(1+r)-C\right]$$ Now I will discount this $x$ to calculate its present value. Since net amount is decreasing, I will equate that present value to zero and solve for $t$. So present value of $x$ is $$x_p = \left[ P -\frac{C}{(1+r)}-\frac{C}{(1+r)^2}-\cdots - \frac{C}{(1+r)^t}\right]$$ Now summing the geometric series, we have $$x_p = P -\left(\frac{C}{r}\right)\left[ 1 - \frac{1}{(1+r)^t} \right]$$ But now since $x_p$ has become zero due to decreasing value, so we now will solve $x_p = 0$ for $t$. We get $$t = \frac{\log\left(\frac{C}{C-Pr}\right)}{\log (1+r) }$$ Plugging the values, we get $t = 19.35$ years. So Chester, is this the method of "discounted cash flow" that you are talking about ? Here I equated the NPV to zero.

 

[Chestermiller]

Ok. Let $C=72000$. So amount left at the end of the first year is $P(1+r) - C$, the amount left at the end of the second year will be $P(1+r)^2 - C(1+r)-C$. So the amount left at the end of $t$ years will be $$ x = \left[P(1+r)^t - C(1+r)^{t-1}-\cdots -C(1+r)-C\right]$$ Now I will discount this $x$ to calculate its present value. Since net amount is decreasing, I will equate that present value to zero and solve for $t$. So present value of $x$ is $$x_p = \left[ P -\frac{C}{(1+r)}-\frac{C}{(1+r)^2}-\cdots - \frac{C}{(1+r)^t}\right]$$ Now summing the geometric series, we have $$x_p = P -\left(\frac{C}{r}\right)\left[ 1 - \frac{1}{(1+r)^t} \right]$$ But now since $x_p$ has become zero due to decreasing value, so we now will solve $x_p = 0$ for $t$. We get $$t = \frac{\log\left(\frac{C}{C-Pr}\right)}{\log (1+r) }$$ Plugging the values, we get $t = 19.35$ years. So Chester, is this the method of "discounted cash flow" that you are talking about ? Here I equated the NPV to zero.

I haven't checked your arithmetic, but it looks pretty OK. The only thing I would also consider is the "three year thing."

 

[scottdave]

I haven't checked your arithmetic, but it looks pretty OK. The only thing I would also consider is the "three year thing."

Yes, and I would set it to grow for 3 years without withdrawals, then pull out the 72000 at the end of the 3rd year. They will need to have that money available at the beginning of the year, to get through the first year of retirement.

 

[issacnewton]

I see your concerns. So let $72000$ be withdrawn at the end of the 3 year. $1M$ grows till then. So amount after withdrawing, at the end of the 3 years is $P(1+r)^3 -C$. And amount after withdrawing, at the end of the 4 years is $P(1+r)^4 -C(1+r)-C$. Hence amount after withdrawing, at the end of the t years is
$$P(1+r)^{t+1}-C(1+r)^{t-2}-C(1+r)^{t-3}\cdots - C(1+r)$$ So the present value of this amount at the end of t years will be(after discounting) $$x = P(1+r)-C\frac{(1+r)}{(1+r)^t}- C\frac{(1+r)^2}{(1+r)^t}\cdots - C\frac{(1+r)^{t-2}}{(1+r)^t}$$ After summing up and simplifying, we get $$x = P(1+r)- \frac{C}{r(1+r)}\Bigg[ 1 - \frac{1}{(1+r)^{t-2}} \Bigg] $$ Now this is the present value and this will become zero at some point if they continue withdrawing like this, so equating $x=0$ and solving for $t$, we will get the answer. So $$t = 2 + \frac{\log\bigg[ \frac{C}{C -Pr(1+r)^2} \bigg]}{\log(1+r)}$$ Solving for $t$, we get $t = 23.38$ years. Is this ok ?
Thanks

 

[scottdave]

I see your concerns. So let $72000$ be withdrawn at the end of the 3 year. $1M$ grows till then. So amount after withdrawing, at the end of the 3 years is $P(1+r)^3 -C$. And amount after withdrawing, at the end of the 4 years is $P(1+r)^4 -C(1+r)-C$.

That part looks good. But then when you generalize it to $t$ years, you get this:

Hence amount after withdrawing, at the end of the t years is
$$P(1+r)^{t+1}-C(1+r)^{t-2}-C(1+r)^{t-3}\cdots - C(1+r)$$

This doesn't match what you had at 3 and 4 years, when the exponent for P(1+r)ⁿ was equal to the number of years. But now it looks like you have an extra year $P(1+r)^{t+1}$. Then the last term, for 3 and 4 years is just C, but in the general formula, you have C(1+r).
Then checking your present value, I set C=0, we should get x=P for the present value after discounting.

So the present value of this amount at the end of t years will be(after discounting) $$x = P(1+r)-C\frac{(1+r)}{(1+r)^t}- C\frac{(1+r)^2}{(1+r)^t}\cdots - C\frac{(1+r)^{t-2}}{(1+r)^t}$$

But your formula generates x=P(1+r), when C is set to zero.
So this tells me there is a mistake in there, as well. The rest going forward based on this formula, looks like you did those correct. If you fix the present value formula, then you should be able to solve for t.
Since you know how to program, it would be good to run a simulation, similar to what you had the first time, but letting it grow for 3 years, before withdrawing, to see if that way matches the formula. They will match, if you didn't make any mistakes.

 

[issacnewton]

Ok Scott, I think I made calculations in a hurry. Here is the correction. The amount in the couple's mutual fund at the end of 3 years, after withdrawing $72000$ is $P(1+r)^3 - C$. And amount after withdrawing, at the end of the 4 years is $P(1+r)^4-C(1+r)-C$. So by inspection, the amount after withdrawing, at the end of the t years is $$P(1+r)^t - C(1+r)^{t-3}- C(1+r)^{t-4}\cdots - C$$ If we want to calculate the present value of this, it would be $$x = P - \frac{C(1+r)^{t-3}}{(1+r)^t} - \frac{C(1+r)^{t-4}}{(1+r)^t} \cdots -\frac{C}{(1+r)^t}$$ After summing up the geometric series and simplifyng, we get $$x = P -\frac{C}{r}\bigg[ (1+r)^{-2} - (1+r)^t\bigg] $$
Now since the value is going down, we can equate the NPV=0 and then solve for $t$. So letting $x=0$ and solving for $t$, we have $$t = - \frac{\log\left[ (1+r)^{-2} - \frac{Pr}{C} \right] }{\log(1+r)}$$ Plugging the values and solving for $t$, we get $t = 23.38$ years. Now I have a doubt here. Why do we need to equate the present value equal to zero ? Since the amount is decreasing, even the future value is also decreasing. In fact, in my python code, I have taken the future value into consideration. And as we can see from the output, its decreasing and eventually turns negative.

 

[Ray Vickson]

$$\vdots$$
Now I have a doubt here. Why do we need to equate the present value equal to zero ? Since the amount is decreasing, even the future value is also decreasing. In fact, in my python code, I have taken the future value into consideration. And as we can see from the output, its decreasing and eventually turns negative.

Starting with bank balance $B_0$ ($) at $t = 0$ and withdrawing $W$ ($) at the start of each year for $N$ years, we have:
$$\begin{array}{rcl}\text{Starting balance at} & t=0 & \text{is} \; B(0) = B_0-W\\
\text{Starting balance at} &t=1 & \text{ is} \; B(1) = (1+r) B(0) - W = (1+r)B_0 - W[1 + (1+r)] \\
\text{Starting balance at} & t=2 & \text{is} \; B(2) = (1+r) B(1) - W \\
& &= (1+r)^2 B_0 - W[1 + (1+r) + (1+r)^2]\\
& & \vdots
\end{array}
$$
Thus, the balance at $t = N$ (the start of year $N$, after withdrawing that year's living expenses) is
$$ B(N) = (1+r)^N B_0 - W \sum_{n=0}^N (1+r)^n. $$
You want to know when $B(N)$ reaches $0$, and that is the same as asking when $B(N)/(1+r)^N$ reaches $0$. Of course, the quantity $B(N)/(1+r)^N$ is just the NPV:
$$ B(N)/(1+r)^N = \text{NPV} = B_0 - W \sum_{n=0}^N \frac{1}{(1+r)^n} ,$$
so equating the "present value" (NPV) to zero solves the problem, as does equating the "future value" ($B(N)$) to zero.

Finally: you can simplify formulas a lot, just by re-defining what you mean by $t$. In this problem, life is simpler if you put $t=0$ at the end of year 3, $t=1$ at the end of year 4, etc. Then the formulas I wrote above apply exactly, but with $B_0 = 1.036^3 \times 1000 = 1108.71785$ and $W=72$ (all in units of $1000s);

 

[issacnewton]

Thanks Ray. So it does not matter whether we equate the present value to zero or the future value to zero. Ok. That makes sense. I hope my answer is correct.

 

[Ray Vickson]

Thanks Ray. So it does not matter whether we equate the present value to zero or the future value to zero. Ok. That makes sense. I hope my answer is correct.

No, of course it does not matter, because they differ by a positive factor:
$$ \text{future_value}(N) = C(N) \times \text{present_value}(N) . $$
Here, $C(N)$ is a positive constant (that depend on $N$, though).
So
$$\begin{array}{l} \text{Present value} > 0 \; \Rightarrow \text{Future value} > 0\\
\text{Present value} < 0 \; \Rightarrow \text{Future value} < 0\\
\text{Present value} = 0 \; \Rightarrow \text{Future value} = 0
\end{array}
$$