Your 75-year-old grandmother expects to live for another 15 years.She currently has $1,000,000 of savings,which is invested to earn a guaranteed 5% rate of return.If inflation averages 2% per year,how much can she withdraw (to the nearest dollar)at the beginning of each year and keep the withdrawals constant in real terms,i.e.,growing at the same rate as inflation and thus enabling her to maintain a constant standard of living?
A) $65,632
B) $72,925
C) $81,027
D) $89,130
E) $98,043

Question

Quizplus · Accepted Answer

Inputs:
Number af years $=15$
Naminal interest rate, INaM $=5\%$
Available to invest = portfolio $=\$1,000,000$
Infletion rate =2% 
Step 1.Set up an "Amortization Table" to show exactly what's happening.We begin with $1 million.But we immediately make the first withdrawal,hence have less than $1 million to invest.We don't know how much we can withdraw initially,so we make a "guess" of $50,000.We subtract the $50,000 from the initial portfolio and get $950,000,which is invested at 5% and thus earns $47,500.The earnings are added to the beginning balance,less the withdrawal,to produce the ending balance,which is carried forward to create the next beginning balance.This process is continued for 15 years.Step 2.We want to end up with a $0.00 ending balance.With the $50,000 initial withdrawal,we see that we end with more than zero.Therefore,we should make a larger initial withdrawal.We could just go through a series of trials and errors until we found an initial withdrawal that produced the zero ending balance.The amount that does the trick is $81,027.42.Replace the $50,000 with 81027.42 to prove that this value "works" to within one penny. 11ea42d0_c070_f385_9abf_19cf07b538d7_TB2090_00 
Using Goal Seek:
1.Put the pointer on the cell for the Ending Balance after the 15th withdrawal.2.Click Tools>Goal Seek to get a dialog box,which you then fill out as shown to the right.3.You will be at the "Set cell" because you put the pointer there initially.4.Go down to the "To value to" cell.You want to get 0 as the ending balance,so enter 0 here.5.Now move down to the "By changing cell" box,then click on the cell with the Year 1 withdrawal and select it.6.Now click OK,and the initial withdrawal will change to $81,027,and the final balance will go to $0.00.You could increase the decimals shown to see the extra digits Excel calculated.Calculator solution:
 Step 1: Find the real rate of return, $r _ { \mathrm { r } }$.
$r _ { \mathrm { r} } = \left( 1 + \mathrm { r } _ { \mathrm { Nand } } \right) ( 1 +$ inflation $) - 1$
$\left. r _ { \mathrm { r} } = ( 1.05 ) / 1.02 \right) - 1$
$r _ { \mathrm { r } } = 2.9412 \%$
Step 2: Use the pMT function in Excel ar a calculator to Eind the initial anount to be writhdram. Be sure to set the calculatar to BEGIN mode, and malce a similar arjustruent to the Earcel function.
BEGIN
$\mathbf { N } =15$
$I =r_ {r} =$$2.9411765 \%$
$P \mathbf {V } =$$- 1,000,00$
PMT =$\$81,027.42$
This is consistent with the value found using Goal Seek.

Your 75-Year-Old Grandmother Expects to Live for Another 15 Years