SOLUTION: Finance excel problems — EASY
SOLUTION: Finance excel problems — EASY.
Attached is the complete solution of the question buddy.Its pleasure working with you
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 ($)
Assumptions:
1. It is assumed that the student joined an undergraduate program in 1st September 2017 and will
graduate in 30th August 2021.
2. Tuition Fees: During the entire study in the graduation the each year per term tuition fees are
assumed to be:
i.
ii.
iii.
iv.
For Spring : $ 10000
For First summer: $ 5000
For Second Summer: $ 5000
For Fall term: $ 10000
3. Assuming today to be 1st July 2019, so the discount rate from the 10 years Treasury bond rate
chart is 2.03%
With the above assumptions, the PV cost of the undergraduate program is $119,683.26
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.
Assumptions:
1. It is assumed that the student joined an undergraduate program in 1st September 2017 and will
graduate in 30th August 2021.
2. Assuming today to be 1st July 2019, so the discount rate from the 10 years Treasury bond rate
chart is 2.03%
3. Assuming that during the entire duration of my undergraduate program I am earning $2000
per month and if I would not have been enrolled, I could have earned $800 per month.
With these assumptions, the opportunity cost is $57,467.54
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.
Assumptions:
1. It is assumed that the student joined a firm after graduation in 1st September 2021 and will
work till December 2070
2. Assuming today to be 1st July 2019, so the discount rate from the 10 years Treasury bond rate
chart is 2.03%
3. Assuming that without degree he would be earning $800 per month with annual increment of
$100 and 5 yearly increment of $500. While with degree the student is earning $5000 per month
with annual increment of $500 per year and $2000 every five year from the date of joining.
With these assumptions, the benefit of having degree is $6,376,572.82
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
= $6,376,572.82- $57,467.54- $119,683.26
= $6,199,422.01
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.
-The assumption made are mentioned clearly in each of the Steps.
B) Make observation on your findings. Comment on how your case may be special or different
from other students.
– The Present value of the degree points out how worth the degree is valuable
tomorrow in terms of it being today.
– It also points out the opportunity cost of joining the undergraduate program over
earning in present time.
C) State what else could have been included in this exercise
-Annuity is one of the many which could have been included in the exercise. An annuity
is a fixed stream of payments to an individual and is primarily used as an income stream for
retirees.
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.
Attached.
Time/Date
Tuitions($) Time for Today(In days) Time for Today(In Years)
################# $10,000
668
1.83
################# $5,000
532
1.46
################# $5,000
412
1.13
Sunday, July 1, 2018 $10,000
365
1.00
################# $10,000
303
0.83
################# $5,000
167
0.46
################# $5,000
47
0.13
################# $10,000
0
0.00
################# $10,000
62
0.17
################# $5,000
198
0.54
Friday, May 15, 2020 $5,000
319
0.87
################# $10,000
366
1.00
################# $10,000
428
1.17
################# $5,000
564
1.55
################# $5,000
731
2.00
################# $10,000
793
2.17
PV of the cost of the
Undergraduate Program
Value as on Today
$10,374.64
$5,148.62
$5,114.72
$10,203.00
$10,168.23
$5,046.19
$5,012.96
$10,000.00
$9,965.92
$4,945.79
$4,912.95
$9,800.50
$9,767.10
$4,847.12
$4,802.75
$9,572.77
$119,683.26
Discount Rate
2.03%
Today’s Date
##########
Time/Date
Earnings ($)
#################
#################
#################
#################
#################
#################
#################
Sunday, April 1, 2018
#################
Friday, June 1, 2018
Sunday, July 1, 2018
#################
#################
#################
#################
#################
#################
#################
#################
#################
#################
#################
Monday, July 1, 2019
#################
#################
#################
#################
#################
#################
#################
#################
#################
Friday, May 1, 2020
#################
#################
#################
#################
#################
#################
#################
#################
#################
#################
#################
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
Could have earned
($)
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
800
Difference
($)
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
1200
Time from
today (days)
668
638
607
577
546
515
487
456
426
395
365
334
303
273
242
212
181
150
122
91
61
30
0
31
62
92
123
153
184
215
244
275
305
336
366
397
428
458
489
519
550
581
609
640
Time from
today (years)
1.83
1.75
1.66
1.58
1.50
1.41
1.33
1.25
1.17
1.08
1.00
0.92
0.83
0.75
0.66
0.58
0.50
0.41
0.33
0.25
0.17
0.08
0.00
0.08
0.17
0.25
0.34
0.42
0.50
0.59
0.67
0.75
0.84
0.92
1.00
1.09
1.17
1.25
1.34
1.42
1.51
1.59
1.67
1.75
#################
#################
#################
#################
2000
2000
2000
2000
800
800
800
800
1200
1200
1200
1200
670
701
731
762
1.84
1.92
2.00
2.09
Opputunity Cost
Value as on
today ($)
$1,244.96
$1,242.90
$1,240.78
$1,238.74
$1,236.62
$1,234.51
$1,232.61
$1,230.51
$1,228.48
$1,226.38
$1,224.36
$1,222.27
$1,220.19
$1,218.17
$1,216.10
$1,214.09
$1,212.02
$1,209.95
$1,208.09
$1,206.03
$1,204.04
$1,201.98
$1,200.00
$1,197.95
$1,195.91
$1,193.94
$1,191.90
$1,189.93
$1,187.90
$1,185.88
$1,183.99
$1,181.97
$1,180.02
$1,178.00
$1,176.06
$1,174.05
$1,172.05
$1,170.12
$1,168.12
$1,166.19
$1,164.21
$1,162.22
$1,160.43
$1,158.45
Discount Rate
2.03%
Today’s Date
##########
$1,156.54
$1,154.57
$1,152.66
$1,150.70
$57,467.54
Time/Date
Earnings with
degree ($)
Earning without
degree ($)
Difference
($)
Time from
today (in days)
#################
$5,000
$800
$4,200
793
#################
#################
#################
#################
#################
#################
Friday, April 1, 2022
Sunday, May 1, 2022
#################
Friday, July 1…