Question

Overview

Mary has been working for a university for almost 25 years and is now approaching retirement. She wants to address several financial issues before her retirement and has asked you to help her resolve the situations below.

Issue D:

Mary wants to help pay for her granddaughter Beth’s education. She has decided to pay for half of the tuition costs at State University, which are now $11,000 per year. Tuition is expected to increase at a rate of 7% per year into the foreseeable future. Beth just had her 12th birthday. Beth plans to start college on her 18th birthday and finish in four years. Mary will make a deposit today and continue making deposits each year until Beth starts college. The account will earn 4% interest, compounded annually. How much must Mary’s deposits be each year in order to pay half of Beth’s tuition at the beginning of each school each year?

Create a spreadsheet or other document, calculating part D of the assignment, and provide a brief explanation for each component including its significance

Answer 1

Issue D
Age 12 – tuition cost = 11,000 % annual growth in tuition cost = 7%
Age 13 – tuition cost = 11,000*(1+7%)^1 = $11,770.00
Age 14 – tuition cost = 11,000*(1+7%)^2 = $12,593.90
Age 15 – tuition cost = 11,000*(1+7%)^3 = $13,475.47
Age 16 – tuition cost = 11,000*(1+7%)^4 = $14,418.76
Age 17 – tuition cost = 11,000*(1+7%)^5 = $15,428.07
Year 1 college - Age 18 – tuition cost = 11,000*(1+7%)^6 = $16,508.03			Half of tuition fees is $8254.02
Year 2 college - Age 19 – tuition cost = 11,000*(1+7%)^7 = $17,663.60			Half of tuition fees is $8831.80
Year 3 college - Age 20 – tuition cost = 11,000*(1+7%)^8 = $18,900.05			Half of tuition fees is $9450.03
Year 4 college - Age 21 – tuition cost = 11,000*(1+7%)^9 = $20,223.05			Half of tuition fees is $10111.53
Interest rate on investment, r = 4% Mary will make 50% contribution to the tuition fees.
Total Tuition Fees =	$36,647.38
Formula:	f = d[(1 + i)^n - 1] / i
	f = Future Value = 36647.38
	d = Principal amount of deposit
	n = time period = 6
	I = interest rate = 4%
36647.38 =	d[(1.04)^6-1]/.04
d =	36647.38/6.633
=	$5,525
She has to deposit $5525 each year for 6 years.