Loan Repayment Estimates

[ZekeMD]

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

---

Members don't see this ad.

 

[Celestron2000]

Hey, that's very sweet... but your link doesn't seem to be working.

 

[exmike]

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

http://www.geocities.com/spermanator/loans.xls
So you're the "spermanator" eh?

 

[ZekeMD]

http://www.geocities.com/spermanator/loans.xls
So you're the "spermanator" eh?

Yeah yeah yeah It's from my yahoo account when I was a teen. I thought I was SO clever.

Anyhow, the link should work now. Dumb Geocities!

 

[Celestron2000]

Hey Thanks! That's awesome!

You're definately getting Karma from me.......... tomorrow (I'm all tapped out.)

 

[jantyme]

Thanks for working it all out for us to give us an idea. It's something to think about since very few of us will leave medical school without any debt.

 

[bmischen]

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]

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

 

[Celestron2000]

well, maybe, since we can deduct our loan interest... it won't be so bad.

 

[juddson]

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]

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]

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]

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.

 

[Fumoffu]

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]

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]

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]

What you take home also depends on the exemptions you claim on W4

 

[ZekeMD]

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]

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]

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 ......

 

[ZekeMD]

Bumping so everyone sees

 

[LaurieB]

Thanks Zeke! This is awesome!!

 

[donnyfire]

Great work. Thank's very much.