Get Your Free Rent Receipt Template Excel for Supervision Today

Free rent receipt template excel for Supervision

hey everyone I'm going to show you a spreadsheet that I created using Google Sheets to track rental property rent payments missed payments outstanding balances and automatically generate rent receipts as a landlord myself with spreadsheet building skills I created this spreadsheet out of necessity hopefully this video will give you some ideas of how 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 and as you can see there are multiple sheets at the bottom the main sheets that provide all the core functions are rent collection annual rent tracker and the receipt generators but let's go through one by one so you can see how it all ties together the first sheet is the properties sheet this is fairly straightforward enter your property details such as address description number of units and any specific details you want to record if there is information that you want to record but no field for it you can enter it into the notes field the only mandatory field that you need to enter is the address right now I've got two properties the next sheet is the tenant list and rent roll as you can see the property field is a drop- down list this drop- down pulls the address from the property sheet we just looked at the mandatory fields are the unit number and tenant name if the property is a single family home you can just leave the unit number blank also the unit number doesn't have to be a number it can also be a combination of numbers and letters or even just letters you can also input an email address and phone number as you can see 456 Elm Street is a fourplex with four different tenants and 123 any street is a duplex with two tenants now let's go through these other fields in a bit more detail you'll see that some of these headings are in yellow and that's because if you want to use the rent payment tracking features in the annual rent report sheet these fields will need information the lease amount is the monthly rent that the tenant has agreed to pay on the sign lease the late fee is a fee that gets charged to the tenant when he or she makes late rent payments the grace period is the period in days after the first of the month that a tenant is able to pay the full rent before getting charged late fees in some jurisdictions this isn't allowed so if you're using this spreadsheet and it's not allowed in your jurisdiction then you can leave the late fee and grace period blank if you do fill it in then the annual rent report will use this information to determine if a late fee should be applied or not here you can record the last month rent deposit and damage security deposit again in some jurisdictions a damage deposit may not be allowed or is sometimes combined into last month's rent these fields don't have to be filled in for the rent tracker to work but it's handy to have it here for easy reference these next four fields are required if you want to use the lease tracking and rent increase tracking features the lease start and end dates are used by the rent tracker to check if the rent report period Falls within the lease period so for example if the lease expires for a tenant and the tenant moves out during during the reporting period Then the rent for that tenant won't be tracked after the lease expires these fields are also used to generate notifications when the lease is going to expire within 90 days the rent increase field is the increased rent payment if you plan to raise the rent the rent increase date field is the date which the rent increase takes effect the rent tracking feature will automatically account for rent increases on the dates specified and track outstanding payments against the new rent if you don't plan on inreasing rents then you can leave these fields blank now let's look at the rent collection sheet 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 which pulls its information from the tenant list I'll just go ahead and pick one then I'll input a rent amount in any note that I want to record a couple more things 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 view just click the X in the upper right corner I recommend you use filtered views for viewing only if you want to edit data I suggest turning the filtered views off now let's look at the annual rent tracker this is where you can track rent payments missed rents outstanding payments and much more you can track it on a single property basis or all properties and on a month-by-month basis and annual basis there are only two inputs needed the starting month and property the output for the rest of the sheet is all automatically generated I'll show you later in the video how some of this works the starting month can be any month so if I select January you can see that the next 11 months will take me till December let's go over what you can track in this sheet on the left is an annual summary where you can see total rent paid and total outstanding rent payments for a single property or all properties in the middle is a summary of the lease terms so you can quickly check when a lease is expiring there is also a lease expiry notification that will automatically highlight when a lease is about to expire within 90 days or if it's already expired it will also show you which tenant has a rent increase coming up within 60 days if we keep scrolling to the right these tables will show the details of the rent collected M rent and outstanding balances automatically there is no user input required in these tables let's walk through through an example with the tenant from 456 Elm Street unit number one whose name is John Let's Pretend JN is starting to have some financial trouble and begins to make late rent payments then eventually missed payments I've already input this data into the rent collection sheet so let's take a look here are the entries for John in January he makes a full rent payment of $1,000 on the 1 in February he makes two partial payments of $800 and $200 on the 1st and the 6th of February both which add up to his rent due of $11,000 in March he also makes two partial payments of $500 one on the 1st of March and another on the 12th again both of which add up to his rent due of $11,000 now in April this is where he starts to miss payments so on the 1 he only pays $500 and then no other payment in may he doesn't pay rent then in June he pays his rent due in full plus all of his outstanding rent and late fees on the first he makes a payment of $11,100 then he pays the outstanding balance of1 ,650 on June 6th now you'll notice he paid rent of $1,100 and not $1,000 that's because there was actually a rent increase this month which was input in the tenant list now let's go see how this looks on the annual rent tracker sheet looking at January there was no issue with rent as you can see rent due is $11,000 and John paid $1,000 so there is no late fee nor outstanding balance also if you look to the left of the table you can see when he paid as well well moving on to February as you recall he made two payments of $800 and $200 on different days you can see one payment on the first and another on the sixth but the total adds up to the rent due also notice that he made his second payment on the 6th of February now if you look at the grace period John has up to the 6th of February to make his rent payment since he paid the full rent within the grace period he is not charged a late fee now if you remember from the tenant list the grace period was set to 5 days let's take a look at the tenant list again now if I change the grace period to 4 days this late fee of $50 should be automatically charged let's try it I'll go ahead and change this to four now when I go back to the rent tracker as you can see the late fee is automatically added and the outstanding balance is now $50 I'm going to go ahead and change the grace period back to 5 days as you recall in March John also made two payments but this time one payment of $500 within the grace period and $500 after the grace period although John made the full payment he gets charged a late fee since his second payment was after the grace period in April as you can see the rent due is $11,000 but John only makes a partial payment of $500 which means he is short $500 notice that the previous month's outstanding balance is now added to April's outstanding balance so $500 outstanding rent plus a late fee of $50 plus the outstanding balance of $50 $ from last month gives me $600 also notice how the outstanding balance is highlighted in red this gives you an easy way to visually scan for missed rent moving on to may as you recall from the rent collection sheet John skipped out on rent this month as you can see the outstanding rent is $11,000 plus a late fee of $50 plus the outstanding balance from last month giving a total of $1,650 finally let's move on to June in June John pays his rent in full on the first then pays the out standing balance of $1,650 on the 6th and since he paid his rent within the grace period there is no late fee notice how the rent due is now $1,100 that's because there was a rent increase let's go back to the tenant list to have a look at how this works as you can see for John he had his rent increase to $1,100 on June 1st so this is automatically pulled over into the annual rent tracker sheet in the month of June as you can see the notification shows up here any rent increase within 60 days will show up here all right now let's take a look at how we deal with a tenant that moves out after the lease expires and a new tenant moves in if we look at 456 Elm Street Unit 4 the tenant Shawn has his lease ending September 30th 2024 let's take a look at the rent tracker to see how this is handled as you can see in September the rent due for Sean is $1,000 and he paid it in full now looking at October he doesn't pay rent because his lease expired and he moved out also notice that his rent due is blank because October is past his lease expiration so that's why it's important to input the lease term if you have tenants moving in or out so now let's say we put unit four back on the market and get a new tenant that plans to move in November 1st back in the tenant list I've gone ahead and input a new tenant for 456 ilm Street Unit 4 his name is Roger and I've input his lease amount and term notice how I didn't delete the previous tenant Shan that's because the rent tracker needs this record for tracking Shan's previous rents now let's go back to the tracker as you can see I now have Roger as a new tenant in November with rent due of $1,000 Sean still shows up here but he has no rent due anymore just remember that you can't delete a tenant that moves out in the reporting period however if you start a new spreadsheet for your next accounting cycle then you can delete tenants that moved out now let's look at the 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 your name as the landlord whether it's your personal name or an LLC 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 if you double click the starting date it will open up a calendar for you to select a date the end date is automatically calculated to be one month from the starting date you select if your tenant made a partial payment first followed by a second payment at a later date in the month the receipt will show that the description field is left blank for you to fill in any details if you wish 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 and this is what the rent receipt looks like if you print it to PDF or paper now if you do end up saving rent receipts as a PDF file one thing I found to be a nuisance was renaming files after they were saved or naming the files before saving them so I created a PDF file name generator to use this you would first generate your receipt then go to this sheet copy the file name then when I go to generate a PDF I can paste the file name into the save as dialogue box the next thing I want to go over is the export bookkeeping sheet this sheet pulls the rent collection data into a table that you can copy directly into our rental property bookkeeping spreadsheet if you purchased it a few important points I want to mention are that if you are copying and pasting this data in the bookkeeping spreadsheet you must paste values only don't just press contrl V instead press control shift V secondly the descriptions for property and category must match what you have in the bookkeeping spreadsheet exactly character for character otherwise it won't work finally I want to touch on editing dropdowns if for example you find the property address has a typo and you want to edit it then there are a few things you need to watch out for so if I go back to the property sheet and make an edit this won't automatically update the rest of the Sheets if you go to the tenants list sheet you now see these red triangles you also see it in the annual rent tracker to correct this you need to reselect the correct item from the dropdown if you don't it's going to cause problems later down the road always check for red triangles in all your dropdowns if you see it then there is a mismatch somewhere between the drop- down list and the list it is pulling the information from and that's how you use this spreadsheet in the rest of this video I'm going to briefly go over how I created some of the tracking features now let's look under the hood of the annual rent tracker so for the rent collection what I've done here is use the query function to pull the rent for each month based on the property that 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 depending on what the user selects from the property drop- down list the IF function will run one of the two queries 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 information from Big sets of data quickly I also do the same with the rent tracker table except I'm pulling the data from the tenant list in order to make this work I actually pull in a lot of extra information which are in Hidden columns I'll go ahead and expand them so you can see what I mean as you can see there is a lot of information that I can pull from here I then use logic in these cells to determine the rent due whether rent was paid late or on time late fees and outstanding rent payments I also check for rent increases lease starts and expirations I also have logic to track outstanding rent payments from month to month 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