COA Calculator and Loan Payment Calculator Forum

[Yardbird]

VERSION 2.0 IS NOW LIVE

Hi all,

From a request in another thread, I have made an excel spreadsheet that will properly allow you to compare COA's taking into account each school's individual tuition increase rates and COL for that area (and increases). It will automatically calculate loans based on some selections (School, Residency, School Medical Insurance, School's Book Allowance) and inputs (Scholarship and Personal Contributions). Also calculated will be your required payments under the 10 year standard payment plan.

The second tab is where you can enter your personal loan information for student loans before law school. Just follow the instructions on the tab.

The third tab will calculate your earnings after federal, state, and city taxes in the major markets (and some regional markets). It will calculate how much extra money you can put to your loans each year after factoring in your desired level of rent, COL, savings and tax info (filing status, dependency status/dependents, etc).

The fourth tab will show a ROUGH payment schedule to follow for all your loans on a monthly basis form 12/2016 until all your loans are paid off. I am not claiming complete accuracy, you should calculate this for yourself once your loans enter repayment to double check. I am assuming no payments on interest are being made while in school.

COA Calculator & Loan Payment Calculator v. 2.0

This will work for Office 2010/2013 for PC. It will not work correctly in OpenOffice (and likely Numbers). It should potentially work in Excel for Mac, but no promises. I hope this helps someone. If you like it, comment saying thanks or something so this stays on the first page to help more people.

- shadowofjazz

Changelog:
v. 2.00: Added monthly loan payment scheduler to aggressively pay off loans (there are two new tabs). The new tab "Loan Information" is where you can enter information about past loans you have. The new tab "Payment Schedule - Consolidated" is a monthly overview starting in 12/2016 of how much to pay toward each loan after taking into account income, taxes and desired COL and savings from the "Loan Payoff Calculator" tab. Also fixed an error in the "Loan Payoff Calculator" tab for CA, TX, and FL where the cities would not display properly.
v. 1.50: Fixed an error in the dependency status based calculations. Added section on COA tab to enter in COL if you know it (below the main calculator) and a question that will switch between school based projections and your actual COL).
v. 1.00: Active second sheet to calculate after-tax salaries in a bunch of states/cities and how much you extra you can put toward loans each year.
v. 0.05. Formula edits so it should work in OpenOffice now (dropdowns and hidden cells might not but the formulas will!). Still working through the tax calculations.

[dcg2120]

Awesome! Thanks, shadowofjazz!

Just a heads-up: the "Estimated Tuition" formula (D16, G16, J16) is too long for OpenOffice--it gets cut off and makes it throw an error. I just opened it in Google Sheets, instead. Also the dropbox for "School" (D6) doesn't show up in OO or Sheets, but typing in the school manually works fine.

[FKASunny]

Awesome excel skills!

However, my only concern is that while yes, it may be more comprehensive to include summer living costs in your calculations, those costs are not available to many people to be financed through Stafford and GradPlus loans. That could mean lower balances if you end up getting paid work or moving home or higher costs if those students take out private loans at higher rates.

[20141023]

.

[Yardbird]

This is some good stuff, shadowofjazz.

Just a few things that you might want to edit:

The origination fees for Direct Unsubsidized Loans and Direct PLUS loans is changing from 1.0% and 4.0% to 1.051% and 4.204%, respectively. (Link)

To shorten the length of the formulas so that people using OpenOffice can see them, you might want to use vlookup formulas instead. For example, the code that you have in cell E16 can be replaced with the following and it will pull the same data:
=VLOOKUP('Budget Calculator'!$D$6,'School Info - DO NOT TOUCH'!$A:$AH,29,FALSE)

Thanks for this and for all of your hard work on this kind of stuff.

Thanks, I'll test this later tonight!

[bombaysippin]

This is awesome, thanks!

[Yardbird]

This is some good stuff, shadowofjazz.

Just a few things that you might want to edit:

The origination fees for Direct Unsubsidized Loans and Direct PLUS loans is changing from 1.0% and 4.0% to 1.051% and 4.204%, respectively. (Link)

To shorten the length of the formulas so that people using OpenOffice can see them, you might want to use vlookup formulas instead. For example, the code that you have in cell E16 can be replaced with the following and it will pull the same data:
=VLOOKUP('Budget Calculator'!$D$6,'School Info - DO NOT TOUCH'!$A:$AH,29,FALSE)

Thanks for this and for all of your hard work on this kind of stuff.

Thanks, I'll test this later tonight!

I should have prefaced this is formatted entirely for excel (FOR PC). I don't know if the dropdown menus, the formulas, and the cells I have hidden are all relayed properly in openoffice. They definitely do not all work in Google as people have stated above. I will definitely shorten everything with the VLOOKUP formula. Had no clue that existed. Expect the next update to have shortened formulas and also the loan payoff stuff.

[Greeno]

Very cool, thanks for this. I'm surprised though, this is the first time I've seen Penn's sticker cost/COL projected higher than NYU's. Any particular reason that you can think of? I was always under the impression that NYC COL is most expensive

[Yardbird]

Very cool, thanks for this. I'm surprised though, this is the first time I've seen Penn's sticker cost/COL projected higher than NYU's. Any particular reason that you can think of? I was always under the impression that NYC COL is most expensive

Penn's base tuition is almost the same (projection wise) for each year. It's additional fees, however, are substantially higher than NYU's. COL is about the same too (slightly less). Again these are based on the schools' estimates for COL and is extrapolated to 12 months instead of 9 months. Medical insurance is also higher at UPenn than NYU (if you choose to take law school insurance).

[mesadurvadc]

Thanks for this!!! Even though it's a little depressing , I'd rather know now while I'm making decisions on schools.

[Tekrul]

Wow, thanks for this. Really nice to see the breakdown.

[Yardbird]

Check the OP for version 0.05. I made the formula edits so it should work in OpenOffice now (dropdowns and hidden cells might not but the formulas will!). Still working through the tax calculations. The formulas for THAT make the formulas for the school stuff look measly. If anyone has any suggestions I would love to hear them...

[hds2388]

Pretty awesome; can't wait until the second page is active. Thanks.

[cslouisck]

Hrm... I get an error saying "Sorry, we are unable to retrieve the document." Maybe I'm the only one?

[PM2353]

Hrm... I get an error saying "Sorry, we are unable to retrieve the document." Maybe I'm the only one?

Same here. Thanks OP, sounds like this will be a very useful tool

[Yardbird]

Hrm... I get an error saying "Sorry, we are unable to retrieve the document." Maybe I'm the only one?

Same here. Thanks OP, sounds like this will be a very useful tool

Oh oops. Forgot to set sharing. Should work now.


[Edit] Found an easy way to do taxes. It'll take me a bit to do them since I have to set things up/do my assignments for class Expect the final update by the end of next weekend.

[Rahviveh]

This is fantastic SOJ. Welcome back! Almost wish you didn't get into ED UVA so you could have been making spreadsheets all cycle lol

Please make sure to include CA and IL for taxes

[Yardbird]

This is fantastic SOJ. Welcome back! Almost wish you didn't get into ED UVA so you could have been making spreadsheets all cycle lol

Please make sure to include CA and IL for taxes

Haha, thanks! I'll be more active now that school is winding down.

Also, they're included! Don't worry. The following states will be included in v. 1.0:

AZ Phoenix
CA Los Angeles, San Francisco, San Diego
CO Denver
DC Washington DC
FL Miami Tampa
GA Atlanta
IL Chicago
MA Boston
MI Detroit
MN Minneapolis
NY New_York
PA Philadelphia
TX Dallas Houston
WA Seattle

Feel free to make requests for cities. I'll add them with demand in the following versions (v. 1.0 will only have the ones below):

[cslouisck]

This is fantastic. Thanks, SOJ.

[Yardbird]

This is fantastic. Thanks, SOJ.

Thanks! Also, call me "shadow" or "jazz." There's another TLS user who's username is "soj," who contributes a lot too.

[Aawaldrop]

Thanks this is pretty great. So for COL you just used the provided estimate from the schools then multiplied by 12/9?

PS every time I read your name I think the a is an i (the second one)

[Yardbird]

Thanks this is pretty great. So for COL you just used the provided estimate from the schools then multiplied by 12/9?

PS every time I read your name I think the a is an i (the second one)

That's correct (for most schools). Some had ridiculously high estimates (*cough* NYU *cough*) so I adjusted those down to match other area schools.

Version 1.0 actually finished itself rather quickly so you can all find a new link and file in the OP. The new version has an active second sheet to calculate after-tax salaries in a bunch of states/cities and how much you extra you can put toward loans each year.

Version 2.0 (will not be out until I am out of school - probably June) will take this to the next level by allowing for extra payment planning on a monthly basis and showing how long it will take to pay off your loans. I did the concept behind this in a different spreadsheet but its very ugly and definitely has problems in it.

[JamesDean1955]

There is very cool, bookmarked and thanks for this!

One note I have though: there is absolutely no way that Penn COA will ever come out higher than NYU's in this universe, practically speaking. I think the fact that you took everything off of Penn's website (which are substantially higher than what the typical student would practically spend) and yet revised NYU's figures downward to come into line with other area schools leaves a distorted comparison there.

Other than that, very nice

[law chihuahua]

This is highly awesome, shadowofjazz.
Appreciate all the work that must have gone into this!

[Yardbird]

There is very cool, bookmarked and thanks for this!

One note I have though: there is absolutely no way that Penn COA will ever come out higher than NYU's in this universe, practically speaking. I think the fact that you took everything off of Penn's website (which are substantially higher than what the typical student would practically spend) and yet revised NYU's figures downward to come into line with other area schools leaves a distorted comparison there.

Other than that, very nice

I adjusted NYU to Columbia's estimate. There was nothing to compare Penn to unfortunately.