Finance excel problems — EASY

Term Paper BFIN 305 – 800: Summer 2019 Submit through D2L. Use only Word and Excel. 25% of your total grade. Due date: 4th July 2019 at 11:59 pm. Show detailed calculations and works. State all your assumptions clearly. Make observations and comment on your findings. In this term paper, you will find the value of your undergraduate 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). This is a term-wise exercise. Use the start of each term as the time/date (column 1 of the table): 15th January for spring, 15th May for first summer, 1st July for second summer, and 1st September for fall terms. Consider all the expenses related to school such as books, fees, etc. as part of Tuition (column 2). Assuming today to be 1st July 2019, 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 value of the tuition as on today (column 4). If the term was in the past, you need to find today’s value in order to find value as on today. If the term is in the future you need to find the PV (present value) in order to find value as on today. You may use the latest 10 years Treasury bond rate (https://ycharts.com/indicators/10_year_treasury_rate) as the discount rate. You just need the latest rate. For past terms: Value as on today = Tuition * (1 + discount rate) time from today in years For future terms: Value as on today = Tuition / (1 + discount rate) time from today in years Finally, add up the last column to find out the PV of the cost of the undergraduate program. Time/Date Tuition ($) Time from today (days) Time from today (years) Value as on today ($) Step 2: Estimate the opportunity cost Since you are a full time or part time student, you might have (and may be in future) foregone earnings that you could have earned had you not enrolled in the program. You may be working for fewer hours a week, 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. This will be a monthly exercise (unlike in step 1 which was a term wise exercise). Rest of the directions and assumptions are similar to as mentioned in Step 1. Time/Date Earnings ($) Could have earned ($) Difference ($) Time from today (days) Time from today (years) Value as on today ($) Finally, add up the last column to find out the present value of the earnings foregone. This is your opportunity cost. 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 the degree makes. This is also a monthly exercise. Start this exercise from the month of graduation and assume that you will be working till 70; you are expected to get a modest pay rise every year that matches the rate of inflation in the recent months. In every 5 years, you may expect to receive a moderate/decent pay rise due to promotion or changing jobs. Your earnings without the degree in the initial months should be in line with the “could have earned” in Step 2. Rest of the directions remain same as in Step 1. However, now you change your discount rate to 11% (we are assuming that this rate is commensurate with the risk of your earnings being different than what you are projecting). Time/Date Earnings with the degree($) Earnings without the degree($) Difference ($) Time from today (years) Value as on today ($) Finally, add up the last column to find out the present value of the benefits of having the degree. Step 4: Calculate the value of your degree The Present Value of your degree = Benefits of the undergraduate degree – Cost of the program – Opportunity Cost Step 5: Analysis A) This exercise requires a number of assumptions to be made by you. Please state all those assumptions clearly and state your basis for such assumptions. B) Make observation on your findings. Comment on how your case may be special or different from other students. C) State what else could have been included in this exercise Special instructions: 1. Start early and submit by deadline. 2. Read this instruction very thoroughly. Ask questions only after you have carefully gone through the instructions. 3. This is a financial exercise. Therefore, ensure that your work makes sense financially. 4. Use MS Excel for all the tables and calculations. 5. Use word document to state assumptions, make comments and observations. 6. Ensure that you show all the calculations and state all the assumptions clearly. 7. After submission, ensure that correct version of all the files have been uploaded. Today Discount rate This is just an example Time/date Tuition/earnings ($) Time from today (days) For a term/month in the past 1-Sep-18 100 303 For a term/month in the future 1-Sep-19 100 62 1-Jul-19 6.00%