Question

How do i answer the following question on excel?

Consider attending one of the following two colleges as a full-time student. One is a public university with low tuition, while the other is a prestige university (they are both in the same city, so housing costs should be equal for each). Suppose you qualify for a partial scholarship at the private university. The financial information corresponding to attending each school is as follows.

Public university ​ Private University

Tuition and related expenses four years at: $3500 per year $29,000 per year

Earnings per year for first 5 years ​ $39,000 per year $56,000 per year

Earnings per year for next 10 years ​ $72,000 per year​ $89,000 per year

Earnings per year for next 17 years ​$88,000 per year​ $118,000 per year

Earnings per year for next 12 years ​$74,000 per year​ $90,000 per year​​

Assuming the decision will be made solely on net financial returns grounds, a) Calculate the present value of associated with attending each college using a three percent (3%) interest (i.e. discount) rate, and b) repeat the calculation using an 9.5% interest rate. C) explain whether the difference in interest rates did or did not change the financial decision.

Answer 1

It is actually a simple calculation. We need to sum up the present values over the 48 years. This can be done as shown below. This table is for 3% discount rate

		Public University	Private University
Index	Time	Monetary Value	Present Value	Monetary Value	Present Value
0	Year 1	-3500	-3500.00	-29000	-29000.00
1	Year 2	-3500	-3398.06	-29000	-28155.34
2	Year 3	-3500	-3299.09	-29000	-27335.28
3	Year 4	-3500	-3203.00	-29000	-26539.11
4	Year 5	39000	34650.99	56000	49755.27
5	Year 6	39000	33641.74	56000	48306.09
6	Year 7	39000	32661.89	56000	46899.12
7	Year 8	39000	31710.57	56000	45533.12
8	Year 9	39000	30786.96	56000	44206.92
9	Year 10	72000	55182.00	89000	68211.09
10	Year 11	72000	53574.76	89000	66224.36
11	Year 12	72000	52014.33	89000	64295.49
12	Year 13	72000	50499.35	89000	62422.81
13	Year 14	72000	49028.50	89000	60604.67
14	Year 15	72000	47600.48	89000	58839.48
15	Year 16	72000	46214.06	89000	57125.71
16	Year 17	72000	44868.02	89000	55461.86
17	Year 18	72000	43561.18	89000	53846.46
18	Year 19	72000	42292.41	89000	52278.12
19	Year 20	88000	50185.17	118000	67293.75
20	Year 21	88000	48723.47	118000	65333.74
21	Year 22	88000	47304.34	118000	63430.81
22	Year 23	88000	45926.54	118000	61583.32
23	Year 24	88000	44588.87	118000	59789.63
24	Year 25	88000	43290.17	118000	58048.18
25	Year 26	88000	42029.29	118000	56357.46
26	Year 27	88000	40805.14	118000	54715.98
27	Year 28	88000	39616.64	118000	53122.31
28	Year 29	88000	38462.75	118000	51575.06
29	Year 30	88000	37342.48	118000	50072.87
30	Year 31	88000	36254.83	118000	48614.44
31	Year 32	88000	35198.87	118000	47198.48
32	Year 33	88000	34173.66	118000	45823.77
33	Year 34	88000	33178.31	118000	44489.10
34	Year 35	88000	32211.95	118000	43193.30
35	Year 36	88000	31273.74	118000	41935.24
36	Year 37	74000	25532.40	90000	31052.92
37	Year 38	74000	24788.74	90000	30148.46
38	Year 39	74000	24066.74	90000	29270.35
39	Year 40	74000	23365.76	90000	28417.82
40	Year 41	74000	22685.21	90000	27590.12
41	Year 42	74000	22024.47	90000	26786.52
42	Year 43	74000	21382.98	90000	26006.33
43	Year 44	74000	20760.18	90000	25248.86
44	Year 45	74000	20155.51	90000	24513.46
45	Year 46	74000	19568.46	90000	23799.48
46	Year 47	74000	18998.50	90000	23106.29
47	Year 48	74000	18445.15	90000	22433.29
			1577227.43		1953932.18

We need just the one simple formula,

For ex, in this table the formula you need to put on excel for Present Value is Monetary Value, divided by 1.03 power the index. Below is how I did this on excel, you can see formula at top

So at 3% discount rate, we should go with private university. Let us repeat the calculation by changing 3% to 9.5%

		Public University	Private University
Index	Time	Monetary Value	Present Value	Monetary Value	Present Value
0	Year 1	-3500	-3500.00	-29000	-29000.00
1	Year 2	-3500	-3196.35	-29000	-26484.02
2	Year 3	-3500	-2919.04	-29000	-24186.32
3	Year 4	-3500	-2665.79	-29000	-22087.96
4	Year 5	39000	27127.40	56000	38952.16
5	Year 6	39000	24773.88	56000	35572.75
6	Year 7	39000	22624.55	56000	32486.53
7	Year 8	39000	20661.69	56000	29668.06
8	Year 9	39000	18869.12	56000	27094.12
9	Year 10	72000	31813.06	89000	39324.48
10	Year 11	72000	29053.02	89000	35912.76
11	Year 12	72000	26532.44	89000	32797.04
12	Year 13	72000	24230.54	89000	29951.64
13	Year 14	72000	22128.35	89000	27353.09
14	Year 15	72000	20208.53	89000	24979.99
15	Year 16	72000	18455.28	89000	22812.78
16	Year 17	72000	16854.14	89000	20833.59
17	Year 18	72000	15391.91	89000	19026.11
18	Year 19	72000	14056.54	89000	17375.44
19	Year 20	88000	15689.69	118000	21038.45
20	Year 21	88000	14328.49	118000	19213.20
21	Year 22	88000	13085.37	118000	17546.30
22	Year 23	88000	11950.11	118000	16024.02
23	Year 24	88000	10913.35	118000	14633.81
24	Year 25	88000	9966.53	118000	13364.21
25	Year 26	88000	9101.85	118000	12204.75
26	Year 27	88000	8312.19	118000	11145.89
27	Year 28	88000	7591.04	118000	10178.90
28	Year 29	88000	6932.46	118000	9295.80
29	Year 30	88000	6331.01	118000	8489.31
30	Year 31	88000	5781.75	118000	7752.80
31	Year 32	88000	5280.13	118000	7080.18
32	Year 33	88000	4822.04	118000	6465.92
33	Year 34	88000	4403.69	118000	5904.95
34	Year 35	88000	4021.63	118000	5392.65
35	Year 36	88000	3672.73	118000	4924.79
36	Year 37	74000	2820.48	90000	3430.32
37	Year 38	74000	2575.78	90000	3132.71
38	Year 39	74000	2352.31	90000	2860.92
39	Year 40	74000	2148.23	90000	2612.71
40	Year 41	74000	1961.86	90000	2386.04
41	Year 42	74000	1791.65	90000	2179.03
42	Year 43	74000	1636.21	90000	1989.98
43	Year 44	74000	1494.25	90000	1817.34
44	Year 45	74000	1364.62	90000	1659.67
45	Year 46	74000	1246.22	90000	1515.68
46	Year 47	74000	1138.10	90000	1384.18
47	Year 48	74000	1039.37	90000	1264.09
			484252.42		549270.84

The choice is still private university, as its net present value is greater