Collaborate on Monthly Rent Receipt Template for Finance with Ease Using airSlate SignNow

Monthly rent receipt template for Finance

hey everyone I'm going to show you how to use Google Sheets to create a rent tracking spreadsheet for rental properties I've built in some useful features like an automated rent receipt generator and annual report that will make your life easier as a landlord being a landlord myself I created this spreadsheet out of necessity hopefully you can use this video to make your own but if you're not familiar with Google Sheets or spreadsheets in general you can also buy it from my Etsy store the link will be down in the description as you can see there are multiple sheets at the bottom the main sheets that provide all the core functionality are rent collection tracker annual rent report and receipt generators let's start with the rent collection tracker this is where you would input the rent you've collected from all your different tenants there are four Fields you can input here the date property unit tenant rent paid and notes let's input an entry starting with the date if you double click the field it will open a calendar then you can select a date the property unit tenant is a drop- down list so I'll just pick one then input a rent amount and any note that you want to record I'll show you how I created these dropdowns later in the video a couple more things uh I created two filtered views which help me quickly sort the data if I select data change view I can select either of these options and it will sort the data ingly so let's Select Property unit as you can see all the data is sorted by property then by unit in ascending order to exit the filtered view just click on this x button or go to the menu and select data change View and click on exit view now let's look at the annual rent report this is where you can see the rent collected for each property month by month or for the whole year you can also see the monthly totals and annual totals there are only three inputs needed the year starting month and property the output for the rest of the sheet is all automated by formulas if functions and query functions I'll show you later in the video how these work the starting month can be any month of the year so if I select March you can see that the next 11 months will take me till February of the following year also instead of selecting a single property you can also select all properties and this will show the monthly and annual totals for all your properties now let's look at their rent receipt generator there are two sheets one for a monthly receipt the other one is for an annual receipt at the top you can input the landlord name address and phone number after that this receipt generator is pretty much all automated the only two inputs you need are the property unit tenant and the starting date for the rental period the property unit tenant selection is a drop- down list I'll go ahead and select a tenant the tenant address will automatically fill in if you doubleclick the starting date it will open up a calendar for you to select a date the end date is automatically calculated to be 1 month from the starting date you select so even if I select a day other than the first for example say you collect rent on the 15th of the month the formula will automatically set the end date to the 14th of the next month the spreadsheet will then pull any rent collected during that time period so if your tenant made a partial rent payment then the remaining balance at a later date in the month the receipt will show that at the bottom you can select the method of payment from here I can go ahead and print to a PDF or paper if needed I'm going to go ahead and show you a print preview the annual receipt generator is essentially the same thing except the rental period covers a full year just like the monthly receipt generator you can select any date so you're not limited to the first day of the year it will give you all the rent paid over the one-year period you specify and the annual total there is one more sheet I will go over and that's the basic rent collection template this is a manual entry alternative to the automated sheets it's really simple you just input the rental property address at the top tenant name and rent collected for each month the sheet will then calculate the monthly total and annual total simple as that all you need to do is duplicate this sheet for each rental property you're managing so if you do plan on buying this spreadsheet from my Etsy store you have two different ways to track your rental income as you noticed I used a lot of drop down list this does two things it makes it easy for inputting and keeps data consistent for search functions I'll go through how to set up the drop- down lists it all starts with the property sheet this is the first sheet you need to set up on this sheet you need to input the addresses of all your rental properties the other sheets will pull on this information for example the rent receipt generator pulls the rental property address from here now there are also a number of other fields that don't get used anywhere else in the spreadsheet so you don't have to use them but I do find it comes in handy to have all this information in one location the other sheet you need to fill in is the tenant list as you can see the property is a drop- down list it pulls this information from the property sheet so you have to fill in the property sheet first in order for this to work the unit number and tenant name should also be filled in as this is what gets pulled for the drop- down lists in the rent collection tracker and receipt generator if it's a single family home you can leave the unit number blank the rest of the fields in this sheet aren't used anywhere else so you don't have to input anything but it does come in handy to have a One-Stop shop for all your info on the right hand side I put in a duplicate Checker this checks the property unit and tenant combination for duplicates if there are duplicates this will flag it for you to fix let's give it a try I'll go ahead and duplicate the first tenant as you can see it got flagged now I'll delete it and the flag is gone so that's a quick overview of how you use this spreadsheet in the rest of this video I will show you how I built this so you can make your own let's look under the hood of the tenant list if I go to the menu and select data then data validation and select the column for property you can see I'm pulling the drop down list from the property sheet now now let's look at the inner workings of the rent collection tracker the drop- down list actually pulls from the tenant list but as you can see each drop down is a full string of the property unit and tenant name if you recall the tenant list actually doesn't show that let's go back and have a look so what I've done is I've combined all three cells into one string over on the right side now you're probably wondering why you don't see anything but what I've done is made the text the same color as the background that's because I like my spreadsheets to have a clean look but if you look closely at the formula bar you'll see that I have something here you'll also notice that I have an IF function and that's because I am checking the cells to make sure there are no blanks now I'm going to change the color of the text you'll see I get this warning about editing the cell that's because I protected any areas of the sheet where you're not supposed to input data I'll go ahead and press okay now you can see the combined string and so this is what is actually being used in the drop down list for the rent collection tracker and rent receipt generator now let's look at the magic behind the annual rent report so what I've done here is use the query function to pull the rent for each month based on the property the user selects if you look closely at this cell I have two query functions embedded into an IF function one for all properties and the other for a single property the IF function will run one of the two queries based on which property the user selects if you're familiar with SQL Syntax for databases the query function is very similar it's a really powerful and versatile function for extracting specific information from data quickly in the rent receipt generator there are two functions I want to go over the tenant address and the payment details the tenant address uses a combination of the index and match functions to retrieve the city state and postal code I then use the text join function to combine all three strings into one string this one is a hard one to digest quickly so you may want to pause the video if you're interested in making your own the next function I want to go over is down in the payment details section here I use the filter function to retrieve the rent collection info based on the tenant selected and Rental period and that's it if you found this video useful please like And subscribe to my channel it will help me post more content on how to use Google Sheets to boost your productivity let me know in the comments about other topics you would like me to cover also if you don't want to spend the time making your own please visit my Etsy shop and you can purchase it for a very reasonable price till next time