Create a Shopping Bill in Excel for Real Estate Effortlessly

Shopping bill in excel for Real Estate

mike here i hope you're having a great day and i hope you're ready to explore this free downloadable excel file i've put together for you to track your rental income and expenses very easily i have a lot of clients who are either real estate investors currently or people wanting to become real estate investors and one of the most common questions i get is how do i account for my rental activity if i don't have quickbooks if i don't have wave accounting software you know can you do it in excel and the answer is yes we're about to make your accounting for your rental activity a lot easier okay you crazy kids that want to be real estate investors let's go over this in this video i really want to focus more on the accounting side of things and a little bit less on the tax side of things now i have made a video similar to this in the past but i've just updated the spreadsheet i want to go over with you how to use this properly the way to use this and the way i what i would do with this is i would save this template and i'm going to have a link to this a dropbox link in the description section of this video and i think i'm also going to put this on my website as well so that the original template does not get distorted by by anybody or changed by anyone the way to use this is to save the original and then make a copy of the original once you've saved it and then use it for your purposes so i want you to always have a copy of the original because for each property for each separate rental property you have i would recommend having this spreadsheet so for example so for your first rental property where it says in the top left corner property address let's just call this property one and this it doesn't matter if it's commercial it doesn't matter if it's residential whatever it may be it could be land it could be but for each separate property that you're renting out have one of these spreadsheets and the records separate does that make sense and then for each year you fill this out from january through december for whatever task you're looking at so right now obviously we're looking at tax year 2022 because we're in 2020 too at the end of every year what i would like you to do or what i should suggest doing is copy this and make a version for 2023 that way you always have your accounting data for 2022 in previous years completely set and it won't that history will not get deleted so each year just make a new one of these and copy it for each for each property and for each new year i really hope you're enjoying this content and this information if you are you can do me a huge favor and tap that like button it really helps spread the reach of this video and if you guys really want to help me grow my channel i'd really appreciate it just share this information with others especially if you know somebody who wants to do real estate investing who has rental properties it could help them really organize their information and save money on tax return preparation or just better understand how accounting works for their rentals thank you so much okay let me show you how to start off with this and how the spreadsheet works you may be inclined to override these numbers with what your rental income numbers are please do not do that the reason why is you can see if you click on these dollar amounts see how there's formulas here that means it's linked the spreadsheet has linked formulas and so if you manually type in over this it's going to override the formula and what's going to happen is the spreadsheet is no longer going to work as it's intended to work you can fix it but if you're not savvy with excel please do not overwrite the formulas because it's just going to create headaches for you now so if you want to insert your rental income the way to do that is actually if you look down below here see these different tabs so this first tab is really the summary page that summarizes all the rental income and all the expenses for your rental the next tab over and when i say tab it's really structured like accounts so your rental income account your advertising expense account it's all down here cleaning the maintenance account so every time you want to add an expense to one of these categories make sure you come here and click on the right tab and add the expense in that section and then the spreadsheet is going to populate the rest and i'll show you show you how so for your rental income for example here's rental income you can see this is where you can manually type in how much rent you receive for that property and i would encourage you to put a date so let's just pretend you received a thousand dollars of rent in january 31st of 2022 drop a date in there and then you can add you know the rental income for each month as you receive it now mike what happens if i have more than one rental check in a month for the property well it's very easy to fix so don't worry guys we got you covered all you need to do especially if you're not excel savvy is right click see this so let's say we have another rent income check in january right click this row or the next row down right click and hit insert you're going to insert a row you're going to answer it one more row and now you can type in once again so that let's just say you got at the end of the month you also got a second check for 500 you can see because there's formulas down here this column now totals to fifteen hundred dollars if you double click double left click here you're going to see that formula in place once again please do not override the formulas because you'll get yourself in trouble and then if we go back to our summary page now you can see now the rent in january now reflects fifteen hundred dollars and so that's why by having these accounts separate in separate tabs it's a lot easier to customize how you're receiving the income and you can see everything monthly which is why it's important in my opinion to follow the inputs this way and so let's go on to the next tab so but now if you didn't want this tab here or this row i'm sorry row you all you would just do is right click and hit delete and voila that's as simple as that let's see how this next one works so now we have advertising advertising same concept if you paid somebody to advertise your property in may of 2022 you would just put the date of may 2022 you would put advertising or you know whatever it's for or whoever you paid and if it was in may you would just put let's say you spent 100 in may to do that so you just put 100 here now if we go back to our summary page under advertising we can see oh wait look at that it's not showing up why is that because it's not appropriately linked and this is a perfect example of why this spreadsheet's not perfect so but let me show you how to fix that on screen so a lot of people ask me how do you link things in excel it's really simple it really is all you do and all and when it comes to learning excel and that's not what this video is about you can google all of this and you can look on youtube you can find your answers but if you want to link this row where it says advertising expense hit the equal sign okay i'm hitting equal i'm going to go to my advertising tab and right here i'm going to link the the total column down below here right here and hit enter and so now this cell is linked to my advertising tab so i'm just going to drag this over just like this i'm going to drag it across all the way through december and i'm gonna let go and now it's picking up that hundred dollars in may from advertising which is right here so that's how you can link a cell super easy to do but for those who aren't sell excel savvy it's okay as you can see there's none of this is rocket science you guys can totally do this and so i'm kind of glad and like i did not plan that air for this video it just it was it was there and i just caught it right now so yeah that's how this can work but essentially and i'll show you how to create another row two here essentially clean you know the same works for cleaning and maintenance the insurance is linked so all these expenses are linked which i think you guys get the idea now so what happens if you want to link an expense that is not currently listed here on the left-hand side of the spreadsheet you're like what if what if there's like pest control that you pay well then let me let me pretend this row is not there at all so so what do we do how do we how do we add pest control very simply so we're gonna wherever we wanna insert pest control we're gonna select this row right here right click and hit insert you're going to insert a row and let's just call this pest control now is there a work tab down here called pest control right now well we can scroll through here and the answer is no there's there's not a tab so how do we get it there well very simply all you need to do is just copy one of these other tabs one of the if it's an expense copy the expense tab if it's an income account copy an income tap but all you need to do is copy let's say we want pest control probably to be right around here right right after mortgage interest so it's in line with this so i would right click on mortgage interest once you're in here right click on on this select move or copy and what you want to do is create a copy of the mortgage interest tab okay yeah you can copy any tab you want you really can but just go ahead and copy the mortgage interest tab you're going to see why so create a copy and hit ok now you have two mortgage interest tabs right here so what i do is i left click on this i drag this over and now i'm going to rename this to pest control pest control so i type that in there and then up top i'm going to type in pest control up here now there's still an issue though what is it the thing is it's still not linked to the front of the spreadsheet so we still need to link pest control so how do we do that well we've created the row for pest control select the first column right here see how there's no dashes right here which basically indicates there's no data here we're going to hit the equal sign on our keyboard hit equals go to the pest control column or the pest control tab i mean that we've just created select this and select the first row in january and hit once it's selected hit enter on your keyboard now it's linked and we're just going to drag we're going to go we're going to make sure we have the right one selected yep it says pest control and we're going to drag it all the way across now all the way across and this is how you drag formulas in excel so if notice how there's a blank row here are in this column there it's not catching this data so all we need to do is just drag drag this down we just need to if you click on this cell and drag this down it'll pick up the proper rows so let's test it let's make sure we are seeing how this works go so go to pest control and let's say you had pest control expenses in mar in uh april let's say in april 2022 the pest control company came out and they charge you 100 bucks i charge you 100 there and for um spraying bugs or or whatever whatever they write on that bill of yours they basically uh poke as one pest control guy told me he goes around to people's houses and he pokes their house with a stick that's how he described his job as somebody who works in the pest control business i thought that was pretty funny to be honest he says that's the simplest way i can describe what i do i'm like huh sounds like a pretty cool job so now since we since we did the legwork we inserted those um we've linked those formulas the hundred dollars comes through here and a hundred dollars now shows here as well because that's the total amount of health of uh health pest control that we've spent so far this year and if we have more expenses it will add these up as it goes along so that's how you create new expense tabs and income tabs the income tab would be the same way you would insert a row here and then you would create a separate income tab for that line item now i don't have pet rent on here i mean i don't have like a work tab for pet rent but like so i'm just going to take this out at the moment but you can you can add a tab for other rental income if you want to it's not linked at least not this one is but just feel free to like i said keep a copy of the original spreadsheet and then modify it from there as what i would recommend and a lot of these expenses the way the presentation of how i've organized this pretty closely follows the tax return of schedule e if that's how this is organized so if you're looking back at your schedule tax return it's gonna be a lot easier to fill it out a lot now if if there's not a primary line item on the tax form for a certain kind of expense that you see here then you could group this with another expense or you would list it out as another expense on your tax return okay let's talk about limitations so on the right hand side i'm i mentioned so before changing save as a separate copy of the original template which we talked about at the start of this video so the one of the biggest limitations here is this spreadsheet only takes into account the profit and loss activity of the rental please remember to account for the balance sheet activity separately including the basis of the property this is where most people get hung up now when you just have a rental that you own personally and you just report it on your personal income tax return this is primarily just all you need is just you're going to report your income and expenses however when you go about forming an llc and you put the property in an llc a limited liability company and it's a separate tax return an llc sometimes a completely separate business return that needs to be filed there's a lot more requirements in terms of filing requirements and accounting requirements with that type of vanity what do i mean well so you not only do you want to track the profit and loss activity as we're seeing here but you want to track the balance sheet activity which is not reflected here and that's where most people miss the big picture when it comes to accounting and because most people are just they're not accountants right that's it's fine it's totally fair you know i do this for a living so i i see this all the time and so what i try to do is help my clients that i work with learn how to not only more simply account for their accounting activity but also understand that there's a balance sheet portion to this i always recommend when you have rentals or any kind of a business really because a a rental it once you have more rentals it's kind of like a trade our business depending upon how you treat it i would suggest personally that you get a separate business checking account for your rental properties that way what it does is all the income you receive from rent and all the expenses you pay out for advertising lawn care maybe maybe the mortgage is paid out of there this and that and the other all that's coming out of that account okay that business checking account for that rental activity and one of the main reasons for doing that is that if you aren't good or disciplined at keeping up to date with your accounting records at least you can go back to those bank statements because it's all consolidated there all and you're only using that checking account the business checking account for your rental then those transactions are should just be whatever you spent on the rental got it you didn't take your business checking account card and go buy meals for you personally or you didn't go buy groceries for yourself personally or something else you just kept it all separate which is what you should what was what was what i really recommend for all my clients and that keeps the accounting activity at a minimum it keeps the headaches at a minimum and helps you create this you know recreate the data you need to properly account for your records and to file your taxes moving forward so start with a business checking account and get that in place if you don't know all the benefits of having a business checking account and there's a lot of free no fee accounts out there that you can utilize then please check out this video next because it's really going to help you get the right structure for your business or rental properties in this case and you're going to learn all the primary advantages of having a separate account there's a lot now with a business checking account if you have your rental in an llc what do you have well you have an asset that cash account is an asset and so at the end of the year you want to know what your bank balance is because you're going to have to report that on your llc's tax return is the the total now there there is a exception if there's not a lot of assets where you don't have to report the balance sheet but i usually just recommend it anyways because really um if you have all of the data you might as well just keep it up to date so you know exactly how much in assets you have exactly like your liabilities would be your mortgage interest rate what's your mortgage interest or what your i mean your total mortgage liability is what i'm trying to refer to your total debt on the property total equity on the property things like that and then what did you what else would the balance sheet show so it would show your assets liabilities but it also would show one of the assets would be what did you pay for the property if you paid let's say 400 000 for the property well part of it's you paid for the structure of that unit and part of it's for the land so by looking at the property tax statements you can kind of see you know what portion would be of that cost basis the 300 000 would be considered the building or the or the dwelling as i would say the dwelling and the land so maybe 300 000 of that 400 000 is for the value of the dwelling and a hundred thousand is for the value of the land and that's your so your total rental property asset is 400 grand right because that's what you paid for that property and so you want to think about things like that and then and when you put if you put money into your business checking account to start it up that is known as a contribution so it's not income it's not a it's a deposit but it's a contribution and it's not considered income if you take money out of your business checking account to pay yourself some and keep some of that rental income for yourself what is that that's known as a draw or a distribution and so these are the type of things that are happening on this the balance sheet side that you won't see here on the profit and loss side so just remember that what else is there there's depreciation right so i'm not going to go into the depreciation in this video but just so you know you can only depreciate the dwelling not the land so if the dwelling if we value that as 300 000 then we're going to depreciate just the 300 000 portion of that the land cannot be depreciated because the irs says that land has an infinite life for tax purposes but we can depreciate the 300 000 amount over 27 and a half years so we can still get a a monthly expense deduction for depreciation every single month and are for for 27 and a half years until the property's fully depreciated for tax purposes and so that's that's the piece that's not on here and if you guys if i get enough um engagement in the comment section or enough likes i might consider doing the balance sheet side of things so you can see the full picture because this is really just half the picture but it will hopefully help you understand that yes there's a profit and loss side and there's a balance sheet side to your accounting records and to have complete accounting records we have to have both right you have to have both in place especially if you want to have an llc and your have your accounting records done right you want to keep your balance sheet up to date because all of it is important and you want to be able to look back and easily determine oh this is exactly how much i bought the property for oh this is exactly how much i owe on the property so it's just helping get these all these things in line now and this is the kind of things i help my clients with when so as you guys know i'm a cpa or if you don't know i have my own accounting practice and i work with a lot of people over youtube and just around the united states in general i reside here in california where i operate my business but i work with a lot of real estate investors a lot of people getting into real estate and so i i help people in this area i help people with their accounting records with their taxes with consulting matters and things like that so feel free to said it's not downl but make make a copy of the original is what i would suggest and then you can tweak it to fit your needs or fit you know whatever type of rental property you have you might have an airbnb rental that's fine you can totally use it for that but the last thing i want to point out is that there's also quarterly totals here so you see how there's a row called quarterly total so you can see how much money you're making from your gross rents every single quarter well except april may there's a there's a reason it's april may because this is following estimated tax payments so as you know or maybe you don't know but if you're making money from your rental well that's additional taxable income right well how do you pay taxes on that well you're gonna be the irs has a pay as you go system which means as you make money the irs actually wants you to pay in some tax as you go and estimate payments may be due now this is not true for everybody so speak with your accountant but based on how much net rental income you have you might have to pay in some extra taxes that are not being captured within the withholdings from your paycheck right because you have your income from your job but maybe it's not enough to to cover the taxes you might eventually own your rental from what you're making on your on your net rents if you're having a positive rental income i believe i have it so that you can print it and it should be within the proper specifications let me see yeah so if you i have the print area of this worksheet so that it's just covering this area which means yeah so you're just going to get the important data here it's not going to pick this up or anything below it the other thing i didn't mention here is like there's you can also track your miles down below this if you want to if you're going to your rental property checking on it you know if you're do especially if you're doing property management things like that so like i said this is not all comprehensive but it's hopefully a good start to get you in the right direction and i'll tell you what your accountant will love you if you summarize your data like this and it might even save you money when it comes time to file your taxes because if your accountant is having to do all this leg work for you they have to charge you more to do this for you it's a lot of time it's a lot of record keeping the more record keeping you can do upfront the better you're going to understand your your business of rental properties and the better you're going to understand where you're at financially and just the cost savings and benefits of doing this are tremendous so i would encourage you to do it and take advantage of it so it's like i said it's free to use enjoy guys thank you so much i will see you down in the comment section let me know what you use to track your rental activity do you use quickbooks do you like using excel do you use some other program let me know what works for you i'd love to hear your questions and thoughts subscribe to the channel so you do not miss our future videos around business investing taxes accounting and more and check out this video next because if you like this one you're probably gonna like these as well you