Question

Suppose you have three goals in your financial planning for saving money. First you would like to be able to retire in 25 years from now with a retirement income of $10,000 (today’s dollars) per month for 20 years. Second, you would like to purchase a vacation home in Sedona in 10 years at an estimated cost of $500,000 (today’s dollars). Third, assuming you will live until your life expectancy, say 20 years after your retirement, you would like to leave a cash contribution to your college in the amount of $1,000,000 (actual dollars) so that they dedicate a building in your name. You can afford to save $2,000 (actual dollars) per month for the next 10 years because you got a great job at an engineering firm. Assume that the general inflation rate is 4% compounded monthly and the property value in Sedona increases at an annual rate of 5% compounded monthly. Your first retirement withdrawal will be made 25 yearsand 1 month from now. Before retirement, you would be able to invest your money at an annual market interest rate of 10% compounded monthly. But after retirement, you will invest your assets in more conservative financial assets at an annual rate of 6% compounded monthly.

a) Calculate total amount you will need achieve your first goal (i.e. $10,000 (today’s dollars) per month is how much in total at the end of 20 years, hint: this will be impacted by inflation and the market interest rate)?

b) What is the required savings in each month in years 11 through 25? Show your results with a sample calculation sheet and an MS Excel sheet.

Answer 1

(a) Total amount needed at the end of 25 years for the first goal =

USD 56,44,534.70

Present Value of this amount at 10% compounded monthly =

USD 4,68,156.2943

(b) Results are shown in the excel attached and highlighted

4%
6%
	Principal	Interest
0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45
2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036	2037	2038	2039	2040	2041	2042	2043	2044	2045	2046	2047	2048	2049	2050	2051	2052	2053	2054	2055	2056	2057	2058	2059	2060	2061	2062	2063
10000	10407.42	10831.43	11272.72	11731.9867	12209.97	12707.42	13225.14	13763.95	14324.72	14908.33	15515.72	16147.85	16805.74	17490.43	18203.02	18944.64	19716.47	20519.75	21355.75	22225.82	23131.34	24073.74	25054.54	26075.3	27137.65158
																									325651.819	338919.4	352727.5	367098.1	382054.3	397619.8	413819.4	430679	448225.6	466487	485492.4	505272.1	525857.6	547281.9	569579	592784.5	616935.5	642070.4	668229.3	695454	723787.9
																									325651.819	319230	312934.8	306763.7	300714.3	294784.2	288971.1	283272.6	277686.5	272210.5	266842.5	261580.4	256422	251365.4	246408.5	241549.3	236785.9	232116.5	227539.2	223052.1	218653.5
																									USD 56,44,534.70
																									468156.2943