Question

Matt and Debra Baxter live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned business. Debra stays home with their child, Brady, who is age 5.

After visiting with their financial planner, the couple became concerned that they were spending too much and not putting enough funds aside for Brady’s future educational needs. Matt earns $85,000 per year, but with the rising costs of education, they are concerned.

Matt is an alumni of Duke University, a prestigious school with tuition and book expenses of approximately $18,000 per year. Debra graduated from Utah Valley University. The expense for tuition and books there is estimated at about $8,000 per year. When Brady turns 18, the couple wishes to send him to one of these two exceptional universities. They have a slight preference for Utah Valley University. The problem, however, is that with the rate at which tuition is increasing the Baxter’s are not sure they can save enough money and they have decided they do not want to borrow to pay for Brady’s education. Assume the tuition at both universities will increase at an annual rate of 5% from now until Brady starts college.

Living expenses are currently estimated to be $9,000 per year at both schools. This expense is expected to increase at only 3% per year. Further, assume that Baxter’s can deposit their money into a growth oriented mutual fund which has historically earned 12% per annum.

The couple wishes to save by having a pre-determined amount automatically withdrawn from their bank account at the end of each month. They plan to contribute from now until Brady starts college. When Brady starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account when Brady starts college so the principle and interest will cover all four years of his college expenses. They will make annual withdrawals from the account to cover both tuition and living expenses for Brady at the beginning of his freshman, sophomore, junior, and senior years. When the withdrawal for the senior year is made the account balance will be zero.

Complete a thorough analysis and write a professional letter to the Baxter’s (who don’t understand finance) explaining the analysis you performed, why you performed it, and the results and conclusions. In the letter and attached schedules provide information that answers the following questions.

-When Brady is 18, what will be the tuition expense, living expense, and total expense for each of the four years that Brady will attend college? Provide the information for each University.

-What amount will be needed in the account when Brady starts his freshman year if he attends Duke? What amount if he attends UVU?--

-How much money will Matt and Debra have to deposit at the end of each month to allow Brady to attend Duke? How much money will have to be deposited per month to allow Brady to attend Utah Valley University? Assume that Matt and Debra stop making deposits when Brady starts college.

-The Baxter’s are concerned that given the current market performance the mutual fund will only earn 9% per year. Redo the analysis assuming they can earn only 9% per year on their investments. How much will be needed in the account when Brady starts college and how much will have to be deposited per month for Brady to have sufficient funds to attend each school?

Please show excel workthrough!

Answer 1

1.) Duke University

The tuition expenses of brady in freshman year=current tuition expenses*(1+inflation rate)^(age at the freshman year-current age)

= 18000*(1+0.05)^(18-5)

=18000*(1.05)^13=$33941.68

The tuition fees for the brady in sophomore year= Tuition expenses in freshman year*(1.05)=33941.68*(1.05)=$35638.77

The tuition expenses for the brady in junior year=tuition expenses in junior year*(1.05)=$37420.70

The tuition expenses for the brady in senior year=tuition expenses in junior year*(1.05)=37420.70*(1.05)=$39291.74

The total tuition expenses for all four years if he attends duke university=$(33941.68+35638.77+37420.70+39291.74)=$146292.90

Utah Valley University

The tutiion fees for the brady in freshman year= current tuition expenses*(1+inflation rate)^(age at the freshman year-current age)

=8000*(1..05)^(18-5)=$15085.19

The tuition fees for the brady in sophomore year= 15085.19*(1.05)=$15839.45

The tuition expenses for the brady in junior year=tuition expenses in junior year*(1.05)=15839.45*1.05=$16631.425

The tuition expenses for the brady in senior year=tuition expenses in senior year*(1.05)=16631.425*(1.05)=$17463

The total tuition expenses for the brady for four years=15085.19+15839.45+16631.425+17463=$65019 app.

Since, The living expenses are estimated to be equal at both universities

Therefore, the living expenses for the brady in freshman year=current living expenses*(1+inflation rate)^(age at the freshman year-current age)

=9000*(1.03)^(18-5)=$13216.80

The living expense for the brady in sophomore year= 13216.80*(1.03)=$13613.30

The living expenses for the brady in junior year=living expenses in sophomore year*(1.03)=13613.30*1.03=$14021.70

The living expenses for the brady in senior year=living expenses in junior year*(1.03)=14021.70*1.03=$14442.36

The total living expenses that occured during all four years irrespective brady chooses to study at duke or utah valley university=$(13216.80+13613.30+14021.70+14442.36)=$55294.158

The total expenses that occured during freshman year for brady at duke university=$(33941.68+13216.80)=$47158.48

The total expenses that occured during sophomore year for brady at duke university=$(35638.77+13613.30)=$49252.07

The total expenses that occured during junior year for brady at duke university=$(37420.70+14021.70)=$51442.4

The total expenses that occured during senior year for brady at duke university=$(39291.74+14442.36)=$53734.1

Therefore, the total expenses that occured during freshman year for brady at Utah valley university=$(15085.19+13216.80)=$28302

The total expenses that occured during sophomore year for brady at Utah university=$(15839.45+13613.30)=$29452.75

The total expenses that occured during junior year for brady at Utah university=$(16631.425+14021.70)=$30653.125

The total expenses that occured during senior year for brady at Utah university=$(17463+14442.36)=$31905.36

b.) the amount needed in bank account at start of freshman year if he joins duke university=amount needed in first year to cover freshmen year expenses + amount needed in second year to cover sophomore year expenses + amount needed in third year to cover junior year expenses + amount needed in fourth year to cover senior expenses=47158.48*(1.12)^(-1)+49252.07*(1.12)^-2+51442.4*(1.12)^(-3)+53734.1*(1.12)^(-4)=$152133.91

The amount needed at start of freshmen year if he joins Utah valley university=amount needed in first year to cover freshmen year expenses + amount needed in second year to cover sophomore year expenses +  amount needed in third year to cover junior year expenses +  amount needed in fourth year to cover senior expenses=28302*(1.12)^(-1) + 29452.75*(1.12)^(-2) + 30653.125*((1.12)^(-3) + 31905.36*(1.12)^(-4) = $90843.92

c.)The concept of annuity will be used here. Therefore, amount needed at start of freshmen year if he attends Duke university=152133.91

Let assume that P be the amount that matt and debre will contribute each month in the fund

present value of all monthly contributions=amount needed at start of the freshman year

12P[1-(1+interest rate)^(-(18-5))/i^(12)]=152133.91

12P [1-1.12^(-13)/0.11386]=152133.91

12P*6.7699=152133.91

P=152133.91/81.23932

P=1872.6633

The monthly contribution at the end of each month should be $1872.6633

Similarly,

amount needed at start of freshmen year if he attends Utah valley university= $90843.92

Let assume that P be the amount that matt and debre will contribute each month in the fund

present value of all monthly contributions=amount needed at start of the freshman year

12P[1-(1.12)^(-13)/0.11386]= 90843.92

P=90843.92/81.23932

P= $ 1118.226

The monthly contribution at the end of each month should be $1118.226

d.) The  amount needed in bank account at start of freshman year if he joins duke university=amount needed in first year to cover freshmen year expenses + amount needed in second year to cover sophomore year expenses + amount needed in third year to cover junior year expenses + amount needed in fourth year to cover senior expenses=

Therefore the amount needed at start of the freshmen year if he joins duke university is $ 162508.7051

he amount needed at start of freshmen year if he joins Utah valley university=amount needed in first year to cover freshmen year expenses + amount needed in second year to cover sophomore year expenses +  amount needed in third year to cover junior year expenses +  amount needed in fourth year to cover senior expenses=

Therefore, the amount needed at start of the year if he attends Utah valley university is $97031.5346.

Now,Let assume that P be the amount that matt and debre will contribute each month in the fund

present value of all monthly contributions=amount needed at start of the freshman year

12P[1-(1+interest rate)^(-(18-5))/i^(12)]=162508.7051

12P [1-1.09^(-13)/0.086488]=162508.7051

12P*7.79093=162508.7051

P=162508.7051/93.4912

P=1738.224

Note: i(12) =12[(1+i)^(1/12)-1]

The monthly contribution at the end of each month should be $1738.224.

Similarly,

amount needed at start of freshmen year if he attends Utah valley university= $ 97031.53457

Let assume that P be the amount that Matt and Debra will contribute each month in the fund

present value of all monthly contributions=amount needed at start of the freshman year

12P[1-(1.09)^(-13)/0.086488]= 90843.92

P=90843.92/93.49120

P= $ 971.6841

The required monthly contribution at the end of each month if he attends Utah valley university is $ 971.6841.

A C E 0.09 Amount discount factor present value 1 Interest rate 47158.48(1+B1)^-1 Total Expenses occurred during freshman year 2 43264.66055 Total Expenses occurred during sophomore year 3 49252.07 0.841679993 41454.48195 Total Expenses occurred during junior year 4 51442.4 0.77218348 39722.97145 CL Total Expenses occurred during senior year 53734.1 0.708425211 38066.59113 Amount needed at the start of the year if he attends duke university 6 162508.7051 s n

A C F discount factor present value 0.09 Amount 1 Interest rate Total Expenses occurred during freshman year 2 28302 0.917431193 25965.13761 Total Expenses occurred during sophomore year 3 29457.75 0.841679993 24793.99882 Total Expenses occurred during junior year 30653.13 0.77218348 4 23669.83674 Total Expenses occurred during senior year 5 31905.36 0.708425211 22602.56139 Amount needed at the start of the year if he attends duke university 97031.53457 6