Financial projections of dental school Excel document

[Bereno]

Hi all! I have seen a few threads on here about the financial feasibility of DS, and wanted to post here. Recently, I threw together an Excel document so that I could run the numbers and see for myself what I can expect based on certain scenarios. I thought that those of you who are curious about the costs, and how they might affect your cash flows might like to use it. I added a few comments to it, and inserted a few instructions so that it is a little easier to use.

Some notes: Its quite comprehensive. Please read all of the blue boxes and try and understand what it is doing before you go and fiddle with everything. Also, this does not segregate the different types of loans for you. This is mainly aimed at helping determine if your financial cash flows can be sustained after graduating dental school (aka "to see if it is financially feasible").

If you have any questions about how to use it, please post them here so others can see your question(s) along with my answer(s).

Anyway, here it is!

---

Members don't see this ad.

 

[UltimateHombre]

First in on an awesome thread!!!

Thanks for all your hard work Bereno!! You truly are a financial executive!

But seriously this is super detailed and awesome!! How long did it take you to put this whole thing together?

 

[iralex801]

This is amazing! I wish I knew how to use excel like that lol. Thanks a lot. The only thing I see unrealistic is the model assumes you are going to make an extra $5000 (or whatever you put in) every year you practice. But there's really nothing you can do about in order to keep the model simple.

 

[MrAvicii]

Hi all! I have seen a few threads on here about the financial feasibility of DS, and wanted to post here. Recently, I threw together an Excel document so that I could run the numbers and see for myself what I can expect based on certain scenarios. I thought that those of you who are curious about the costs, and how they might affect your cash flows might like to use it. I added a few comments to it, and inserted a few instructions so that it is a little easier to use.

Some notes: Its quite comprehensive. Please read all of the blue boxes and try and understand what it is doing before you go and fiddle with everything. However, this does not segregate the different types of loans for you. This is mainly aimed at helping determine if their financial cash flows can be sustained after graduating dental school (aka "to see if it is financially feasible").

If you have any questions about how to use it, please post them here so others can see your question(s) along with my answer(s).

Anyway, here it is!

EXCELLENT JOB BERENO! This would be awesome to use to theorize! Thanks a lot buddy!

 

[amanda9717]

Wow, Bereno! Thank you so much!!

 

[amalgamgrillz]

Amazing job, Bereno!

Moderators: please sticky this!

 

[Parle G]

Thanks a ton Bereno!

 

[dentaluofm]

This is amazing! Bereno=Boss

 

[letsdoitbama]

I don't know about you guys, but I see a lotttt more green if I'm using my state school here.

 

[hellopeople]

Wow. Good job. I made something similar, but not nearly so detailed.

Absolutely epic contribution.

 

[jackalo34]

I agree. This looks like this took quite a lot of time and effort. Thanks for sharing, Bereno!

 

[Bereno]

First in on an awesome thread!!!

Thanks for all your hard work Bereno!! You truly are a financial executive!

But seriously this is super detailed and awesome!! How long did it take you to put this whole thing together?

Thanks man, i think it probably took me about 7-8 hours or so overall (I never worked on it for more than 2 hours at a time though). The first 4 were used making it work, like getting the formulas correct and whatnot. The last 3-4 were making it usable for others who don't know what I was doing. This includes making the little notes, changing the layout and stuff like that.

This is amazing! I wish I knew how to use excel like that lol. Thanks a lot. The only thing I see unrealistic is the model assumes you are going to make an extra $5000 (or whatever you put in) every year you practice. But there's really nothing you can do about in order to keep the model simple.

Yeah, I thought about making a dynamic system for that, but I decided against it. I think i even made a small note that pretty much said exactly what you said - I needed to keep it simple. Also, this was part of the reason for the "Gray zone" because your income can pretty much do anything after 10 years lol

I don't know about you guys, but I see a lotttt more green if I'm using my state school here.

HAHA! Yeah, that might be a common theme here

I'm really glad you guys like it. Like I said, I made it for myself so that i could see the overall effects of a change in either salary, payback duration, or even when I wanted to buy a house.

Once again, if you have any questions on how to use, please let me know

 

[DentalGunner]

Using numbers for Penn:
Please get the presidential scholarship...
Otherwise I must give BJs to 135 strangers for $60 each, every month, for the first 7 years...to get out of the red. Ugh. Bring it on, boys. Who's first?

 

[Bereno]

Using numbers for Penn:
Please get the presidential scholarship...
Otherwise I must give BJs to 135 strangers for $60 each, every month, for the first 7 years...to get out of the red. Ugh. Bring it on, boys. Who's first?

Damn, thats about 4.4 BJs a day - I really hope you're down for that!

 

[Bereno]

Some questions that others have had:

hey bereno thanks for the awesome spreadsheet. I have a few questions. how is residency taken into account here? I dont see how it adds to the income of year 1 and also when I put down mortgage as an amount, do I have to zero out the rent portion to make it more realistic?

Residency does not change your starting salary. You choose what you think you will start with. Residency is mostly used for calculating the addition interest accrued on your loans. For example if you have 300K coming out of DS, and you want to attend a 1 year residency, then the worksheet will calculate the interest added to your loans over that additional year. It will not change your starting salary though. If you think your starting salary will change because of your residency, then you should change your starting salary accordingly.

There is a place for you to indicate how long it will take you to buy a house. This number is also used to calculate how long you will be renting. For example, if you buy a house 4 years out from dental school, the worksheet assumes that you will be renting for those 4 years. Basically, it will include rent for the first 4 years, and then automatically switch to your mortgage payments from the 4th year on for the rest of the mortgage duration. Essentially, to answer your question: no, you don't have to zero out the rent portion - it takes care of that for you.

Thanks for the questions, I hope I have cleared them up for you

oh ok so if i have no mortgage in there, does it stop using the rent exp after year 3? thats why i saw such a huge jump in yearly income after year 3. that explains it!

Well, if you indicate that you are going to buy a house after year 3, it will stop the rent expense after year 3 even if you have no mortgage. This is because it assumes you are now living in a home, not paying rent.

Basically, if you are never going buy a house, indicate that in the gray box by selecting 40.

 

[Golfguy]

Awesome work Bereno!

If you ever get the itch to add a few things, I would suggest payroll and state taxes, as well as individual loan breakdowns (stafford vs. plus).

I would add them myself, but I'm not that smart.

 

[Bereno]

Awesome work Bereno!

If you ever get the itch to add a few things, I would suggest payroll and state taxes, as well as individual loan breakdowns (stafford vs. plus).

I would add them myself, but I'm not that smart.

I would add the opportunity to alter the loans, but that kinda takes away from the intent of the document. I want it to be used for a cash flow analysis, not loan valuation.

As far as state and payroll taxes - those are so variable that its hard to have any consistency here. Remember, I want to keep this simple. I might add a spot that can allow you to include additional taxes, but I am not sure how those would work with marginal rates. If you know how to include a marginal tax rate analysis using payroll or even state taxes, please let me know

 

[Tyrangiel]

Thanks for putting this together. It is informative if a bit sobering...

 

[Golfguy]

I would add the opportunity to alter the loans, but that kinda takes away from the intent of the document. I want it to be used for a cash flow analysis, not loan valuation.

As far as state and payroll taxes - those are so variable that its hard to have any consistency here. Remember, I want to keep this simple. I might add a spot that can allow you to include additional taxes, but I am not sure how those would work with marginal rates. If you know how to include a marginal tax rate analysis using payroll or even state taxes, please let me know

Agreed, it's not easy. The big hit will be funding social security at the full 15% (after the business loan is taken out, and we become self employed). But like you said, there are so many variables that it's impossible to take account of them all.

Thanks for doing this for the benefit of others.

 

[Dusk]

amazing!!! i'm totally going to check this out in depth after midterms.. thanks
being a finance major, i'm sure you'll do fine in the future!

 

[pupsingh]

do you think you could add an option for Income Based Repayment? maybe you could incorporate the ibr calculator into the excel somehow?

 

[Golfguy]

.

 

[Barking Lamb]

Hey, a few schools I looked into suggested deferring loan payments for as long as possible and accruing the maximum amount of capital for investment. They argued the returns would be significantly greater than the interest on our student loans. I'm no financial expert, could you give me your 0.02 cents on how that might factor into or affect your model?

As a note, my comment still wouldn't leave cash lying around post graduation for a brand new car or anything. Meaning we would still be just as poor but I assume you all get the rough idea.

 

[Bereno]

do you think you could add an option for Income Based Repayment? maybe you could incorporate the ibr calculator into the excel somehow?

lol, do you know how much work that would entail? Sorry, I can't do it. Not anytime soon anyway. It would take forever to determine all of the contributing factors that alter the reduced payment, let alone allow that to be included in the cash flows portion of the document. If you have looked into all the rules for IBR, you would see how much time this would take. Even then, I don't know of a good approximation method that would be relatively close to actual payments. I will think about it though.

You might be able to figure this cash-flow boost by artificially lowering your Misc payments though...

This document is aimed at the general population who is not going to use IBR. It is meant to be used for high level feasibility; not plan your every dime. It is used to see just how strong your cash-flows need to be to supplement your loans from dental school. Hope this helps

 

[Bereno]

Hey, a few schools I looked into suggested deferring loan payments for as long as possible and accruing the maximum amount of capital for investment. They argued the returns would be significantly greater than the interest on our student loans. I'm no financial expert, could you give me your 0.02 cents on how that might factor into or affect your model?

As a note, my comment still wouldn't leave cash lying around post graduation for a brand new car or anything. Meaning we would still be just as poor but I assume you all get the rough idea.

Are you referring to the six month grace period? I have heard of people getting early jobs, and stacking every dime away for this six months before they are required to start paying. The intent of this is to allow you to develop a small "emergency" fund. This emergency fund would be there to compensate for any large "emergency" payments that might arise. This would in no way reduce your monthly cash outflows though, which are the real killer for most. This is more of a precautionary policy than a financial one.

BTW, 0.02 cents is a lot different than $0.02. I know you know this, but I thought it was funny. lol
[YOUTUBE]http://www.youtube.com/watch?v=2Y7yqphVq54[/YOUTUBE]

 

[Demps]

Bereno,

You are the man

 

[MrAvicii]

do you think you could add an option for Income Based Repayment? maybe you could incorporate the ibr calculator into the excel somehow?

You can visit the following site for IBR repayments. You dont need a whole calc for it. This will suffice http://studentaid.ed.gov/PORTALSWebApp/students/english/IBRCalc.jsp

 

[AlbinoPolarBear]

Awesome job Bereno.

 

[Barking Lamb]

Are you referring to the six month grace period?

BTW, 0.02 cents is a lot different than $0.02. I know you know this, but I thought it was funny. lol

Yeah, it was sarcasm. I thought it would play off my phrase "I'm no financial expert." That stuff doesn't translate very well over the internet.

-------------------------

No, I am not referring to the grace period. I mean making the minimum payments on your loans and not paying them back for (insert huge number of years).

I had always been of the mindset that I would move in with my parents or my uncle after dental school and pay off the student loans in two years while living a sorry sorry life.

However, Nova for example recommended that students make minimum payments on their loans. They argue the interest on the debt doesn't matter when compared with the returns on a 401k, mutual funds, stocks and bonds portfolio.

I don't know, like I said I'm not well read on this material yet and I don't need to worry for a few years. But, I saw your undergrad is in the financial area and wanted your opinion.

 

[Bereno]

Yeah, it was sarcasm. I thought it would play off my phrase "I'm no financial expert." That stuff doesn't translate very well over the internet.

-------------------------

No, I am not referring to the grace period. I mean making the minimum payments on your loans and not paying them back for (insert huge number of years).

I had always been of the mindset that I would move in with my parents or my uncle after dental school and pay off the student loans in two years while living a sorry sorry life.

However, Nova for example recommended that students make minimum payments on their loans. They argue the interest on the debt doesn't matter when compared with the returns on a 401k, mutual funds, stocks and bonds portfolio.

I don't know, like I said I'm not well read on this material yet and I don't need to worry for a few years. But, I saw your undergrad is in the financial area and wanted your opinion.

Lol, got it.

Don't listen to Nova. Simple as that. If you are sitting on 400K+ of debt and only make the minimum payments, you will be FAR worse than you would be otherwise. This still holds even if your investments have a higher rate of return than the interest rates on your loans. Essentially, paying the minimum only defers your payment, which costs you money. I don't want to go through all of the numbers right now, but I can run a valuation test sometime later, and post on here to give you a concrete example.

Moral of the story: Pay it off as soon as you can (while still being comfortable)

 

[MrAvicii]

However, Nova for example recommended that students make minimum payments on their loans. They argue the interest on the debt doesn't matter when compared with the returns on a 401k, mutual funds, stocks and bonds portfolio.

.

thats probably the dumbest advice ive ever heard LOL the compounding interest will eat away at any "investments" you can make. In addition, this market is so horrible right now that you will end up "double dipping" by losing some money on your investments and paying off significantly higher loans later on.

Be smarter and take a schools advice with a grain of salt. They aren't the ones that are paying off the loans.

 

[Bereno]

Bump for version 2.0 of the excel document! It now has the option to include a flat rate auxiliary tax. This can be in the form of either a state tax, self employment tax, whatever.

Please note: This tax is a post deduction tax, so it is only a flat rate of what you earned after deductions. If you need a pre-deduction tax... well... sorry haha

 

[FLOSS UR TEETH]

Hey Bereno! Your the bomb! I really appreciate you sharing this with us buddy.

 

[Bereno]

Hey Bereno! Your the bomb! I really appreciate you sharing this with us buddy.

Yeah, of course! I like being able to help my fellow peers

 

[SexyMariGal]

This deserves a bump, guess I'm going into the military, lol.

 

[metaverse]

This is amazing! Thanks, Bereno!

 

[MrGoOrange]

Thanks Bereno! This is an amazing spreadsheet!

 

[timwatley]

Finally got around to plugging my numbers in, this thing rocks! thanks

 

[woolloo]

You're real a savior, Bereno! I like the sophistication and elegance of this spreadsheet

 

[Cello]

Just now saw and had the opportunity to use this spreadsheet, great work Bereno! If this forum had rep I'd +rep you.

 

[dantemac]

Thank god for the military.

 

[white fang]

Very good product. After viewing the sheet, I realized I would be happy to pay you for this spreadsheet. If I were you I would take down the link to the sheet and charge people $9.95 for the download. Your time is worth more than $10 for this wonderful spread sheet.


Signed,

Guy who is willing to set up the paypal and take half the profits in exchange

 

[white fang]

...sorry double post....

 

[SexyMariGal]

Very good product. After viewing the sheet, I realized I would be happy to pay you for this spreadsheet. If I were you I would take down the link to the sheet and charge people $9.95 for the download. Your time is worth more than $10 for this wonderful spread sheet.


Signed,

Guy who is willing to set up the paypal and take half the profits in exchange

Very good product. After viewing the sheet, I realized I would be happy to pay you for this spreadsheet. If I were you I would take down the link to the sheet and charge people $9.95 for the download. Your time is more than worth it for this wonderful spread sheet.

Wow, really? You should be put on probation too like that Wired guy you kept arguing with. Stop being an ass.

 

[DJPaulyDDS]

LOL not a bad idea. cut down on those loans anyway you can.

 

[white fang]

Wow, really? You should be put on probation too like that Wired guy you kept arguing with. Stop being an ass.

I don't understand, do you not value the time and effort that Bereno spent?

To me it is worth at least 10 bucks, I don't know how cheap you are but it seems ridiculous...

 

[tinman831]

Back on topic please. Personal attacks will not be permitted.

 

[Bereno]

Very good product. After viewing the sheet, I realized I would be happy to pay you for this spreadsheet. If I were you I would take down the link to the sheet and charge people $9.95 for the download. Your time is worth more than $10 for this wonderful spread sheet.


Signed,

Guy who is willing to set up the paypal and take half the profits in exchange

Lol, no it will stay free. I am glad you appreciate it!

Wow, really? You should be put on probation too like that Wired guy you kept arguing with. Stop being an ass.

Don't be hard on the guy. Even if he was being sarcastic, I would still give him the benefit of the doubt.

 

[white fang]

Lol, no it will stay free. I am glad you appreciate it!




Don't be hard on the guy. Even if he was being sarcastic, I would still give him the benefit of the doubt.

FYI I was not being sarcastic,, it really is worth being sold. Though I was joking about setting up the paypal and taking some profit.

Not sure why it was such a bad post, I meant nothing but compliments to you.

 

[Bereno]

FYI I was not being sarcastic,, it really is worth being sold. Though I was joking about setting up the paypal and taking some profit.

Not sure why it was such a bad post, I meant nothing but compliments to you.

I figured you were being honest, hence why I said thanks! So thanks again!! I'm not too sure why SexyMariGal jumped on it though... Maybe the paypal thing? haha