Question

Part 1: Barry and Samantha Harris – Retirement Savings (15 points) Barry and Samantha are starting to take their retirement planning seriously. They are both 46 and plan to retire in 20 years at the age of 66. They expect to live 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts they currently have $92,400 in retirement savings. They currently have a combined income of $85,000 per year and expect to be able to live comfortably in retirement with 85% of their current purchasing power. They expect inflation to be 2.0% per year for the rest of their lives. They also expect to earn 10.5% per year on their investments, both now and in retirement. Conduct an analysis of their retirement planning needs and provide them with a professionally written letter. Use the rubric provided when preparing the letter. In the letter and attached schedules provide information that answers the following questions. Please include a description of the relevant assumptions and any explanatory comments that make the results easier to understand. What amount of annual income will they need (after adjusting for inflation) in each of the twenty years of retirement to have the purchasing power of 85% of their current income? Assuming they will continue to earn 10.5% on their investments, how much money will they need to have in their retirement accounts when they retire so that it will provide the twenty years of income? Taking into account what they currently have in savings, how much will they have to save each month to meet their retirement needs? Sensitivity analysis: Redo the analysis assuming that they only earn 8% on their investments, instead of 10.5%. Determine the needed amounts so they have the money they need in retirement. Note: Assume that all payments will be made at the end of the year (ordinary annuity). Part 2: The Pearson Family – College Savings (15 points) Matt and Debra Pearson live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned automotive painting business. Debra stays home with their son, Brady, who is four. After visiting with their financial planner, the couple became concerned that they were spending too much and not putting enough funds aside for Brady’s future educational needs. Matt earns $95,000 per year, but with the rising costs of education, they are concerned. Matt is an alumni of the University of California at Los Angeles (UCLA) with tuition and book expenses of approximately $16,500 per year today. Debra graduated from Utah Valley University. The expense for tuition and books there is currently estimated at about $6,500 per year. When Brady turns 18, the couple wishes to send him to one of these two exceptional universities. They have a slight preference for Utah Valley University. The problem, however, is that with the rate at which tuition is increasing the Pearson’s are not sure they can save enough money and they have decided they do not want to borrow to pay for Brady’s education. Assume the tuition at both universities will increase at an annual rate of 5% from now until Brady finishes college. Living expenses are currently estimated at $10,000 per year at both schools. This expense is expected to grow at only 3% per year. Further assume that Pearson’s can deposit their money into a growth oriented mutual fund at the Salt Lake City based mutual fund company which has historically earned 9% per annum. The couple wishes to save by having a pre-determined amount automatically withdrawn from their bank account at the end each month. They plan to contribute from now until Brady starts college. When Brady starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account to cover all four years of college expenses when Brady starts college. Assume that the funds in the account will continue to earn a return while he is in college. They will make annual withdrawals from the account to cover both tuition and living expenses for Brady at the beginning of his freshman, sophomore, junior, and senior years. When the withdrawal for the senior year is made the account balance will be zero. Complete an analysis and write a professional letter to the Pearson’s (who don’t understand finance) explaining the analysis you performed, why you performed it, what the results are, and your recommendations. Use the provided rubric in preparing your letter. In the letter and attached schedules provide information that discusses and answers the following questions. What will be the tuition expense, living expense, and total expense for each of the four years that Brady will attend college? Provide the information for each University. What amount will be needed in the account when Brady starts his freshman year if he attends UCLA? What amount if he attends UVU? How much money will Matt and Debra have to deposit at the end of each month to allow Brady to attend UCLA? How much money will have to be deposited per month to allow Brady to attend Utah Valley University? Assume that Matt and Debra stop making deposits when Brady starts college. The Pearson’s are concerned that given the current market situation the mutual fund will only earn 7.5% per year. If the return is only 7.5% how much will be needed in the account when Brady starts college and how much will have to be deposited per month for Brady to have sufficient funds to attend each school?

Answer 1

Part 1
Harris Family
Time to retire	20 years
Life in retirement	20 years
Inflation	2%
Income growth rate	2%
Expected ROI	10.50%
Purchasing power in retirement	85%

Age	Applicable rate	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	77	78	79	80	81	82	83	84	85	86
Year		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
Income	2%	85,000	86,700	88,434	90,203	92,007	93,847	95,724	97,638	99,591	101,583	103,615	105,687	107,801	109,957	112,156	114,399	116,687	119,021	121,401	123,829	126,306
Retirement expense	2%																					=126,306*85%*(1+2%)	109,507	111,697	113,931	116,210	118,534	120,904	123,323	125,789	128,305	130,871	133,488	136,158	138,881	141,659	144,492	147,382	150,329	153,336	156,403	159,531
PV of annual retirement expense at start of retirement	10.50%																					=[126,306*85%*(1+2%)]/(1+10.5%)^1	99,101	91,478	84,441	77,946	71,950	66,415	61,307	56,591	52,237	48,219	44,510	41,086	37,926	35,008	32,315	29,830	27,535	25,417	23,462	21,657
Sum of PV at start of retirement																							1,028,432
FV of current retirement saving at start of retirement	10.50%	92,400																				=FV(10.5%,20,0,-92400,0)	680,640
Additional savings required at start of retirement																						=1,028,434-680,640	347,792
Savings required each month to meet retirement target	10.50%	$429.11	=PMT(10.5/12,20*12,0,-347792,0)

Sensitivity

ROI= 8%

Sum of PV at start of retirement		1,243,255
FV of current retirement saving at start of retirement	8.00%	430,672
Additional savings required at start of retirement 812,582
Savings required each month to meet retirement target	8.00%	$590.46

Part 2
Pearson Family
Current fees at UCLA	16,500
Current fees at UVA	6,500
Time to enter college	14 years
Inflation in college fees	5%
Living expense	10,000
Inflation in living expense	3%
Expected ROI	9%

College expenses at beginning of college UCLA	31,113	=FV(5%,13,0,-16500,0)
College expenses at beginning of college UCLA	12,257	=FV(5%,13,0,-6500,0)
Living expenses at beginning of college UCLA	14,685	=FV(3%,13,0,-10000,0)

Expenses in each college during 4 years
Year	0	1	2	3
College expenses at beginning of college UCLA	31,113	32,669	34,302	36,017
College expenses at beginning of college UCLA	12,257	12,870	13,513	14,189
Living expenses at beginning of college UCLA	14,685	15,126	15,580	16,047
Total expense
UCLA	45,799	47,795	49,882	52,064
UVA	26,942	27,995	29,093	30,236
PV of total expense at beginning of college
UCLA	45,799	43,848	41,985	40,203
UVA	26,942	25,684	24,487	23,348
Total PV at beginning of college
UCLA	171,835
UVA	100,460
Monthly deposit required for 13 years
UCLA	$583.69	=PMT(9%/12,13*12,0,-171835,0)
UVA	$341.24	=PMT(9%/12,13*12,0,-100460,0)

Sensitivity

Expenses in each college during 4 years
Year	0	1	2	3
College expenses at beginning of college UCLA	31,113	32,669	34,302	36,017
College expenses at beginning of college UCLA	12,257	12,870	13,513	14,189
Living expenses at beginning of college UCLA	14,685	15,126	15,580	16,047
Total expense
UCLA	45,799	47,795	49,882	52,064
UVA	26,942	27,995	29,093	30,236
PV of total expense at beginning of college
UCLA	45,799	44,460	43,165	41,910
UVA	26,942	26,042	25,175	24,339
Total PV at beginning of college
UCLA	175,333
UVA	102,498
Monthly deposit required for 13 years
UCLA	$666.92
UVA	$389.87