Question

Create an excel solution for this problem and show steps.

Assume that you plan to retire 40 years from now and that you expect to need $2million to support the lifestyle that you want.

a) if the interest rate is 10%, is the following statement approximetly true? "waiting 5 years to start saving doubles what you must deposit each year."

b) if the interest rate is 12%, is the required multiplier higher or lower than for the 10% rate in a)?

c) At what interest rate is the following statement true? "waiting 5 years to start saving doubles what you must deposit each year."

Answer 1

a) To save $2 million after 40 years, if one saves an amount A every year then

Future value of annuity of A at 10% for 40 years = $ 2 million

=> A*[1.1^40-1]/0.1 = 2000000 from the Annuity formula FV = A * [(1+r)^n -1)]/ r

=> A = 200000 / 44.2593

10% Amount required 40 years from now $20,00,000.00 Amount required 40 years from now $20,00,000.00 4518.828829 2607.251166 Amount needed to save each year starting today Amount needed to save each year starting today 7379.41024 4633.23866 Amount needed to save each year starting 5 years later Amount needed to save each year starting 5 years later Multiplier 1.633036019 Multiplier 1.777058812 14.78% Amount required 40 years from now $20,00,000.00 1197.100177 Amount needed to save each year starting today 2394.200554 Amount needed to save each year starting 5 years later Multiplier 2.000000167

= $ 4518.83

Similarly , if one starts 5 years later and saves only for 35 years then

A*[1.1^35-1]/0.1 = 2000000

A= 200000 / 27.1024

= $ 7379.41

So, the multiplier here is $7379.41/4518.83 = 1.633

So, the saving required is less than double. The statement is false.

b) If the interest rate is 12%, then for 40 years

A*[1.12^40-1]/0.12 = 2000000

A= $ 2607.25

and for 35years

A*[1.12^35-1]/0.12 = 2000000

A= $ 4633.24

and the multiplier is 4633.24/2607.25= 1.777

So, the multiplier is higher in case of 12% than in case of 10% i..e one has to deposit more money in case of higher interest rates if one starts after 5 years.

c) Using Solver in excel , we can see that the exact rate for which the multiplier is 2 is 14.78%

Image of Excel sheet is attached for reference