Create Your Billing Statement in Excel for Banking Effortlessly

Billing statement in excel for Banking

good afternoon everyone thank you for joining us for today's training on how to structure bank statement loans we've already covered this earlier on another uh training but this is a very important uh segment of non-qm lending so I want to make sure to those of you that have um seen our training on bank statements before we revisit it and those of you that have not are aware of all the opportunities uh in bank statement loans so let's get right into it bank statement loans are a loan which uses the borrower business personal or comingled which means combining both bank statements to calculate the monthly income we can use 12 or 24 months worth of bank statements uh bank statement loans are for self-employed borrowers only please do be aware that um we have uh different options you have U Bank saving loans where we can also combine other income types that the borrower or co-borrower may have like W2 income profit and loss asset utilization or 1099 so who are the eligible borrowers and trans transactions for a bank statement loan now again I mentioned self-employed borrowers with minimum two years in business however we do have exceptions for this um the borrower can be one year in business for example if they were previously employed performing the same job duties as a W2 employee we're talking examples like an electrician that is licensed could have been W2 and now he's 1099 or just totally self-employed we could have a doctor or we could have a nurse those are just three common examples of individuals that we could approve them having only been one year in business as long as they were previously performing the same job duties as a W2 employee uh now obviously eligible borrowers for these transactions are US citizens permanent resident aliens nonpermanent resident aliens with a work of authorization document or an itin borrower please note that uh foreign National borrowers are not eligible uh as uh to do a bank statement loans because foreign National borrowers do not derive their income if they're actually being qualified as a foreign National borrower cannot derive their income from the US uh the maximum LTV on any bank statement loan is going to be 90% LTB that's going to be for a purchase on a primary uh Maximum cash out may be as high as 85% LTV but most of the options I'm seeing right now cap you at 80 rate and term refine definitely Max 85% LTV we have delayed financing transactions that's where borrower uh buys a property cash and within the first six months wants to do refinance to uh recuperate the whatever money they put out on the transaction that's called delayed financing and then we also have second mortgage options through our hel loone option which is a fixed rate second mortgage that will go up to a Max 85% CLTV when it's a primary uh owner occupied transaction for a Hilo so what about program options right be do be aware that all non-qm programs have guideline variations that are specific to that option right so you cannot generalize and say for a bank statement loan can you do such and such thing because it's really going to depend on what the guidelines state so most uh you now you can use like I mentioned business bank statements or personal bank statements or comingled if you're using business bank statements uh you're going to start with at least a 50% expense Factor now again do be aware like you see there on the photo fixed expense ratio that's actually from the guidelines from one of our conduits where if for example you have a business that it's a service related business with no uh employees other than yourself the expense factor for that uh transaction would only be 20% so you would be able to capture 80% of the deposits if that same business had won to five employees the expense Factor increases to 40% which means you would be able to capture 60% of the deposits so the expense Factor uh can be um um modified with a CPA letter so if for example you do have those one to five employees but the accountant can document in the letter that your expense factor is actually 30 or 25% then we can use a higher percentage of the deposits that's incre that's uh to increase the profit with a CPA letter and like I mentioned some program options have the expense Factor table like what I you showed you and that's what you have to consider when you're structuring the deal that's why it's so important to ask the customer to complete the business narrative which is the document that they answer that states the structure of their business how many employees do they have a location do they pay rent all that kind of stuff that's going to help determine their overhead now when you use personal bank statements we are allowed to use 100% of the business business related deposits but keep in mind that the only way to use 100% of the business related deposits when using personal bank statements is when we also have business bank statements uh ing to the guidelines they're going to need anywhere from two to three months worth of business bank statements to document the expenses that are covered by the business if the borrower provides personal bank statements and does not have any business bank statements then you will need to apply whatever is the correct expense factor for that business to the bank statement deposits normally we don't include an expense Factor when using personal bank statements but if the borrower does not have business bank statements then that will be the case you have to apply the expense Factor because there's no business bank statements when using personal bank statements now you can combine business and personal that's called comingled for like different months however please note that if you are using comingled bank statements where one month you use the business bank statements another month you use the personal bank statement for whatever reason depending on how the deposits were received then you're going to have to apply the same expense factor that you would to the business bank statements to the personal bank statements so going up to our table there if you have a product related business with zero employees and we're submitting to the investor where I obtain this table your expense Factor would be 40% and you would be able to use 60% of the deposit so very important that we all understand uh when an expense factor is to be applied especially when using personal bank statements so what are some of the potential issues why that we could have in a bank statement loan and what could be some of the solutions right well the potent the most of the solutions the solution to most of these issues is I'll just tell you ahead of time is going to be a profit and loss loan however most profit and loss loans do require uh two to three months bank statements to be produced to document the income um now how much of the income you got to document depends on the guidelines right some of our one of our guidelines State you got a document G at least 75% of the income stated on the p&l with a current bank statement so what are some of the issues that we have for bank statement loans well some of the issues we could have is that the borrower cannot document the needed business history right they they self file so they don't have an accountant readily available to provide the third- party verification letter maybe they're in a business where they do not have a separate business license maybe they're a sole proprietor so no corporate docs or articles of uh incorporation or articles of formation are available so I mean those would definitely be uh some issues uh no if you cannot document the need of business history for a bank statement loan you're probably not going to be able to document the needed um business history for a p&l loan so that is first and foremost when trying to document that your borrower is self-employed be on the lookout for the potential obstacles there so you can see what you would need to do to be able to provide documentation that the borrower is actually self-employeed another potential issue with bank statement loans is declining deposit Trends right you're seeing the monthly deposit that's why we don't like to go more than 12 months back on a bank statement loan because you expose the borrower to additional fluctuations of the deposit Trends where we could end up with declining deposit Trends uh then we have large deposits that cannot be documented that's the perfect one to go pnl on uh that's one of our I mean favorite reasons to go p&l on a bank statement loan is when the deposit Trend you have large deposits that cannot be documented we had a deal that went p&l because of that the C some we only had like five deposits in the whole year each deposit was like for over $250 to $300,000 when we asked the bar could he document those deposits where they came from um he preferred not to go that route stated it would be very difficult and we ended up closing the deal as a profit and loss because we identified the issue up front beware on large deposits the beware that guidelines require all the deposits greater than 50% of the monthly income to be documented so if you calculate your monthly income at $14,000 for your borrower that means any and all deposits of $7,000 or above need to get documented right copy of the item deposited copy of an invoice or a contract or something to document a large deposit uh irregular deposit Trends is another issue for bank statements right right some months you got deposits and then other times it may go a couple of months without any deposits we would need to know why is that part of the business cycle of the borrower why you know would they not have deposits on any given month so could be a perfectly understandable reason but we just need to know needs to be explained and possibly provide documentation now nsfs and overdrafts is another area of concern now we do have one option that does not necessarily judge the borrower based on nsfs or overdrafts but will require solid explanations and to prove that it's no longer an NSF or overdraft but usually in most of the cases more than three nsfs in a 12-month period will disqualify the deal and um another issue to be concerned of is uh for the underwriter to increase the expense ratio due to the business type so if you did not review that table showing the business type and the expense factor and then you provide the business narrative and the underwriter says right he has two employees and it's a service related business I need to to make a hit and I'll go back a minute to that slide so if we did have a service related business with two employees we'd be getting a 40% expense Factor so we could only use 60% of the deposits so it's something to be aware of that the underwriters will get Proactive or the underwriter reviews the tax the the bank statements and realizes there's a lot more expenses on there uh that would appear then that the expense factor is higher than 20% and they may actually do their calculations and update the expense Factor due to the um income and expenses that's running through the bank statement so as previously stated the profit and loss loan is the solution to irregular deposits High expenses and and any of these other issues that we have mentioned here so actually calculating the income is um can always be an adventure right but I will let you all know please use the investor bank statement service whenever possible most of these uh investors are now making it a requirement to have the bank statements reviewed and the income calculated by them internally before submitting the file because you have to submit the file with a copy of the spreadsheet that you were provided most of them will guarantee that in income so if that's a given and that's your income now you know what you're going to deal with which should make the structuring of the loan a lot easier now please note that um you know we do have like a generic Excel spreadsheet that you can use uh for U documenting the bank statement income but please try to use if you're not going to have their the investor service complete the form um please make sure that um you do use the one that that uh that we have internally to put in all your deposits and I I will share that in the following slide but basically what you need to do you're going to input the total of the deposits in the First Column and then you're going to back out one at a time the non business income deposits so if your total deposits for the month were1 ,000 but then you had four deposits of $250 each that were credit card returns you'll put minus $250 in the next column then the next one minus 250 and minus 250 and minus 250 so that the system then will have a total of of $1,000 being deducted from the total deposits and then you will get the amount of total deposits in the end so let me just show it show that to you a minute and then we'll go back to the sheet so you're seeing right here this is month one this is the date of the bank statement this column here is the total of the deposits in that account and then beginning on this line we're backing out deposits that were not applicable right so in this particular scenario here I'm backed out one of $870 and one of $34 and3 and 58 so you would do that for all the statements for all the months and in the end you'll have a figure over here all the way to the right which is going to be the net deposits for the month so let me go back to calculating so we're going to use the Excel spreadsheet required for the investor for the option Chosen and if not using this if not using their scenario Des bank statement review option so I guess what I meant to say there is if you're going to load them up on a spreadsheet try to see first if the investor where you're sending the loan to if you if you if for whatever reason you're not using their service or if you sent it to their service but you want to document it on your own as well to compare then make sure that you see if they have their own branded spreadsheet most of them do that's branded to them with whatever adjustments they want to do and then use that form if not you can use our form that we have um so you're going to input total deposits and back out non business business uh uh income deposits what are we referring to that these would be these nonbusiness income deposits uh basically we're talking about things like transfers from other accounts of theirs that are not business income like they just decided to transfer money into the account to cover some expenses uh credits from credit card companies for example you know Credit Credit item and unverified fiable large deposit so a large deposit any deposit that's 50% or greater than the monthly income and if any of these large deposits are not verified satisfactorily then um we'll just have to back it out from the amount uh be aware of the expense Factor ratio restrictions and the guidelines for the option chosen I already covered that a couple of times and very importantly make sure you compensate for the borrower proportion share of the of the business so let's say the expense factor is already 5050 for this for this deal but they on top of that are only 25% owner in the company so once you get the 50% off the 50% amount uh which is you know the amount you're going to use right you're giving them 50% of the uh deposits as income and then they are 25% owner of the business you're going going to get that gross amount that you got which is 50% of the gross deposits of the acceptable deposits after any addings backed up and then you're going to multiply it times 20% Which 25% excuse me which is their proportionate share of the business to um get to the monthly income for that borrow I'm using one right now where I gotta multiply it times 51% and but we're still at like $25,000 a month in income which is pretty good so you're seeing how the calculations work here the most important part I guess is you know review the guidelines for the option that you're choosing to make sure that you're following their guidelines uh regarding you know expense Factor uh co-mingled bank statements because not you know I say it's possible to use comingled bank statements but not every not all of our conduits allow comingled bank statement which means you could have a September October November from the from the from the personal then no uh December January from the business and then uh February moving on forward from the personal that would be co-mingled uh scenario where you have business bank statements and you have personal bank statements so that concludes our training on this I'm going to leave leave the spreadsheet up there want to see if we have any questions I see a question that asks do we offer HELOC un investment properties yes we do offer helocs on investment properties the LTV obviously is lower on an investment property than on a primary we also offer he loans on an investment property now um it's not as flexible in all of the income streams when we're going um to the uh investment property on a HELOC right uh that's HELOC it's pretty much going to be full dock only on those there are some alt dooc options that are now coming out but do review the guidelines on those because they're very restrictive so do we have any questions on uh bank statement loan structure the most popular nqm origination loan origination type is bank statement I'll give it another minute see if we get any uh additional questions looks like we are good then with the questions on structuring your bank statement loan thank you for attending and I will see all of you tomorrow