COA Calculator and Loan Payment Calculator

(Applications Advice, Letters of Recommendation . . . )
User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

COA Calculator and Loan Payment Calculator

Postby Yardbird » Sun Apr 21, 2013 2:34 pm

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.
Last edited by Yardbird on Tue Sep 24, 2013 8:59 pm, edited 17 times in total.

User avatar
dcg2120
Posts: 175
Joined: Mon Dec 10, 2012 1:24 pm

Re: COA Calculator and Loan Payoff Calculator

Postby dcg2120 » Sun Apr 21, 2013 3:16 pm

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.

User avatar
FKASunny
Posts: 3904
Joined: Mon Feb 04, 2013 1:40 am

Re: COA Calculator and Loan Payoff Calculator

Postby FKASunny » Sun Apr 21, 2013 3:27 pm

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
Posts: 3072
Joined: Mon Jun 18, 2012 12:17 am

Re: COA Calculator and Loan Payoff Calculator

Postby 20141023 » Sun Apr 21, 2013 3:41 pm

.
Last edited by 20141023 on Mon Feb 16, 2015 12:27 am, edited 1 time in total.

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Sun Apr 21, 2013 3:43 pm

kappycaft1 wrote: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. :D
Thanks, I'll test this later tonight!

User avatar
bombaysippin
Posts: 1977
Joined: Fri Mar 22, 2013 3:11 pm

Re: COA Calculator and Loan Payoff Calculator

Postby bombaysippin » Sun Apr 21, 2013 3:48 pm

This is awesome, thanks!

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Sun Apr 21, 2013 7:28 pm

shadowofjazz wrote:
kappycaft1 wrote: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. :D
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.

User avatar
Greeno
Posts: 50
Joined: Mon Jun 11, 2012 10:26 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Greeno » Sun Apr 21, 2013 8:29 pm

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

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Sun Apr 21, 2013 8:51 pm

Greeno wrote: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
Posts: 24
Joined: Sat Jan 26, 2013 6:15 pm

Re: COA Calculator and Loan Payoff Calculator

Postby mesadurvadc » Sun Apr 21, 2013 9:03 pm

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

User avatar
Tekrul
Posts: 493
Joined: Wed Apr 10, 2013 8:17 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Tekrul » Sun Apr 21, 2013 9:16 pm

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

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Sun Apr 21, 2013 9:22 pm

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
Posts: 206
Joined: Tue Aug 04, 2009 4:07 pm

Re: COA Calculator and Loan Payoff Calculator

Postby hds2388 » Sun Apr 21, 2013 9:27 pm

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

cslouisck
Posts: 134
Joined: Sun Oct 14, 2012 11:59 am

Re: COA Calculator and Loan Payoff Calculator

Postby cslouisck » Sun Apr 21, 2013 9:57 pm

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

PM2353
Posts: 85
Joined: Wed Jan 09, 2013 12:33 pm

Re: COA Calculator and Loan Payoff Calculator

Postby PM2353 » Sun Apr 21, 2013 10:04 pm

cslouisck wrote: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

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Sun Apr 21, 2013 10:13 pm

PM2353 wrote:
cslouisck wrote: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 :evil: Expect the final update by the end of next weekend.

User avatar
Rahviveh
Posts: 2271
Joined: Mon Aug 06, 2012 12:02 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Rahviveh » Sun Apr 21, 2013 11:19 pm

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 :wink:

Please make sure to include CA and IL for taxes :lol:

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Sun Apr 21, 2013 11:39 pm

ChampagnePapi wrote: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 :wink:

Please make sure to include CA and IL for taxes :lol:
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
Posts: 134
Joined: Sun Oct 14, 2012 11:59 am

Re: COA Calculator and Loan Payoff Calculator

Postby cslouisck » Mon Apr 22, 2013 12:37 am

This is fantastic. Thanks, SOJ.

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Mon Apr 22, 2013 12:45 am

cslouisck wrote: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.

User avatar
Aawaldrop
Posts: 251
Joined: Thu Nov 01, 2012 11:28 am

Re: COA Calculator and Loan Payoff Calculator

Postby Aawaldrop » Mon Apr 22, 2013 2:23 am

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)

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payoff Calculator

Postby Yardbird » Mon Apr 22, 2013 2:36 am

Aawaldrop wrote: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.

User avatar
JamesDean1955
Posts: 744
Joined: Sun Jul 29, 2012 4:06 pm

Re: COA Calculator and Loan Payment Calculator

Postby JamesDean1955 » Mon Apr 22, 2013 10:13 am

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 8)

User avatar
law chihuahua
Posts: 83
Joined: Thu Jul 01, 2010 11:18 am

Re: COA Calculator and Loan Payment Calculator

Postby law chihuahua » Mon Apr 22, 2013 10:23 am

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

User avatar
Yardbird
Posts: 1093
Joined: Sun Dec 04, 2011 1:45 pm

Re: COA Calculator and Loan Payment Calculator

Postby Yardbird » Mon Apr 22, 2013 10:23 am

JamesDean1955 wrote: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 8)
I adjusted NYU to Columbia's estimate. There was nothing to compare Penn to unfortunately.




Return to “Law School Admissions Forum”

Who is online

Users browsing this forum: Bing [Bot] and 3 guests