Quick & Easy Underwriting with My Calculator: Part 3 Guide
Oct 10, 2024
Welcome back to our series on how to underwrite an assisted living business! In Parts 1 and 2, we covered the basics of our underwriting calculator. Now, let's dive into the exciting part: looking into the future of your potential investment.
Check out the video too:
Why Future Projections Matter
When you're thinking about buying an assisted living facility, you need to know if it will make money not just now but in the years to come. That's where the "Projected Financials" tab in our calculator comes in handy. It helps you see what might happen to your investment down the road.
Using the Projected Financials Tab
Let's break down how to use this powerful tool:
Color Coding: Your Guide to Editing
First things first: look at the colors!
- Red cells have formulas you shouldn't change.
- Green cells are where you can put in your own numbers.
This color system helps you avoid messing up the calculator by accident. If you do make a mistake, just hit Ctrl + Z to undo it.
Key Numbers to Put In
- Money Sources: Enter how much money you're borrowing from the bank, how much you're putting in yourself, and if the seller is helping with financing.
- Costs and Empty Beds: Put in how much you think things will cost to run (like staff pay and food) and how many beds might be empty.
- Down Payment: Change this to see how it affects how much money you need to invest.
- Partners' Money: If you're working with partners, you can enter how much money each type of partner (general or limited) is putting in.
Cool Features to Check Out
- Tax Stuff: The calculator helps you figure out how much you might save on taxes.
- Income Guesses: Enter info about your beds and rates, and the calculator will show you how much money you might make each month and year.
- Loan Calculators: There are tools to help you figure out your monthly payments for different types of loans.
Planning for the Unexpected
One super important thing the calculator helps with is planning for surprises. It's a good idea to set aside some extra money (about two months' worth) just in case things don't go as planned.
Real-Life Example
Let's say you're looking at buying a 50-bed assisted living facility for $2 million. You plan to put down 20% ($400,000) and borrow the rest.
Using the calculator, you might find:
- Your monthly loan payment would be about $10,000
- If all beds are full, you could make $200,000 per month
- Your yearly expenses might be around $1.8 million
- After all expenses and loan payments, you might make a profit of $400,000 per year
But what if only 45 beds are usually full? The calculator would show your profit dropping to maybe $300,000. This helps you see how changes can affect your investment.
Wrapping It Up
Our underwriting calculator is a powerful tool for figuring out if assisted living is profitable. It helps you look into the future and make smart decisions about investing in this business.
Remember, good underwriting is key to finding great deals in assisted living. It helps you spot opportunities and avoid risky investments.
Ready to take the next step in your assisted living investment journey? We've got two great resources for you:
1. Download our free Assisted Living Business Plan Checklist. It'll help create a solid business plan as you start your investing journey.
2. Need help figuring out where to start? Join the next Roadmap Challenge and build your launch plan with me.
With these tools and our underwriting calculator, you'll be well-equipped to make smart investment decisions in the assisted living industry.
Happy investing!
Show full transcript 👇
Transcript
00:00:01
[Music] hey guys welcome back to assisted living investing uh welcome to part three of the tutorial on the on the calculator this is taking a little bit longer than i thought it would but hopefully hopefully it's valuable to you hopefully you're finding information uh and i'm learning a little bit about how to use the calculator how to look at some of these different metrics how to look at different numbers and and hopefully this is valuable to you make sure you like uh the videos uh ring that bell uh so that
00:00:38
i can get you more information more content comment down below so i know what content is helpful to you and what i can do to help you on your journey uh in assisted living investing we're going to jump into into the video this time and we'll go through the other tabs on on that calculator on the sheet so i'm just going to go ahead and we'll get that ready for you uh let me move around a few things so last time we talked about um the past financials and we talked about the expenses and and how those work so
00:01:12
this time we're going to get into the projected financials on the calculator and and how this part works so you can see here i've got a few different colors on here typically red means you're not going to make any adjustments on it and green main means that you are able to kind of start making adjustments on there but you also want to make sure that you're just being aware some of these are going to have formulas in them and the way that you can see if it has formula is up here in this
00:01:39
function um bar right there you can see that one says it's referencing different cells whereas this one right here that says 100 is not and so it's a whole number so that's kind of just another quick tip on on how to use a spreadsheet some of those are going to be functions um so just make sure that you're not overwriting them or if you're overriding them that you are aware that you're overriding a function and and where it might have been referenced a quick thing on how to if
00:02:09
you if you went in and you accidentally typed 50 on this one formula there you can click this back button undo or you can use the short key there which is the control button plus the z button z is in zebra um if you do that then it will undo the thing that you just did so you don't have to remember what the formula was just a quick um hopefully handy tip there for you working in excel here or working in google sheets so i'm going to walk through these things really quick i tried to make them
00:02:38
as self-explanatory as possible um and have kind of outlined them with bolder lines and different areas so that they're kind of you can kind of see what sections they are and get a feel for for how they work but this is money that is going directly to the seller um so this is the lender amount this is the amount of money that the lender is putting into it this is the amount of money that you as the borrower are putting into the deal so this is cash going to the seller the origination fee for an sba loan or something like that
00:03:12
this is any extra working capital that you need in the loan this is the sba guarantee fee sometimes you've got to pay those fees or all kinds of fees when you're working with lenders this is title and closing costs and then this would be the total amount that's going to the seller at closing this is the amount that's going to them from the lender this is the amount that's going to them from you again these are just numbers i think this was an example of one i was looking at in utah um but i've also kind of
00:03:41
mixed some numbers in here together so this is not like a legit 100 deal um that you're looking at here just examples but you can fill fill those in this initial home value is actually not going to be the purchase price of the facility this is like what it would appraise for um this is going to help you on things like uh taxes and also uh looking at the amount of equity that you have in the house and things like that so the the initial home value is not going to be the same as the purchase price in every case it could be but it
00:04:11
is not it does not have to be the same there these are appreciation rates so um like the appreciation rate of the real estate what that's going to look like um this is they can see rate for the facility uh operating expense increase so you're just you know the way that knocks work you're going to have some appreciation you're going to have um in everything um so you're going to probably have to spend more on food you're going to have to have higher payroll those types of things so we want
00:04:42
to take that into account as we're projecting these things out into the yearly proforma tab which we'll get into next um to see you know your your expenses are not going to stay static they're going to fluctuate but they are generally going to increase we want to account for that this is the buyer injection so this is how much money you're gonna put into it uh generally this is let's see i've got this um pulling in from down here below but these numbers are actually gonna feed
00:05:09
over here what that down payment is like and so if you need to get this down payment to 15 15 20 whatever that down payment needs to be 30 you're going to adjust these numbers right here to get that down payment amount over here and then that information is going to populate up here to tell you exactly how much money you're putting into the deal so from here this is where the actual purchase price is going to go this is any seller injection so if the seller is doing if there's any seller
00:05:42
financing so that's where the seller says yeah i want to sell this to you i'm willing to kick in a hundred thousand dollars of money so you don't have to give that all to the bank um some sellers do this some some don't want to do it so it just kind of depends on who the seller is but if you are able to get seller financing this is where you're going to be able to track that in there um from there we have this is the the gp injection you'll remember we went over some different uh definitions so the gp
00:06:11
is the general partner this is how much um the general partnership um the joint venture the sole proprietorship this is how much money that group or individual is going to be putting into the deal and then from here you've got the lp purchase injection the limited partners these are the people that are really just the money partners and what they're going to be putting into the deal so i have a way to kind of track those things and play around with that so you can see what that down payment's going to look
00:06:37
like how much money you're going to have to bring to the table and then how much money is going to the seller from the lender as well so you can kind of get that that information in there if you're going to end up having a renovation you can list that in here this is also another one that you want to do and i i always try to build in now that i've purchased a couple of these a contingency for operating capital and you can build that into you can see right here that's built into the the
00:07:08
loan from the lender so that money is not actually going to go that money is going to be separate it's not going to necessarily go over to the seller that money is going to actually come into your bank account but it's going to be on your loan um so this is my way of kind of factoring that in for what that loan amount is going to be um so it just kind of gives you an idea but having about two months worth of operating expenses is something that's going to be very helpful you certainly
00:07:35
want to have at least one month this is all built off of these expenses and the expense tab and then it's looking at these monthly amounts and then it's taking the sum of that that total and multiplying it by two in this case so if you wanted to adjust that and have it be more than two months you would just click in that cell and change that to three or one and a half or whatever number you want that to be but building in a contingency unless you have funds available for it is going to be key and
00:08:06
going to help you be able to function in your facility and move forward with it and then this is going to be any holding costs so if it's if this is an opportunity where you are going to be purchasing something you've got to do a big renovation on it or something like that you're going to have to hold it what is that going to look like do you need a bridge loan or or whatever that's going to look like this is where i'm tracking those holding costs let's see we'll get down here into hard money so
00:08:30
these are different ways of kind of funding things so typically i'm looking at using a traditional lender where i've got a mortgage essentially a mortgage payment but you may choose to go the route of using a hard money lender and so i'm building this in so that you can can put in any hard money that's coming in so hard money lender is like a private person or or group sometimes even banks will do hard money but it's just a group and they have different they have a lot of the same
00:08:59
types of criteria that they need for borrowing but they're able to collateralize different things on the deal to help you get the money that you need or if you have bad credit um hard money lenders are are also a possibility to look at to get funding on a purchase there they want to be in first position on on anything um so you likely aren't going to be able to use hard money and a traditional lender and very few instances will be able to find a traditional lender that is okay not being in first position and most
00:09:30
times hard money lenders also want to be in first position on a mortgage so using the two of them is is going to be somewhat difficult though not impossible i'm just going to take a lot of conversations and working with with different people to get it there your rental income rate your rental income increase rate so we talked up here about operating expenses going up your rental income is also likely going to go up um it's just it will also just kind of increase over time um the state will adjust rates for medicaid
00:10:01
to to make it a more marketable rate and things like that so those rates will also go up they're not always going to be the exact same but generally they're going to be hopefully about the same amount of increases as your expenses are those expenses will also be passed along to your residence uh this is the average shareholder equity so i'm gonna jump right here um to the gp count so this would you know if you're doing a joint venture there's two or three of you you would put this number in there
00:10:30
so if i put three in there for example it's going to adjust this one down to the gp average shareholder equity down to 33 percent um at two it's going to be at a hundred percent um so it gets that there if you've got uh limited partners in here it's going to look at that gp factor so in this case it's 100 this is a joint venture um so that's what it's set at but let's say the you're going to end up doing a syndication and 30 of that money is going here so now it's going to adjust
00:11:02
that you've got two general partners um they're going to get 30 percent of of the cash flow in the deal um and that's divided amongst them so they essentially are going to get 15 and then the lps are going to get the remaining 70 and then there's an lp breakdown tab over here that's going to kind of help identify exactly how much money is going into them for this for purposes here we're just going to keep this at 100 with two partners it's just a joint venture um that's how you're going to
00:11:31
set this up but if you wanted to get into the part where you're you're bringing unlimited partners and things like that you can mess around with some of those numbers and see what types of returns are are going to to come into effect there if you have the effective tax rate um you can add that in here uh depreciation rate um for how the property is going to depreciate the real estate uh also the depreciation period so typically real estate is going to depreciate at 27 and a half years it might go faster depending on if you
00:12:00
use different strategies like the cost segregation or or different things like that you might be able to adjust some of those things so talk to your accountant on that would be my advice to make sure you're understanding how you're going to depreciate things and at what rate um things will be depreciated at but it's built into the calculator so you can look at that and then this would be closing costs sell rate so this also the calculator when we get into the early pro forma is going to
00:12:27
give you an overview of what it might look like if you're looking to sell it in year 5 year 10 or whatever but you're going to have to pay closing costs and so i've built in a factor there to see what the the closing costs sell rate would be um when you go and sell below that uh we've got the the and these ones are things that you don't need to make adjustments on these are all going to be formulas down here so this is your monthly rental rate so this one is based off of a number over
00:12:57
here so this is resident information and really these ones these cells right here are populating over on the yearly pro forma tab right there so that's kind of what they're doing it gives you an idea of what that down payment is the total injection you're doing anticipated down payment amount your anticipated closing costs it just kind of does a breakdown here it's just really an overview uh lets you look at what the occupancy is once occupied and things like that over here we've got a mortgage
00:13:27
calculator uh so on the back end i've got mortgage info this tab and then this tab is then populating uh like what that monthly payment is actually going to look like so you can get your your own uh interest rate uh your own terms so how long it's going to be how many uh payments you're going to make in a year if it's 12 it's 24 however you want to work that out and then that's going to adjust these numbers right here and give you what your loan total loan amount is and that
00:13:59
should match this number right here and then from there you can figure out what your annual payment is going to be and then from there what that monthly mortgage rate is essentially and you can as we discussed earlier see what your down payment percentage rate is is going to be right there so very very handy down here something similar this one is is for hard money and this pulls from this hard money info but the exact same type of uh of number that goes into that and it is actually fed by if we were to put in a hundred thousand
00:14:32
dollars over here for example it's going to should at least populate um that information over here yeah you can see so it popped up loan amount 100 000 and monthly payment right there and then if i hit control z like i mentioned um it took that back down to zero and it adjusts that so that's how you can back out of those so mortgage calculator hard money calculator those are kind of built in there and then this is going to be the resident information so this is where you're going to put in
00:15:02
that inflation rate so how much your rent is going to increase on an annual basis this is your bed count this would be resident count so in my mind i actually am quite conservative on how i do this so i've got 55 beds i'm saying that i've got a 10 vacancy but i'm also factoring in like how many how many of those 55 beds i think are actually going to be occupied so i've got 55 beds i'm going to have 50 of them occupied and then of those 50 i'm gonna have a 10 um vacancy rate so i'm really kind of
00:15:36
factoring in when i do my numbers like a 20 vacancy rate here you can do whatever you want but that's kind of how this is this is where you put in if you know private pay uh daily rates medicaid daily rates and if we don't really deal with medicare in uh in assisted living but if you happen to have a medicare resident that medicare is paying for i don't think they actually pay for assisted living but they might for memory care or if you're using this to do like skilled nursing or
00:16:04
or something different where you're you know a hospice or something where medicare does pay you're going to be able to enter that information in here and then this is going to let you say the vacancy or the occupancy of those types of residents so you know i've got 50 beds here they're all medicaid so it's going to take these 50 residents and multiply it by this daily rate uh whereas if i did um if i say that this is at 50 and this this one's at 50 it's gonna take and take 50 percent of
00:16:37
these 50 residents so 25 of the residents get this rate um 25 of the residents get this rate um and that's kind of how that one's built so you can adjust those things and there uh and these ones again are things that you don't make adjustments to but this is overall occupancy rate um and then you've got the um vacancy rate here um i have those in there separately because i think i've got formulas bouncing off of both of them is just easier and it just makes it a little more clear um to do that these are how
00:17:09
many beds are in each of those and so i you know if you notice when i threw in here the 50 beds it's going to address adjust those um those numbers down there um so you can see what those look like um and then this is going to give you what that yearly rate is going to be for all those medicaid beds and and for all of those private pay beds etc and then this is going to give you the total um of all those so it's a sum of of those three cells right there um and lets you look at those and then this
00:17:40
total average monthly better rate is uh that total annual rate divided by 12 so you know there's 12 months in a year so that's how we get that one and then this one is getting us the average monthly bed rate so we're taking this average monthly rate and we're dividing it by the total resident account so um you could give bed rates monthly bed rates for your private pay or medicaid by doing some different calculations this is just like your average this is taking everything into account on
00:18:15
average your bed right here is 2500 a month this is your total yearly bed count so this one is looking at these 50 beds at 90 occupancy um and and multiplying that by 12 because that's how many um you know how many uh months there are in a year and then this average monthly bed cost is taking that total yearly bed count and just dividing it by 12 so we get a monthly account and then this one is actually looking at the let's see let's look at this total uh yearly bed count and it's dividing it by
00:18:55
how many beds there are and so it's basically saying that 10.8 is saying that you're going to over the course of a year you're going to have a an entire month essentially where your facility is completely vacant but you're a hundred percent vacant the other 11 months it's kind of it's just another way to look at it um it's just kind of something that in my brain makes sense but it might not for you but for me it does and then this um is the vacancy bed rate so you can
00:19:26
set this as you could do this as a percentage or you could say you know i want to set aside 300 a month per bed um to make sure that um we're putting money away in the event that we have vacancies um just so that we we have that in there we went over a hard money calculator and then this part of the of the calculator down here is just going through projected closing costs so this all is going to end up filling up here the title and closing costs um up there and i've kind of got it broken down um a lot
00:19:56
of this is kind of based off of purchase price and and different things like that but it's a breakout of what those closing costs are are going to look like so that is the the bulk of of the calculator there's a lot this is the brains of everything um i i know i hopefully didn't get too deep holy i didn't lose you too much on this one um but yeah there's a lot of information in there it really just kind of puts everything together and hopefully gives you a good understanding of what it looks like and
00:20:27
how it works and from here our next video we're going to get into the yearly pro forma we're going to finish out the the tutorial we're going to finish up what we've been doing so far and hopefully all these videos combined are going to give you a really good understanding of exactly how the calculator works and how you can move forward with it and use it to analyze deals for yourself so make sure that you like the video make sure you ring that bell so you get notified every time i put up a video so
00:20:55
you get new content uh we're putting out videos two times a week also daily videos on tick tock making sure you get some good quick tips there uh and join the conversation on instagram facebook twitter uh i'd love to hear from you i love i love working with people and helping people better understand exactly what's going on and so please just make sure you reach out make sure you comment in the video let me know if this has been helpful for you uh and i hope to see you next time we'll
00:21:21
finish out the tutorial on the calculator and uh yeah i'm excited to to hear from you and see where things go uh thanks for watching and have a great day [Music]
Download Your FREE Calculator
Stay connected with news and updates!
Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.
We hate SPAM. We will never sell your information, for any reason.