Question

NEED EXCEL USE OF PV and FV.Thanks

Ben Bates graduated from college six years ago with a finance undergraduate degree. Although he is satisfied with his current job, his goal is to become an investment banker. He feels that an MBA degree would allow him to achieve this goal. After examining schools, he has narrowed his choice to either Wilton University or Mount Perry College. Although internships are encouraged by both schools, to get class credit for the internship, no salary can be paid. Other than internships, neither school will allow its students to work while enrolled in its MBA program. Ben currently works at the money management firm of Dewey and Louis. On average, his annual salary until retirement is expected to be $80,000 per year. He is currently 28 years old and expects to work for 40 more years. His current job includes a fully paid health insurance plan, and the tax rate is 26 percent. Ben has a savings account with enough money to cover the entire cost of his MBA program. The Ritter College of Business at Wilton University is one of the top MBA programs in the country. The MBA degree requires two years of full-time enrollment at the university. The annual tuition is $80,000, payable at the beginning of each school year. Books and other supplies are estimated to cost $3,000 per year. Ben expects that after graduation from Wilton, he will receive a job offer of approximately $135,000 annual salary on average until retirement. Because of the higher salary, his average income tax rate will increase to 32 percent. The Bradley School of Business at Mount Perry College began its MBA program 16 years ago. The Bradley School is smaller and less well known than the Ritter College. Bradley offers an accelerated, one-year program, with a tuition cost of $100,000 to be paid upon matriculation. Books and other supplies for the program are expected to cost $4,500. Ben thinks that he will receive an offer of approximately $118,000 annual salary on average until retirement. His average tax rate at this level of income will be 29 percent. Both schools offer a health insurance plan that will cost $3,000 per year, payable at the beginning of the year. Ben also estimates that room and board expenses will cost $2,000 more per year at both schools than his current expenses, payable at the beginning of each year. The appropriate discount rate is 6 percent.  

Answer 1

1. Option 1- Continue current job

Salary = $ 80,000

Tax rate =26%

Growth rate = 0%

Years left to work =40

After tax salary = Salary*(1-t) = 80,000*(1-26%) = 59,200

Discount rate = 6%

PV of growth annuity =

PV = 59200 / (0.06) *[ 1- (1.06)^-40] = [59,200 / 0.06]*(0.09722) = $1,203,704

PV = $1,203,704

FV after 40 years = PV*(1+r) 4⁰ = 1,203,7047*(1+0.06)40 = 12,380,957

Option 2  The Ritter College of Business at Wilton University

The calculations of PV 2 years from now

a) PV of salary for 38 years (40-2 years)

b) PV of cost in college

c) PV of salary lost of current job

Salary = $ 1,35,000

Tax rate =32%

Years left to work =38

After tax salary = Salary*(1-t) = 135,000*(1-32%) = $ 91,800

Discount rate = 6%

PV of annuity =

PV = 91,800 / (0.06) *[ 1- (1.06)^-38] = [91,800 / 0.06]*(1-0.8908) = $1,362,864

PV = $1,362,864

b) PV of cost = Tuition + Health insurance + Room +Books =80000 + 3000+ 2000 + 3000 = 87,000

n=2

PV =

PV = 87,000*(1-(1+0.06)^-2)/0.06 *(1+0.06) = 87,000*1.9434

PV = 171,018

c) PV if he continued in current job

Salary = $80,000

Tax rate =26%

Years =2

After tax salary = Salary*(1-t) = 80,000*(1-26%) = $59,200

Discount rate = 6%

PV of growth annuity =

PV = 59,200 / (0.06) *[ 1- (1.06)^-2] = [59,200 / 0.06]*(1-0.89) = $108,537

PV = $108,537

NPV = PV of Salary after MBA - PV of expense - PV of loss of salary

NPV after 2 years = 1,362,865 - 171018 - 1408,537 = 1,083,309

FV after 38 years = NPV(1+r)n = 1,083,309 *(1+0.06)³⁸ = 9,916,882

Option 3 The Bradley School of Business at Mount Perry College

The calculations of PV 1 years from now

a) PV of salary for 39 years (40-1 years)

b) PV of cost in college

c) PV of salary lost of current job

Salary = $ 118,000

Tax rate =29%

Years left to work =39

After tax salary = Salary*(1-t) = 118,000*(1-29%) = $ 83,780

Discount rate = 6%

PV of annuity =

PV = 83,780 / (0.06) *[ 1- (1.06)^-39] = [91,800 / 0.06]*(1-0.8908) = $1,252,433

PV = $1,252,433

b) PV of cost = Tuition + Health insurance + Room +Books =100,000 + 3000+ 2000 + 4500 = 109,500

n=1

PV =

PV = 109,500*(1-(1+0.06)-1)/0.06 *(1+0.06) = 109,500

PV = 109,500

c) PV if he continued in current job

Salary = $80,000

Tax rate =26%

Years =1

After tax salary = Salary*(1-t) = 80,000*(1-26%) = $59,200

Discount rate = 6%

PV of growth annuity =

PV = 59,200 / (0.06) *[ 1- (1.06)^-1] = [59,200 / 0.06]*(1-0.9433) = $55,849

PV = $55,849

NPV = PV of Salary after MBA - PV of expense - PV of loss of salary

NPV after 1 years = 1,252,433 - 109,500 - 55,849= 1,087,084

FV after 39 years = NPV(1+r)n = 1,087,084 *(1+0.06)³⁹ =10,548,532

Thus as per FV, continuing current job is the best option