Comparing College Financial Aid Offers: A Worksheet For New College Students
A couple of years ago, my goddaughter had several “financial aid award letters” in hand from colleges she is considering attending, and I made a worksheet to help her compare them.
Note: Editing this in spring 2024 as we prepare to review the next goddaughter’s financial aid situation . . . and see if my old worksheet still holds up.
I thought others might benefit from the worksheet. I link to a view-only version below. You can copy that to create your own version to adapt and to fill in with information from the colleges you are considering.
Why did we need our own worksheet? Can’t we just put the financial aid award letters side by side to understand what her options are?
It wasn’t that easy. Our main goal is to understand what gap remained after financial aid so we could discuss how to close that gap. How big a check was ultimately going to be written? But as we compared the letters and their implications, we kept getting confused.
So, I decided to organize a spreadsheet in a way that made sense to me, re-entering the numbers from the award letters and adding in variables for my goddaughter’s circumstances. As I did so, I realized we were getting confused along three dimensions.
First we got confused because of apples-to-oranges comparison challenges.
Each college described its aid in unique ways. For example, most letters lines for “grants” and “scholarships,” and each college used their different names for those. You might think of these as “need based” aid and “merit based” aid. A letter from one college might put all of this one or two lumps. Other colleges itemize each grant and scholarship making it look like you’ve won several awards. In the end, what matters to the student and their family is the sum of all the grants and scholarships that reduce the price from the full rack rate.
Second we got confused over which kinds of aid reduced which costs.
A move about 10 years ago to identifying “net cost” (after financial aid) was supposed to clarify all this for students. But we noticed that the costs included in the net cost tally differed by school. It might or might not include include books, travel expenses or other personal expenses?
Then financial aid would be applied to the cost to come up with a net cost, but that was done the same way at evert school. For example, work study is a part of financial aid, right? Seems reasonable. (Though you can’t count on maxing out the possible earnings, and your child might opt not to participate in a campus job.) But on some letters that amount is part of what is subtracted from the costs, and on other letters it wasn’t. They assume you keep earnings from work study for other personal expenses that are not included in the cost number. When you subtract the aid from the costs, the math doesn’t work out, and confusion ensues.
Similarly, the net costs varied in their assumption about what loans would be accepted by the student. Imagine two letters that both say your family will pay $10,000 in the first year after all the financial aid where one lists the maximum subsidized federal loan ($5,500 for freshman right now) and the other presumes you’ll take an extra $10,000 direct loan from the college’s lending partners. The costs are being reduced by two different approaches to student debt that are hard to intuit and compare.
Another example of confusion in the expenses section of the award letter is that they differ in how they spell out three different categories:
- the exact expenses you directly pay the college on the bill (tuition, fees and room and board);
- estimated costs you probably will incur elsewhere on campus, such as the campus bookstore, software vendors for required ebooks or online course materials; and
- estimated related expenses that you may acquire off campus (the Apple store for a new laptop, or travel to and from campus.) To some degree these are necessary educational expenses, but they differ for every student, and it’s difficult to know what the college is including in their estimate. Meanwhile, you may have other personal expenses not included here that should be accounted for. (We’ll get to that below.)
For the most part, none of this is a criticism of the colleges. Individual letters are generally clear enough individually with some close scrutiny. But they aren’t consistent with one another. Each small difference in how they present the costs and financial aid compounds the difficulty of comparing them and making an informed financial decision.
I did feel that one letter tried to mislead us and obscure how expensive the college was by foregrounding per-semester numbers for costs but foregrounding per-year numbers for financial aid.
Third, the letters aren’t interactive tools.
We needed a way to play with the variables for my goddaughter’s unique family situation and the different decisions. For example, a family might be comparing one college choice that involves commuting to campus and another choice that involves living on campus. Those will have very different personal, travel and room and board expenses. The award letter’s net cost is unlikely to be an individual family’s actual net cost. We needed a way to tally up and compare our own cost estimates, which wouldn’t be the same at each school.
Another example: When you think about it, work study and loans are financial aid but they aren’t “awards.” You have to work for that money sooner or later. So you need a way to interactively compare scenarios of accepting or not accepting those forms of financial aid.
Building the comparison worksheet
I started to lay out the information from the award letters and from our personal family circumstances in a way that made sense to me:
- all the estimated costs added up, both the college and personal expenses;
- all the grants and scholarships that reduced those costs;
- all the long-term loans that potentially reduced the short-term payments on all the costs; and
- all sources of money to cover the costs in the short term, including family savings, family contribution, a work study job, summer jobs, etc.
The result shows clearly — to me at least — the gap my goddaughter was trying to make up. And it does that side-by-side for all the colleges in play.
Importantly, it showed what needs to come out of pocket in the next year and what the total out of pocket is when loans are included.
As I built the spreadsheet, I added in projections over for years. For example, we know from experience that it’s important to have a clear eyed view of the out-of-pocket and loan costs after year one. Tuition and expenses generally go up in the sophomore year, while financial aid awards don’t. Therefore, the amount students and their families pay generally keeps increasing. I wanted to be able to see that side-by-side at each school.
I tidied up and anonymized the information from our worksheet for the public version linked below, which I’m glad to share with anyone who finds it useful. A few caveats:
- I had in mind a particular high-school senior when I created this, and she will be a full-time residential college student. I recognize that the vast majority of college students are not full-time, not residential or not between 18 and 24 years old. I think with some tinkering this will work for those students, and I know that not designing it with those students in mind isn’t very inclusive. Please keep in mind this is mostly a “save as” of something created for our particular family situation. I’ll be glad to collaborate with anyone who is interested in forking this and create something more inclusive and adaptable.
- I’m not an expert on college financial aid — just a highly motivated armchair reader. The rows I choose to include on the spreadsheet and my instructions reflect that inexpert approach.
As you’ll see, the template uses four “typical” types of four-year colleges and some basic research about what the costs and financial aid at those colleges might look like (as of 2021). If you’re more expert in this field and want to suggest better examples for the template, let me know.
I’m glad to keep updating the worksheet with additions, corrections and clarifications. And I’m glad to keep updating this page with more explanation about my approach.
Lastly, if I’ve re-invented a less effective wheel than something better that is already out there, let me know and I’ll link to it here.
I’d be glad to hear what you think in the comments here or at https://twitter.com/robertwmcguire.
