Question

Matt and Martha Maxwell live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned automotive painting business. Martha stays home with their child, Melanie, 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 Melanie’s future educational needs. Matt earns $105,000 per year, but with the rising costs of education, they are concerned. Matt is an alumnus of Ohio State University, with tuition and book expenses of approximately $13,600 per year. Martha graduated from Utah Valley University. The expense for tuition and books there is estimated at about $7,500 per year. When Melanie turns 18, the couple wishes to send her 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 Maxwell’s are not sure they can save enough money and they have decided they do not want to borrow to pay for Melanie’s education. Assume the tuition at both universities will increase at an annual rate of 6% per year for the foreseeable future. Living expenses are currently estimated at $8,000 per year at both schools. This expense is expected to grow at only 3% per year. Further, assume that Maxwell’s can deposit their money into a growth oriented mutual fund at the Salt Lake City based mutual fund company, Wasatch Advisors which has historically earned 12% per annum. The couple wishes to save by having a pre-determined amount automatically withdrawn form their bank account at the end each month. They plan to contribute from now until Melanie starts college. When Melanie starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account to cover all four years of college expenses when Melanie starts college. They will make annual withdrawals from the account to cover both tuition and living expenses for Melanie 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. Remember, that the funds will continue to earn interest while Melanie is in college. Complete a thorough analysis and write a professional letter to the Maxwell’s (who don’t understand finance) explaining the analysis you performed, why you performed it, the results and conclusions. In the letter and attached schedules provide information that answers the following questions.

What will be the tuition expense, living expense, and total expense for each of the four years that Melanie will attend college? Provide the information for each University.

What amount will be needed in the account when Melanie starts his freshman year if he attends Ohio State?

What amount if he attends UVU? How much money will Matt and Debra have to deposit at the end of each month to allow Melanie to attend Ohio State?

How much money will have to be deposited per month to allow Melanie to attend Utah Valley University? Assume that Matt and Debra stop making deposits when Melanie starts college. The Pearson’s are concerned that given the current market performance the mutual fund will only earn 10% per year. If the return is only 10% how much will be needed in the account when Melanie starts college and how much will have to be deposited per month for Melanie to have sufficient funds to attend each school?

Answer 1

Following are the Tution and living expenses for the four years with respective increasing rate

Ohio University
Year	Tution and Book Expenses	Living Expenses	Total
1	13600	8000	21600
2	14416	8240	22656
3	15280.96	8487.2	23768.2
4	16197.8176	8741.816	24939.6
Total			92963.8

Utah University
Year	Tution and Book Expenses	Living Expenses	Total
1	7500	8000	15500
2	7950	8240	16190
3	8427	8487.2	16914.2
4	8932.62	8741.816	17674.4
Total			66278.6

Rate of given @ 10 % we can discount with this factor

Ohio
Year	Total Amount	Discount Factor@10%	Present Value of Cash flows
1	21600	0.909	19636.364
2	22656	0.826	18723.967
3	23768.2	0.751	17857.370
4	24939.6	0.683	17034.105
Total	92963.8		73251.806

Utah University
Year	Total Amount	Discount Factor@10%	Present Value of Cash flows
1	15500	0.909	14090.909
2	16190	0.826	13380.165
3	16914.2	0.751	12707.889
4	17674.4	0.683	12071.878
Total	66278.6		52250.841

We can Caluculate discount factor using formula 1/ (1+r)^n

Here r = rate of return = 10%

n = respective Year

C.

No of Periods for attaining age 18 = (18-5)*12 = 156

Interest rate = 10% p.a = 0.8333% per month

For Ohio University amount to be deposited =

By using Excel PMT function we calculate amount to be deposited for end of each month

PMT(0.0083333,156,0,-73251.806)

we can get $ 230.3877

For Utah amount to be deposited

PMT(0.0083333,156,0,-52250.841)

we can get $164.3371