Question

ohn and Daphne are saving for their daughter Ellen's college education. Ellen just turned 10 (at t = 0), and she will be entering college 8 years from now (at t = 8). College tuition and expenses at State U. are currently $14,500 a year, but they are expected to increase at a rate of 3.5% a year. Ellen should graduate in 4 years--if she takes longer or wants to go to graduate school, she will be on her own. Tuition and other costs will be due at the beginning of each school year (at t = 8, 9, 10, and 11).

So far, John and Daphne have accumulated $12,000 in their college savings account (at t = 0). Their long-run financial plan is to add an additional $5,000 in each of the next 4 years (at t = 1, 2, 3, and 4). Then they plan to make 3 equal annual contributions in each of the following years, t = 5, 6, and 7. They expect their investment account to earn 9%. How large must the annual payments at t = 5, 6, and 7 be to cover Ellen's anticipated college costs?

Question 10 options:

	$3,758.85
	$3,595.43
	$4,085.71
	$4,004.00
	$4,698.57

Answer 1

Formula sheet

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

2

3

Cost of college eduction in todays dollar

14500

4

Education cost inflation

0.035

5

Rate of return

0.09

6

Duration of College

4

Years

7

Assuming A is the savings during years 5, 6 and 7.

8

To meet the college cost, the NPV of the above Cash flows should be zero.

9

NPV of the Cash Flows can be calculated as follows:

10

Year (n)

0

1

2

3

4

5

6

7

8

9

10

11

11

Cash Flow

12000

5000

5000

5000

5000

A

A

A

=-$D$3*(1+$D$4)^L10

=-$D$3*(1+$D$4)^M10

=-$D$3*(1+$D$4)^N10

=-$D$3*(1+$D$4)^O10

12

Required rate of return (i)

=D5

13

Present Value Factor (P/F,i,n)

=1/((1+$D$12)^D10)

=1/((1+$D$12)^E10)

=1/((1+$D$12)^F10)

=1/((1+$D$12)^G10)

=1/((1+$D$12)^H10)

=1/((1+$D$12)^I10)

=1/((1+$D$12)^J10)

=1/((1+$D$12)^K10)

=1/((1+$D$12)^L10)

=1/((1+$D$12)^M10)

=1/((1+$D$12)^N10)

=1/((1+$D$12)^O10)

14

Present Values of Cash Flows

=D11*D13

=E11*E13

=F11*F13

=G11*G13

=H11*H13

0.65A

0.60A

0.55A

=L11*L13

=M11*M13

=N11*N13

=O11*O13

15

NPV

=$12000+$4587+$4208+$3861+$3542 ++(0.65+0.60+0.55)A - $9582-$9099-$8640-$8204

16

=$28,199+(0.65+0.60+0.55)A-35525

17

=1.80A-$7327

18

19

Since NPV should be zero, therefore

20

0=1.80A-$7327

21

22

Solving the above equation,

23

A=

=-(SUM(D14:H14)+SUM(L14:O14))/SUM(I13:K13)

=-(SUM(D14:H14)+SUM(L14:O14))/SUM(I13:K13)

24

25

Hence amount to be saved during year 5, 6, and 7 is

=D23

26

Thus the third option is correct.

27