# 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…