I have finished steps 1-2 and am inquiring on step 3
How would you type in and figure out the information on this step for an excel document?
Also if you think my information for 1 or 2 is wrong please feel free to say so.
Step 1: Calculate the cost of the program First fill up the following table to list down the term wise tuitions. Some terms are in the past and some will be in the future (until graduation). Only consider the current stint at MSUB; ignore if you were registered at MSUB in past or other school but did not complete the degree. Use the start of each term as the time/date (column 1 of the table): 15th January for the spring, 15th May for the first summer, 15th June for the second summer, and 1st September for the fall. Consider all the expenses related to school such as books, fees, etc. as part of Tuition (column 2). Assuming today is 1st July 2020, find the time difference (column 3) between today and the date for each term. Time from today (years) = Time from today (in days)/365 Now find the present value of the tuition (column 5). Use the formulas provided below to find the present value. Use discount rate = 2%. For past terms: Present Value = Tuition * (1 + discount rate) time from today in years For future terms: Present Value = Tuition / (1 + discount rate) time from today in years Time/Date Tuition ($) Time from today (days) Time from today (years) Present Value ($) Finally, add up the last column to find out the PV of the cost of the undergraduate program. Today Discount rate 1-Jul-20 2.00% Tuition/earnings ($) Time from today (days) Time/date Time from today (years) Present Value ($) 1-Sep-13 15-Jan-14 15-May-14 1-Sep-14 15-Jan-15 15-May-15 1-Sep-15 15-Jan-16 1-Sep-16 15-Jan-17 1-Sep-17 15-Jan-18 1-Sep-18 15-Jan-19 15-May-19 1-Sep-19 15-Jan-20 15-May-20 1-Sep-20 15-Jan-21 15-May-21 1800 1750 1300 1800 2100 1000 1750 1250 1200 1800 1250 1650 2200 1950 1800 1200 1850 1900 1200 1850 1900 2495 2359 2239 2130 1994 1874 1765 1629 1399 1263 1034 898 669 533 413 304 168 47 62 198 318 6.8356 6.4630 6.1342 5.8356 5.4630 5.1342 4.8356 4.4630 3.8329 3.4603 2.8329 2.4603 1.8329 1.4603 1.1315 0.8329 0.4603 0.1288 0.1699 0.5425 0.8712 Total PV 2,060.91 1,988.94 1,467.91 2,020.50 2,339.93 1,107.02 1,925.86 1,365.50 1,294.63 1,927.66 1,322.13 1,732.38 2,281.32 2,007.21 1,840.79 1,219.96 1,866.94 1,904.85 1,195.97 1,830.23 1,867.50 36,568.14 The present value of my undergraduate program is $36,568.14 Step 2: Estimate the opportunity cost Since you are a full time or part time student, you must be foregoing some earnings that you could have earned had you not been enrolled in the program. You may be working for fewer hours, or not working at all, or working in a place with lower wage. Find the difference between what you could have earned and what you have actually earned. Estimate similar difference for the future months as well, as you continue until graduation. In the last column find the present value of the difference. This is a monthly exercise (unlike step 1 which was a term-wise exercise). Rest of the directions and assumptions are similar to those in Step 1. Earnings ($) Could have earned ($) Difference ($) Time from today (days) Time from today (years) Present Value ($) Time/Date Finally, add up the last column and multiply the sum by 0.88 (assuming 12% tax rate) to find your opportunity cost. A B D E F G Today Discount rate 1-Jul-20 2.00% Sum of PV Opportunity cost 68512.79 60291.25 Earnings 2352 Difference 680 Could have earned 3032 3032 3032 Present Value 778.57 777.30 776.00 2352 Time from Today Days 2495 2465 2434 2404 2373 680 2352 680 2352 2419.2 680 680 2419.2 2419.2 680 680 2342 2314 2283 2253 680 680 2419.2 2419.2 2419.2 2419.2 2419.2 2419.2 3032 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 1 Step 2 2 3 4 Time/Date 5 1-Sep-13 6 1-Oct-13 7 1-Nov-13 8 1-Dec-13 9 1-Jan-14 10 1-Feb-14 11 1-Mar-14 12 1-Apr-14 13 1-May-14 14 1-Jun-14 15 1-Jul-14 16 1-Aug-14 17 1-Sep-14 18 1-Oct-14 19 1-Nov-14 20 1-Dec-14 21 1-Jan-15 22 1-Feb-15 23 1-Mar-15 24 1-Apr-15 25 1-May-15 26 1-Jun-15 27 1-Jul-15 28 1-Aug-15 29 1-Sep-15 30 1-Oct-15 31 1-Nov-15 32 1-Dec-15 680 680 2222 2192 2161 2130 Time from today Years 6.8356 6.7534 6.6685 6.5863 6.5014 6.4164 6.3397 6.2548 6.1726 6.0877 6.0055 5.9205 5.8356 5.7534 5.6685 5.5863 5.5014 5.4164 5.3397 5.2548 5.1726 5.0877 680 680 680 2419.2 2419.2 680 680 774.73 773.43 772.13 770.96 769.66 768.41 767.12 765.87 764.59 763.30 762.06 760.78 759.54 758.27 756.99 755.84 754.57 753.35 752.08 750.86 749.59 748.33 747.12 745.86 2419.2 2688 3368 2100 2069 2039 2008 1977 1949 1918 1888 680 3368 680 2688 2688 680 3368 3368 3368 2688 2688 680 680 2688 680 1857 3368 3368 2688 680 1827 3368 680 1796 2688 2688 5.0055 4.9205 4.8356 4.7534 3368 680 1765 1735 2688 3368 680 2688 3368 680 4.6685 1704 1674 2688 3368 680 4.5863 744.65 32 2688 3368 680 1674 744.65 4.5863 4.5014 33 3659.2 1643 743.40 680 680 34 1612 4.4164 742.15 1583 35 36 37 38 1-Dec-15 1-Jan-16 1-Feb-16 1-Mar-16 1-Apr-16 1-May-16 1-Jun-16 1-Jul-16 1-Aug-16 1-Sep-16 1-Oct-16 3659.2 3659.2 3659.2 3659.2 3659.2 1552 680 680 680 4.3370 4.2521 4.1699 4.0849 1522 680 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 1491 1461 740.98 739.74 738.53 737.29 736.09 734.86 733.62 732.43 39 680 4.0027 40 680 41 680 680 1430 1399 1369 42 3.9178 3.8329 3.7507 3.6658 3.5836 43 3659.2 3659.2 3659.2 3659.2 3659.2 3659.2 3592 3592 3592 1-Nov-16 680 1338 731.20 44 1-Dec-16 1308 45 1277 680 680 680 680 2912 2912 2912 46 1246 1218 47 48 2912 3592 680 1187 3.4986 3.4137 3.3370 3.2521 3.1699 3.0849 3.0027 2.9178 49 2912 3592 680 1157 730.01 728.78 727.56 726.45 725.23 724.05 722.84 721.66 720.45 719.24 718.07 716.86 50 3592 1126 2912 2912 2912 680 680 51 3592 3592 1096 1065 52 53 2912 3592 1034 2.8329 1-Jan-17 1-Feb-17 1-Mar-17 1-Apr-17 1-May-17 1-Jun-17 1-Jul-17 1-Aug-17 1-Sep-17 1-Oct-17 1-Nov-17 1-Dec-17 1-Jan-18 1-Feb-18 1-Mar-18 1-Apr-18 1-May-18 1-Jun-18 1-Jul-18 2912 54 55 1004 680 680 680 680 680 2.7507 3592 3592 2912 973 2.6658 56 943 715.70 2912 3024 680 57 58 59 3592 3704 3704 3704 3704 3024 912 881 853 714.49 713.29 680 680 2.5836 2.4986 2.4137 2.3370 2.2521 3024 712.21 60 3024 680 822 711.01 61 3704 792 3024 3024 62 3704 680 680 680 761 2.1699 2.0849 2.0027 709.86 708.66 707.51 63 3024 3704 731 63 64 65 680 680 2.0027 1.9178 707.51 706.32 1-Jul-18 1-Aug-18 1-Sep-18 1-Oct-18 3024 3024 3024 3024 3024 3704 3704 3704 3704 3704 731 700 669 639 680 1.8329 705.13 66 680 1.7507 1.6658 703.99 702.80 67 1-Nov-18 680 608 68 3024 680 578 701.66 680 700.48 69 70 3030.72 3030.72 3704 3710.72 3710.72 3816 1.5836 1.4986 1.4137 1.3370 547 516 488 680 71 3136 680 699.31 698.24 697.07 72 680 3136 3136 3136 3816 3816 457 427 73 680 695.94 3816 396 74 75 76 680 680 3136 366 1.2521 1.1699 1.0849 1.0027 0.9178 0.8329 0.7507 0.6658 3136 3816 3816 3816 3816 335 680 680 694.77 693.64 692.47 691.31 690.18 77 3136 304 78 3136 680 274 3816 680 79 80 81 3136 3136 243 213 680 1-Dec-18 1-Jan-19 1-Feb-19 1-Mar-19 1-Apr-19 1-May-19 1-Jun-19 1-Jul-19 1-Aug-19 1-Sep-19 1-Oct-19 1-Nov-19 1-Dec-19 1-Jan-20 1-Feb-20 1-Mar-20 1-Apr-20 1-May-20 1-Jun-20 1-Jul-20 1-Aug-20 1-Sep-20 1-Oct-20 1-Nov-20 1-Dec-20 1-Jan-21 1-Feb-21 3816 3816 0.5836 0.4986 182 3136 3248 680 680 689.02 687.90 686.75 685.59 684.52 3928 151 0.4137 82 83 3248 3928 680 122 84 3248 3928 680 91 0.3342 0.2493 0.1671 0.0822 683.37 682.25 85 3248 680 61 3928 3928 86 680 3248 3248 30 0 681.11 680.00 87 680 0.0000 88 3248 3928 3928 3928 31 0.0849 678.86 680 680 0.1699 89 90 3248 3248 3248 3928 3928 680 680 62 92 123 0.2521 0.3370 91 677.72 676.61 675.48 674.38 673.25 672.11 92 3248 153 3928 3928 93 3248 680 680 680 184 0.4192 0.5041 0.5890 94 3248 3928 215 94 1-Feb-21 3248 3928 680 215 0.5890 672.11 95 3250.24 3930.24 680 243 0.6658 671.09 96 3250.24 3930.24 680 274 0.7507 669.97 1-Mar-21 1-Apr-21 1-May-21 1-Jun-21 97 3250.24 3930.24 680 304 0.8329 668.88 98 3360 4040 680 335 0.9178 667.75 99 1-Jul-21 3360 4040 680 365 1.0000 666.67 Step 3: Find out the benefits of the undergraduate degree In this step we will calculate the monitory value of your degree. This is valued as the difference in earnings that this degree makes. Make this an annual exercise. Start this exercise from the month of graduation and assume that you will work until you tun 65 without any break. You are expected to get a modest raise in pay every year that matches the rate of inflation in the recent months. In every 5 years, you should expect to receive an extra raise due to promotion or job change. Your earnings without the degree in the initial months should be in line with the could have earned in Step 2. In the last column find the present value of the difference. Rest of the directions remain same as in Step 1. However, now you change your discount rate to 8%. Earnings with the degree($) Earnings without the degree($) Difference ($) Time from today (years) Present Value ($) Time/Date Finally, add up the last column and then multiply the addition by 0.82 (assuming 18% tax rate) to find out the present monetary value of the benefits of having this degree. Step 1: Calculate the cost of the program First fill up the following table to list down the term wise tuitions. Some terms are in the past and some will be in the future (until graduation). Only consider the current stint at MSUB; ignore if you were registered at MSUB in past or other school but did not complete the degree. Use the start of each term as the time/date (column 1 of the table): 15th January for the spring, 15th May for the first summer, 15th June for the second summer, and 1st September for the fall. Consider all the expenses related to school such as books, fees, etc. as part of Tuition (column 2). Assuming today is 1st July 2020, find the time difference (column 3) between today and the date for each term. Time from today (years) = Time from today (in days)/365 Now find the present value of the tuition (column 5). Use the formulas provided below to find the present value. Use discount rate = 2%. For past terms: Present Value = Tuition * (1 + discount rate) time from today in years For future terms: Present Value = Tuition / (1 + discount rate) time from today in years Time/Date Tuition ($) Time from today (days) Time from today (years) Present Value ($) Finally, add up the last column to find out the PV of the cost of the undergraduate program. Today Discount rate 1-Jul-20 2.00% Tuition/earnings ($) Time from today (days) Time/date Time from today (years) Present Value ($) 1-Sep-13 15-Jan-14 15-May-14 1-Sep-14 15-Jan-15 15-May-15 1-Sep-15 15-Jan-16 1-Sep-16 15-Jan-17 1-Sep-17 15-Jan-18 1-Sep-18 15-Jan-19 15-May-19 1-Sep-19 15-Jan-20 15-May-20 1-Sep-20 15-Jan-21 15-May-21 1800 1750 1300 1800 2100 1000 1750 1250 1200 1800 1250 1650 2200 1950 1800 1200 1850 1900 1200 1850 1900 2495 2359 2239 2130 1994 1874 1765 1629 1399 1263 1034 898 669 533 413 304 168 47 62 198 318 6.8356 6.4630 6.1342 5.8356 5.4630 5.1342 4.8356 4.4630 3.8329 3.4603 2.8329 2.4603 1.8329 1.4603 1.1315 0.8329 0.4603 0.1288 0.1699 0.5425 0.8712 Total PV 2,060.91 1,988.94 1,467.91 2,020.50 2,339.93 1,107.02 1,925.86 1,365.50 1,294.63 1,927.66 1,322.13 1,732.38 2,281.32 2,007.21 1,840.79 1,219.96 1,866.94 1,904.85 1,195.97 1,830.23 1,867.50 36,568.14 The present value of my undergraduate program is $36,568.14 Step 2: Estimate the opportunity cost Since you are a full time or part time student, you must be foregoing some earnings that you could have earned had you not been enrolled in the program. You may be working for fewer hours, or not working at all, or working in a place with lower wage. Find the difference between what you could have earned and what you have actually earned. Estimate similar difference for the future months as well, as you continue until graduation. In the last column find the present value of the difference. This is a monthly exercise (unlike step 1 which was a term-wise exercise). Rest of the directions and assumptions are similar to those in Step 1. Earnings ($) Could have earned ($) Difference ($) Time from today (days) Time from today (years) Present Value ($) Time/Date Finally, add up the last column and multiply the sum by 0.88 (assuming 12% tax rate) to find your opportunity cost. A B D E F G Today Discount rate 1-Jul-20 2.00% Sum of PV Opportunity cost 68512.79 60291.25 Earnings 2352 Difference 680 Could have earned 3032 3032 3032 Present Value 778.57 777.30 776.00 2352 Time from Today Days 2495 2465 2434 2404 2373 680 2352 680 2352 2419.2 680 680 2419.2 2419.2 680 680 2342 2314 2283 2253 680 680 2419.2 2419.2 2419.2 2419.2 2419.2 2419.2 3032 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 3099.2 1 Step 2 2 3 4 Time/Date 5 1-Sep-13 6 1-Oct-13 7 1-Nov-13 8 1-Dec-13 9 1-Jan-14 10 1-Feb-14 11 1-Mar-14 12 1-Apr-14 13 1-May-14 14 1-Jun-14 15 1-Jul-14 16 1-Aug-14 17 1-Sep-14 18 1-Oct-14 19 1-Nov-14 20 1-Dec-14 21 1-Jan-15 22 1-Feb-15 23 1-Mar-15 24 1-Apr-15 25 1-May-15 26 1-Jun-15 27 1-Jul-15 28 1-Aug-15 29 1-Sep-15 30 1-Oct-15 31 1-Nov-15 32 1-Dec-15 680 680 2222 2192 2161 2130 Time from today Years 6.8356 6.7534 6.6685 6.5863 6.5014 6.4164 6.3397 6.2548 6.1726 6.0877 6.0055 5.9205 5.8356 5.7534 5.6685 5.5863 5.5014 5.4164 5.3397 5.2548 5.1726 5.0877 680 680 680 2419.2 2419.2 680 680 774.73 773.43 772.13 770.96 769.66 768.41 767.12 765.87 764.59 763.30 762.06 760.78 759.54 758.27 756.99 755.84 754.57 753.35 752.08 750.86 749.59 748.33 747.12 745.86 2419.2 2688 3368 2100 2069 2039 2008 1977 1949 1918 1888 680 3368 680 2688 2688 680 3368 3368 3368 2688 2688 680 680 2688 680 1857 3368 3368 2688 680 1827 3368 680 1796 2688 2688 5.0055 4.9205 4.8356 4.7534 3368 680 1765 1735 2688 3368 680 2688 3368 680 4.6685 1704 1674 2688 3368 680 4.5863 744.65 32 2688 3368 680 1674 744.65 4.5863 4.5014 33 3659.2 1643 743.40 680 680 34 1612 4.4164 742.15 1583 35 36 37 38 1-Dec-15 1-Jan-16 1-Feb-16 1-Mar-16 1-Apr-16 1-May-16 1-Jun-16 1-Jul-16 1-Aug-16 1-Sep-16 1-Oct-16 3659.2 3659.2 3659.2 3659.2 3659.2 1552 680 680 680 4.3370 4.2521 4.1699 4.0849 1522 680 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 2979.2 1491 1461 740.98 739.74 738.53 737.29 736.09 734.86 733.62 732.43 39 680 4.0027 40 680 41 680 680 1430 1399 1369 42 3.9178 3.8329 3.7507 3.6658 3.5836 43 3659.2 3659.2 3659.2 3659.2 3659.2 3659.2 3592 3592 3592 1-Nov-16 680 1338 731.20 44 1-Dec-16 1308 45 1277 680 680 680 680 2912 2912 2912 46 1246 1218 47 48 2912 3592 680 1187 3.4986 3.4137 3.3370 3.2521 3.1699 3.0849 3.0027 2.9178 49 2912 3592 680 1157 730.01 728.78 727.56 726.45 725.23 724.05 722.84 721.66 720.45 719.24 718.07 716.86 50 3592 1126 2912 2912 2912 680 680 51 3592 3592 1096 1065 52 53 2912 3592 1034 2.8329 1-Jan-17 1-Feb-17 1-Mar-17 1-Apr-17 1-May-17 1-Jun-17 1-Jul-17 1-Aug-17 1-Sep-17 1-Oct-17 1-Nov-17 1-Dec-17 1-Jan-18 1-Feb-18 1-Mar-18 1-Apr-18 1-May-18 1-Jun-18 1-Jul-18 2912 54 55 1004 680 680 680 680 680 2.7507 3592 3592 2912 973 2.6658 56 943 715.70 2912 3024 680 57 58 59 3592 3704 3704 3704 3704 3024 912 881 853 714.49 713.29 680 680 2.5836 2.4986 2.4137 2.3370 2.2521 3024 712.21 60 3024 680 822 711.01 61 3704 792 3024 3024 62 3704 680 680 680 761 2.1699 2.0849 2.0027 709.86 708.66 707.51 63 3024 3704 731 63 64 65 680 680 2.0027 1.9178 707.51 706.32 1-Jul-18 1-Aug-18 1-Sep-18 1-Oct-18 3024 3024 3024 3024 3024 3704 3704 3704 3704 3704 731 700 669 639 680 1.8329 705.13 66 680 1.7507 1.6658 703.99 702.80 67 1-Nov-18 680 608 68 3024 680 578 701.66 680 700.48 69 70 3030.72 3030.72 3704 3710.72 3710.72 3816 1.5836 1.4986 1.4137 1.3370 547 516 488 680 71 3136 680 699.31 698.24 697.07 72 680 3136 3136 3136 3816 3816 457 427 73 680 695.94 3816 396 74 75 76 680 680 3136 366 1.2521 1.1699 1.0849 1.0027 0.9178 0.8329 0.7507 0.6658 3136 3816 3816 3816 3816 335 680 680 694.77 693.64 692.47 691.31 690.18 77 3136 304 78 3136 680 274 3816 680 79 80 81 3136 3136 243 213 680 1-Dec-18 1-Jan-19 1-Feb-19 1-Mar-19 1-Apr-19 1-May-19 1-Jun-19 1-Jul-19 1-Aug-19 1-Sep-19 1-Oct-19 1-Nov-19 1-Dec-19 1-Jan-20 1-Feb-20 1-Mar-20 1-Apr-20 1-May-20 1-Jun-20 1-Jul-20 1-Aug-20 1-Sep-20 1-Oct-20 1-Nov-20 1-Dec-20 1-Jan-21 1-Feb-21 3816 3816 0.5836 0.4986 182 3136 3248 680 680 689.02 687.90 686.75 685.59 684.52 3928 151 0.4137 82 83 3248 3928 680 122 84 3248 3928 680 91 0.3342 0.2493 0.1671 0.0822 683.37 682.25 85 3248 680 61 3928 3928 86 680 3248 3248 30 0 681.11 680.00 87 680 0.0000 88 3248 3928 3928 3928 31 0.0849 678.86 680 680 0.1699 89 90 3248 3248 3248 3928 3928 680 680 62 92 123 0.2521 0.3370 91 677.72 676.61 675.48 674.38 673.25 672.11 92 3248 153 3928 3928 93 3248 680 680 680 184 0.4192 0.5041 0.5890 94 3248 3928 215 94 1-Feb-21 3248 3928 680 215 0.5890 672.11 95 3250.24 3930.24 680 243 0.6658 671.09 96 3250.24 3930.24 680 274 0.7507 669.97 1-Mar-21 1-Apr-21 1-May-21 1-Jun-21 97 3250.24 3930.24 680 304 0.8329 668.88 98 3360 4040 680 335 0.9178 667.75 99 1-Jul-21 3360 4040 680 365 1.0000 666.67 Step 3: Find out the benefits of the undergraduate degree In this step we will calculate the monitory value of your degree. This is valued as the difference in earnings that this degree makes. Make this an annual exercise. Start this exercise from the month of graduation and assume that you will work until you tun 65 without any break. You are expected to get a modest raise in pay every year that matches the rate of inflation in the recent months. In every 5 years, you should expect to receive an extra raise due to promotion or job change. Your earnings without the degree in the initial months should be in line with the could have earned in Step 2. In the last column find the present value of the difference. Rest of the directions remain same as in Step 1. However, now you change your discount rate to 8%. Earnings with the degree($) Earnings without the degree($) Difference ($) Time from today (years) Present Value ($) Time/Date Finally, add up the last column and then multiply the addition by 0.82 (assuming 18% tax rate) to find out the present monetary value of the benefits of having this degree
