Question

1. (Calculate with Excel only) Consider Bob, who just turned 18 years old and is about to embark on a four-year degree program. The tuition for the course is $20,000 per year. Bob currently works at Vons making $18,000 per year and will have to surrender his job to pursue the degree. Based on the BLS Occupational Handbook Bob anticipates his earnings will be $25,000 more each year following graduation (starting when he turns 22) until he retires on his 60^th birthday. He anticipates his incremental earnings will increase by 4% each year of his career. For your calculations, you can assume all payments and income are incurred-received at the end of each year and that he can borrow/save at an interest rate of 6%.

1. Calculate the net present value of this investment.

2. Calculate the internal rate of return from the investment in education                  (round answer to a whole percent).

3. Based on the NPV and IRR, should he undertake the program? Why?

Answer 1

a]

the cash flows of the investment are as follows :

Year	Cash flow
1	($38,000)
2	($38,000)
3	($38,000)
4	($38,000)
5	$25,000
6	$26,000
7	$27,040
8	$28,122
9	$29,246
10	$30,416
11	$31,633
12	$32,898
13	$34,214
14	$35,583
15	$37,006
16	$38,486
17	$40,026
18	$41,627
19	$43,292
20	$45,024
21	$46,825
22	$48,698
23	$50,645
24	$52,671
25	$54,778
26	$56,969
27	$59,248
28	$61,618
29	$64,083
30	$66,646
31	$69,312
32	$72,084
33	$74,968
34	$77,966
35	$81,085
36	$84,328
37	$87,701
38	$91,210
39	$94,858
40	$98,652
41	$102,598
42	$106,702

The cash flows in first four years are outflows : loss of current earnings $18,000 + tuition fee $20,000 = $38,000

Incremental earnings in year 5 is $25,000, which increases by 4% each year for 42 years (until retirement)

NPV is calculated using the NPV function in Excel, with inputs being rate = 6%, and values being the array of cells containing the cash flows from year 1 to year 42

NPV = $378,344

b]

IRR is calculated using the IRR function in Excel, with input being the array of cells containing the cash flows from year 1 to year 42

IRR = 17%

c]

based on NPV and IRR, he should undertake the program as NPV is positive, and IRR is higher than the borrowing/saving cost of 6%