Question

1.The recorded session, provides a good overview on working this problem. Use the "A Number of Useful Financial Spreadsheets" to find a sample template you can use to work this problem. I went over this in the recorded session. The problem you will answer has the following fact pattern:

A couple has just given birth to a baby and named him Jimmy. They want to set up a college savings account for Jimmy and start saving for his college education. The following facts will help you work this problem:

Current Cost of Tuition, Room & Board                                            $25,000

Expected Average Inflation for College Costs 5%

Expected Average Percentage Return on Savings                               4%

Years until you need the first tuition payment 18

You will make 18 yearly payments with the first payment starting a year from now.

How much will the couple need at the end of 18 years to fund Jimmy's college education for 4 years? Round your answer to a whole number (Example 150450.61 rounded to 150451 with no commas or dollar signs).

2.Based on the amount you calculated above, how much will the couple need to save each year in Jimmy's account to reach their goal of having enough money to fund his college education at the end of 18 years? Round your answer to a whole number (Example 1450.61 rounded to 1451 with no commas or dollar signs)

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3		Current Cost of Tuition, Room and Board	25000
4		Expected Average inflation for college costs	0.05
5		Expected Average Percentage return on savings	0.04
6		Years until you need the first tuition payment	18
7
8		1)
9
10		Amount needed to finance the college after 18 Years	=Current cost*(1+inflation rate)^Time
11			=D3*(1+D4)^D6	=D3*(1+D4)^D6
12
13		Hence Amount needed to finance the college after 18 Years is	=D11
14
15		2)
16
17		Let annual payments be A such that the future value of annual payments equals the amount of fee required after 18 years i.e.
18		A*(F/A,4%,18)=$60,165.48
19
20		(F/A,4%,18)	=FV(D5,D6,-1,0)
21
22		Using the above future factor,
23		A*25.65=$60,165.48
24
25		Solving the above equation A can be found as below:
26		A=	=D13/D20
27
28		Hence annual savings required is	=D26
29