Question

G H PROBLEM Suppose your uncle, who is 65 years old, is contemplating retirement. He expects to live for another 20 years, has a $1 million nest egg, expects to earn 8% on his investments, expects inflation to average 3% per year, and wants to withdraw a constant real amount annually over the next remaining 20 years. If the first withdrawal is to be made today, what is the amount of the initial withdrawal? 14 INOM Use Financial Calculator 1 Step 1: Before we can use a financial calculator, we must first find the expected real, or inflation-adjusted, rate of return. The real rate is calculated as follows: Real rate= (1 + NOMY(1 + Inflation) - 1.0 8.00% 15 Inflation 3.00% 16 17 Real rate (1 + INOM) 1 (1 + Inflation) - 18 Real rate = 1.08 19 Real rate = ? 20 21 Step 2: 22 Now we have all the inputs needed to solve this problem with the financial calculator. 23 BEGIN 24 N 25 1 26 PV $1,000,000 27 FV 28 PMT ? 29 30 Your uncle's initial withdrawal today would be $75,585.53 20 Goal Seek 32 Use Amortization Schedule and Excel's Goal Seek Function Withdrawal Interest Age at Beginning Made at Beg. Of Earned During Year Beg. of Year Balance Year Year $1,000,000.00 $75,585.53 $73,953.16 998.367.62 77,853.10 73,641.16 67 994.155.68 8 0.188.69 73.117.36 987.084.35 82,594.35 72,359.20 69 976,849.20 85,072.18 71,342.16 5-14 IRR 5-15 Annual Monthly Compound Age at End of Year 66 67 58 To value: By changing cell 66 09:54 - Ending Balance $998,367.62 994,155.68 987.084.35 976,849.20 963,119.17 5-16 EFF Cancel 68 69 38 70 5-17 Days in a Year 5-18 Amortization V

Answer 1

Real rate	1.08	/	1.03	-1
Real rate	4.85%
Begin
N	20
I	4.85%
PV	1000000
FV	0
PMT	$75,585.53

Workings

AutoSave off 2 Book1 - Excel Sign in - E Share 0 x Comments File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do - 11 = = = > ele Calibri BIU - AA A Insert - 9X Delete - Format Paste General - $ -% Number - 88 Conditional Format as Formatting Table Styles Cell Styles . Sort & Find & Filter Select Editing Clipboard Font Alignment Cells B18 - X Fx 1.08 A 1.03 18 Real rate 19 Real rate 1.08 =B18/D18-1 20 23 Begin 24 N 25 I 26 PV 27 FV 28 PMT =B19 1000000 =PMT(B25,B24,-B26,827,1)