Question

As of the end of 2009, Amelia and Adam have a joint investment account of $400,000 in assets. They are 50 years old and plan to retire in 15 years. They expect to live for another 20 years after they retire. They have a household annual income of $200,000 of which they spend $101,000. They would like to save enough money so that they can maintain the same consumption level of 101,000 a year. The inflation rate is 4%, and the annual rate of return on investment is 10%. How much do they need to contribute to their investment account every year in order to achieve this goal. Solve the problem using the constant dollar approach (use real values and real rate of return) (1+R nom) = (1+r real) (1+inflation rate) Build a spreadsheet that solves for these two questions and shows the investment account during the two phases: the accumulation phase and the withdrawal phase. You will proceed in two steps: Phase 1: Accumulation phase 1) Calculate the real rate of return 2) Calculate the amount that Amelia and Adam need to have on their account on the day of their retirement as they aim to keep the same standard of living (same spending needs) → think will you use PV or FV of annuity? 3) Calculate the PMT that they need to make annually into their retirement fund during the accumulation phase using both the excel PMT function and the formula method. Use the real rate of return (constant dollar approach) Think about which formula to use → What is FV and what is PV that you will use? 4) The PMT for the nominal dollar approach will be equal to the PMT with real dollar but adjusted for the inflation rate. 5) Build the retirement plan that shows their contribution annually to the retirement fund Phase 2: Spending phase As they plan to live ...... this spending phase will have..... years so your plan will have ...... rows On the last day of their life they should have spent everything and nothing is left for their heirs. Add a spinner to see the impact on the retirement fund of changing real interest rates

Answer 1

1) (1 + R nom) = (1 + r real) (1 + inflation rate)

(1+.1) = (1 + r real) (1 + .04)

1.1/1.04= (1 + r real)

1.058 - 1 = r real

Real rate =5.8%

2) They will retire at 65 age and will live for 20 years more. Therefore, they need $101,000 for 20 years

Value of consumption amount at the age of 65 age = $101,000 x PVAF (5.8%,20)

                                                                                            = $1,177,508

They are required to have in $1,177,508 their account at their retirement.

3)

Amount they already have in account = $400,000

Let they invest Y amount every year

Total amount after 15 years = $400,000 x FVF(5.8%,15) + Y x FVAF(5.8%,15)

$1,177,508 = $400,000 x FVF(5.8%,15) + Y x FVAF(5.8%,15)

$1,177,508 = $931848 + Y x 22.9245

$1,177,508 - $931848 = Y x 22.9245

       $245660/22.9245   = Y

            $10,716 = Y

Annualy they are required to deposit $10,716 in their account.

5)

time	beginning amount($)	contribution to fund($)	total amount invested($)	interest on total amount (nominal)($)	total amount ($)	real amount adjusting inflation($)
1	400000	10716	410716	41072	451788	434411
2	434411	10716	445127	44513	489640	470808
3	470808	10716	481524	48152	529676	509304
4	509304	10716	520020	52002	572022	550021
5	550021	10716	560737	56074	616811	593087
6	593087	10716	603803	60380	664183	638638
7	638638	10716	649354	64935	714289	686817
8	686817	10716	697533	69753	767286	737775
9	737775	10716	748491	74849	823340	791673
10	791673	10716	802389	80239	882628	848681
11	848681	10716	859397	85940	945336	908977
12	908977	10716	919693	91969	1011663	972753
13	972753	10716	983469	98347	1081815	1040207
14	1040207	10716	1050923	105092	1156015	1111553
15	1111553	10716	1122269	112227	1234496	1187016