Question

Diane has just turned 19 and also completed high school. She is wondering about the value of a college education. She is pretty good with numbers, and driven by financial considerations only, so she sits down to calculate whether it is worth the large sum of money involved. She knows that her first year tuition will be $14,000, due at the beginning of the year (that is, right away). Based on historical trends she estimates that tuition will rise at 7% per year for the 4 years she is in school. She also estimates that her living expense above and beyond tuition will be $8,500 per year (assume this extra expense occurs at the end of each year only when she is in college) for the first year and will increase $500 each year thereafter to keep up with inflation. She does not plan to work at all while attending school. Were she to forgo college she would be able to make $22,000 per year out of high school and expects that to grow 3% annually. With the college degree, she estimates that she will earn $40,000 per year out of college, again with annual 3% increases in salary. Either way, she plans to work until 64 (she begins college right away). The interest/discount rate is 5%. What is the NPV of her college education? (Note: All cash flows except tuition payments occur at the end of the year.)

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J	K	L	M	N
2
3
4		Inflation in college fee	0.07
5		Increase in salary	0.03
6
7		Cash flow from college education will be as follows:
8		Age	19	=D8+1	=E8+1	=F8+1	=G8+1	=H8+1	=I8+1	=J8+1	…	64
9		Year	0	1	2	3	4	5	6	7		=M8-D8
10		College Fee	-14000	=D10*(1+$D$4)	=E10*(1+$D$4)	=F10*(1+$D$4)
11		Living Expense		-8500	=E11-500	=F11-500	=G11-500
12		Opportunity Cost of not having job during college		=-22000	=E12*(1+$D$5)	=F12*(1+$D$5)	=G12*(1+$D$5)
13		Salary Earned						40000	=I13*(1+$D$5)	=J13*(1+$D$5)	..	=I13*(1+$D$5)^(M9-I9)
14		Total Cash Flow	=SUM(D10:D13)	=SUM(E10:E13)	=SUM(F10:F13)	=SUM(G10:G13)	=SUM(H10:H13)	=SUM(I10:I13)	=SUM(J10:J13)	=SUM(K10:K13)	=SUM(L10:L13)	=SUM(M10:M13)
15
16		The present value of salary earned can be calculated by finding the present value of growing annuity year 5 onward.
17
18		Salary Earned at year 5	=I14
19		Growth rate	=D5
20		Period	=(M9-I9)+1	years
21		Interest rate	0.05
22
23		The present value can be found using the formula of growing annuity.
24
25		Present value of growing annuity is given as follows:
26
27
28
29
30		Where P is first payment, i rate for the period, g is growth rate and n is the period.
31
32		Thus present value of growing annuity at year 4	=(D18/(D21-D19))*(1-(((1+D19)/(1+D21))^D20))	=(D18/(D21-D19))*(1-(((1+D19)/(1+D21))^D20))
33
34		Cash Flow can be represented as folllows
35		Year	0	1	2	3	4
36		Cash Flow	=D14	=E14	=F14	=G14	=H14
37		Present Value of Salary Earned at year 4					=D32
38		Total Cash Flow	=D36	=E36	=F36	=G36	=H36+H37
39
40		NPV of the college education can be calculated as follows:
41
42		Year	0	1	2	3	4
43		Free Cash Flow (FCF)	=D38	=E38	=F38	=G38	=H38
44		Required rate of return (i)	=D21
45		(P/F,i,n) for each year	=1/((1+$D44)^D42)	=1/((1+$D44)^E42)	=1/((1+$D44)^F42)	=1/((1+$D44)^G42)	=1/((1+$D44)^H42)
46		Present Value of cash flows = FCF*(P/F,i,n)	=D43*D45	=E43*E45	=F43*F45	=G43*G45	=H43*H45
47		NPV of cash flows	=SUM(D46:H46)	=SUM(D46:H46)
48
49		Hence NPV of college education is	=D47
50