Brian Canes sent me a question via Twitter, and I couldn’t quite answer it in 140 characters, so I decided to do a blog post about it since I thought it illustrated some important points. His question was:

“How do you calculate a % funded for a 529 account given an account balance and projected college tuition assuming no future contributions?”

In essence, Brian is asking “Am I done saving for college?” It is entirely possible that he is, but in order to decide, we’ll have to make a couple of key assumptions and do a little math.

In order to make this exercise a little more real, let’s use my own personal situation and numbers.

**Whitney wants to go to Brigham Young University**

WCI columnist Whitney is in 8th grade but has been talking about attending BYU already since she was very small. The 2017-2018 cost of attendance at BYU is $18,430. That’s a ridiculously inflated number for my daughter given tuition is only $5,460, and she’s been well taught in how to be a cheapskate, but that’s what we’ll use. In order to keep things easy, we’ll assume Whitney graduates in just four years, goes straight through, and that during school her 529 will have returns equal to cost of attendance inflation. She just started 8th grade, so she has five years left until she will need $18,430 * 4 = $73,720. We’ll also assume that the entire cost is paid for out of Whitney’s 529, which is most definitely NOT true.

So how much is in her 529? On the day I wrote this (back in August), there was $34,298.82.

Now we’ll need to make two key assumptions. First, we’ll need to make an assumption about investment returns for the next five years, and then we’ll need to make an assumption about the rate of cost of attendance inflation over the next five years. I’m going to use 8% and 5%. If you don’t like those numbers, use your own, but I think they’re reasonable for our situation (aggressive 529 investing and a reasonable rate of tuition increases).

**Calculating Whitney’s 529 plan % funded**

Now we’ll plug those numbers into a financial calculator, or my preference, the Excel Future Value function.

Without additional contributions, over the next five years, at 8%, her 529 will grow to

=FV(8%,5,0,-34298.82) = $50,396.22

Meanwhile, the cost of attendance will grow to:

=FV(5%,5,0,-73720) = $94,087.48

So, Whitney’s 529 is $50,396.22/$94,087.48 * 100 = 53.6% funded.

**Maren wants to go to the University of Utah**

Maren is 2 1/2 years younger than Whitney, about the same height, and in the 5th grade. They have been intense rivals from the time Whitney took scissors to Maren’s arm during her infancy. When Maren found out Whitney wanted to go to BYU, she instantly decided she would spend her undergraduate years at the rival institution, the University of Utah. Let’s repeat the exercise for her using the same assumptions.

University of Utah 2017-2018 Cost of Attendance: $23,444

Maren’s 529 Account Balance: $34,318.63

**Calculating Maren’s 529 plan % funded**

Without additional contributions, over the next 5 years, at 8%, her 529 will grow to:

=FV(8%,8,0,-34318.63) = $63,521.39

Meanwhile, the cost of attendance will grow from $23,444*4 = $93,776 to:

=FV(5%,8,0,-93776) = $138,549.86

So, Maren’s 529 is $50,396.22/$94,087.48 * 100 = 45.8% funded. Perhaps surprising, given her younger age and equal 529 balance. Picking a more expensive school makes a big difference.

**Whitney wants to go to medical school**

Whitney has also been talking about going to medical school. If we look at the total burden of her education for both undergraduate (again at BYU) and medical school (at the University of Utah), what percentage of her education is already funded?

Again, we’ll make some simplifying assumptions. We’ll assume Whitney goes straight through and that any invested 529 money keeps up with cost of attendance inflation from the time she starts undergraduate until the time she finishes med school.

The University of Utah School of Medicine Cost of Attendance for 2017-2018 is around $56,000 per year. Again, Whitney has $34,298.82 in the 529.

C**alculating Whitney’s 529 plan % funded to include medical school**

Without additional contributions, over the next five years, at 8%, her 529 will grow to

=FV(8%,5,0,-34298.82) = $50,396.22

Meanwhile, over the next five years, the total cost of her education ($73,720 undergraduate + $224,000 medical = $297,720) will grow to:

=FV(5%,8,0,-297720) = $439,868.

Thus, her total educational cost is currently 11% funded.

Now, have I simplified things a lot? Absolutely. I only wanted big round numbers anyway, and the truth of the matter is my simplifications pale in comparison to the assumptions. If you want the most accurate numbers possible, be sure to repeat the exercise each year with updated numbers and assumptions.

**Let’s use more realistic assumptions like scholarships, work, and additional 529 contributions**

You can make it a lot more complex if you want to be super realistic. For instance, I have no intention of paying for all of Whitney’s undergraduate education, much less her graduate education. I expect her to work and get scholarships to help pay for undergraduate, and take out a few loans to pay for medical school. I also expect to make some more contributions to her 529.

Let’s say I invest another $4K per year for five years into that 529. Let’s also assume she gets a half tuition scholarship and keeps it for four years. And earns $8000 per year by working during the summers and the school year. And lives cheaply and so cuts another $2K of the cost of attendance.

**How much should be left in her 529 after completing her undergraduate?**

First, let’s calculate what’s in there when she starts undergraduate.

=FV(8%,5,-4000,-34298.82,1) = $75,739.94

Now, let’s decrease the cost of attendance by $8,000 per year for her work, $2,000 per year for her frugal living, and $3,000 per year for her scholarship, or $13,000 per year. That adds up to $52,000. $94,087.48 – $52,000 = $42,087.48

$75,739.94 – $42,087.48 = $23,652

That will probably cover the first semester’s tuition for medical school. Now I could also add in amounts that I intend to cash flow and additional amounts I may contribute to the 529 above and beyond the tax-deductible $4K per year I’ve been putting in there and maybe we could get her into her 2nd or even 3rd year of medical school before she has to start taking out loans. But if my intent were to completely pay for eight years of education, you can see that I would need to be putting a ton of money into that 529 for each of the next five years. And these schools are cheap compared to most.

Well, this post ran 1000 words. No wonder I couldn’t fit it into a tweet.

Share this Post