ZekeMD

15+ Year Member
Oct 11, 2003
838
5
Status
Fellow [Any Field]
Since I've seen people start to post regarding debt load and repayment, I threw together this spreadsheet. It shows many repayment scenarios for different debt amounts and salaries.

Loan Payment Estimations
 

exmike

NOR * CAL
10+ Year Member
15+ Year Member
May 19, 2003
4,206
11
42
Bay Area
Status
Fellow [Any Field]
About the Ads

jantyme

Junior Member
7+ Year Member
15+ Year Member
Oct 8, 2003
17
0
40
california
Visit site
Status
Thanks for working it all out for us to give us an idea. :clap: It's something to think about since very few of us will leave medical school without any debt. :(
 

bmischen

word!
7+ Year Member
15+ Year Member
Jun 6, 2003
51
0
41
on bended knee
Visit site
Status
does your recalc sheet take into account compound interest or is the interest all lumped together on a yearly basis? I may have missed it, but what interest rate are you assuming? 5%?
Thank you for all of your work!
 

juddson

3K Member
7+ Year Member
15+ Year Member
Aug 21, 2002
4,049
3
Visit site
Status
very nice, but I think your assumed tax rate is a bit optimistic even at the lowest income level.

You have to take into account federal taxes, state taxes, local taxes, social security taxes, medicare taxes, employment insurance taxes.

I was making $100,000 a year right before I stopped practicing law. My after tax takehome was about $5,000. That's an effective combined tax rate of something like ((5000)/(100,000/12) = .60) 40%. You can expect to keep about 60% of your before tax income if you are making around $100,000. That number can be expected to go down to around 55% as salaries increase to 300,000. These are old numbers, and do not reflect changes to the tax code in the last three years - but even then I think a combined rate of 40% is probably more accurate than your 31% rate.

I hope I am wrong (PLEASE, somebody tell me I am wrong - somebody familiar with the new rate tables). But this will lower your estimates somewhat.

Nice piece of work, though.

Judd
 

juddson

3K Member
7+ Year Member
15+ Year Member
Aug 21, 2002
4,049
3
Visit site
Status
yea, also, I can't make out your interest rate. You seem to be calculating the "interest paid" amount as merely the "total payments" minus $100,000. I'm not sure I understand what you are doing.

Judd
 

ZekeMD

15+ Year Member
Oct 11, 2003
838
5
Status
Fellow [Any Field]
Sorry, I forgot to post the rate I used. I assumed an interest rate of 7%. Not too high, but not too low.

Bmischen, I believe I calculated it as if the interest was being compounded monthly...I may have done it compounded daily (I honestly don't remember and I'd have to go thorough the numbers again to find out).

Juddson, I didn't look at any tax tables to figure out income. I figured to take home around 69% (based on my personal experience), but there are many many factors that go into this. I realize that it may be off somewhat, but it's merely to give an estimate.
 

ZekeMD

15+ Year Member
Oct 11, 2003
838
5
Status
Fellow [Any Field]
juddson said:
yea, also, I can't make out your interest rate. You seem to be calculating the "interest paid" amount as merely the "total payments" minus $100,000. I'm not sure I understand what you are doing.

Judd
Oh yeah, that was an error on my part. I forgot to change the value from 100,000 from tab to tab. Reload the page and it's fixed.
 

Adapt

2K Member
7+ Year Member
15+ Year Member
Mar 18, 2003
2,048
6
Visit site
Status
ZekeMD said:
Juddson, I didn't look at any tax tables to figure out income. I figured to take home around 69% (based on my personal experience), but there are many many factors that go into this. I realize that it may be off somewhat, but it's merely to give an estimate.
I'm from CA and was particularly interested in this. I looked at both the state tax, the federal tax, and social security tax.

Under Bush's tax cut which was enacted for this year, a single person making 120K gross a year would come out with about 75K net. Under Clinton's plan before Bush's tax break, that same single person making 120K would make 73K net a year. That's a 2K difference or an extra month's loan payment.

For a married, person you can expect to make about 81K net from a 120K salary, but that's assuming you file jointly with your spouse so the 120K factors in both salaries of you and your spouse. In this day and age, your spouse will also work so really that 120K gross would increase.
 
About the Ads

Fumoffu

Senior Member
10+ Year Member
15+ Year Member
Mar 25, 2004
194
0
Visit site
Status
You used take home income of 69%?

Hmmm isn't it closer to 60% if you're a single male? For NY state I mean.
 

juddson

3K Member
7+ Year Member
15+ Year Member
Aug 21, 2002
4,049
3
Visit site
Status
That was my point. But as the previous poster said, the numbers change depending on state, marital status and number of dependants.

I think 69% is too high. On reflection, I think 60% might be a tad low. i think 63% is probably a good estimate with the current tax situation.

Judd
 

juddson

3K Member
7+ Year Member
15+ Year Member
Aug 21, 2002
4,049
3
Visit site
Status
Hey Zeek, I would make the following changes (which I have done to my copy but don't know how to link to this board).

1. On the $100,000 page (you only need to do one page), put .07 in cell A1.
2. Put 100000 in cell A2.
3. In cell H2, put in the following formula:

=((($A$1/12)+(($A$1/12)/(((1+($A$1/12))^(H1*12))-1)))*$A$2)

4. Copy this forumala to all the cells in the second row (that is, so it calculates the monthy payment for each number of payback years)
5. In cell H4, the following formula: =(H3-$A$2)
6. Copy the formula in H4 across all the cells in Row 4.

Now you can erase all the pages except one. Now the user can simply plug in whatever loan interest rate he wants into cell A1 and any total loan amount into A2. This data will then spread throughout the spreadsheet.

I already did this to one of my sheets, and it seems to work OK. But i have no idea how to post it to this forum.

Judd
 

CanIMakeIt

It will get better once you are...still waiting :)
15+ Year Member
Oct 8, 2003
1,494
18
Status
Attending Physician
What you take home also depends on the exemptions you claim on W4
 

ZekeMD

15+ Year Member
Oct 11, 2003
838
5
Status
Fellow [Any Field]
All set Judd. I changed it so the user can set their own debt and interest rate. I also changed the take home salary to 63%.
 

Krazed_Medic

Registered Banned User
7+ Year Member
15+ Year Member
May 5, 2001
446
0
43
Oklahoma
Visit site
Status
Pre-Medical
dude, that's cool! I couldn't get the geocites website to work, but the excel sheet worked fine for me. You my friend, are getting karmaized!
 

CanIMakeIt

It will get better once you are...still waiting :)
15+ Year Member
Oct 8, 2003
1,494
18
Status
Attending Physician
ZekeMD said:
All set Judd. I changed it so the user can set their own debt and interest rate. I also changed the take home salary to 63%.

Hey Zeke good work man ...... i have modified mine to customize the interest rate too ......

:clap: :clap:
 
About the Ads