Prorated
By Richard Rost
4 years ago
How to Calculate Prorated Rent in Microsoft Access
In this Microsoft Access tutorial, I will show you how to calculate prorated rent (or utilities, or whatever else you want) using simple query calculations and some date functions.
Alana from Phoenix, Arizona (a Gold Member) asks: I run an apartment complex. We get a lot of tenants moving in or out on days that are not the 1st of the month. How can I calculate their prorated rent due?
Pre-Requisites
Links
Recommended Courses
Methods
- Number of days in a banker's month (30)
- Number of days in the average month (30.4167)
- Number of days in the year (365 or 366)
- Number of days in the current month
Usage
- D: MoveInDate
- FirstDayNextMonth: DateSerial(Year([D]),Month([D])+1,1)
- DaysInMonth: Day([FirstDayNextMonth]-1)
- DaysLeftInMonth: [FirstDayNextMonth]-[D]
- Rent: 2000
- EachDayWorth: [Rent]/[DaysInMonth]
- ProratedRentDue: Round([EachDayWorth]*[DaysLeftInMonth],2)
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Prorated Rent Calculator for Landlords, How is the prorated rent calculated, How do you calculate prorated expenses, What is rent prorated, Top 4 Methods for Prorating Rent, Prorated Rent Calculator
Subscribe to Prorated
Get notifications when this page is updated
Intro In this video, I will show you how to calculate prorated rent and bills in Microsoft Access. We will cover several common methods, including using a standard 30-day month, the average number of days per month, and the actual number of days in the current month, even accounting for leap years. I'll walk you through building a query to determine the number of days left in the month, how much each day is worth, and how to calculate the final prorated amount due based on move-in dates. All examples are demonstrated using a customer table in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to calculate pro rated rent, bills, or whatever else you have to pro rate in Microsoft Access.
Today's question comes from Olena in Phoenix, Arizona, one of my Gold members. Olena says, I run an apartment complex. We get a lot of tenants moving in or out on days that are not the first of the month. How can I calculate their pro rated rent due?
Well, Olena, this is going to be one of those videos where I am going to tell you to go watch a couple other videos first. Then, based on the knowledge you get from those videos, you can put the Legos together in a different way to calculate the number that you need.
Go watch this video first. This video teaches you how to calculate the first day of the month, and it also teaches you how to calculate the first day of the next month following any date.
So if, for example, someone is moving in on December 4th, you have to figure out what the last day of that month is. You need December 31st. So you need to figure out the first day of the following month so you can figure out how many days this month has. Go watch this video first.
This video has a couple of other videos you might need to watch first to calculate fields, how DateSerial works, your Month and Day functions, and ISO date. Go watch all of these if you are not familiar with any of this stuff first, then come on back. Also watch my video on rounding numbers - real simple. Those are all free videos on my website and my YouTube channel. I'll put links down below you can click on to go watch them, so go watch them, then come on back.
This is my TechHelp free template. This is also a database you can grab off my website if you want to, but I am going to use it because I have got a customer table and my customer table has a whole bunch of dates in here - where are they, where are those dates?
Oh, right here, CustomerSince. We are just going to use these, and we'll pretend those dates are their move-in dates. So let's go make a query. Create. Query Design. I am going to bring in that customer table with all those move-in dates or move-out dates, whichever you prefer.
Now, there are four different ways that I can think of to calculate pro rated rent or whatever.
The first is the number of days in a banker's month: 30. So, for example, if someone is moving in on the 20th, they have to pay 10 days' worth of rent. You just take the rent divided by 30, multiply it by 10. It's the simplest way to do it. You don't have to know how many days are in a particular month to calculate that. That's easy. You can figure that out based on the rest of the stuff I am going to teach you. So if that's the method that you use, it's really easy.
The next way is to use the number of days in the average month. If you take the total number of days divided by 12 in a year, you get 30.4167, or 30.42 is what I see a lot of. That is another easy way to do it. That way, you don't get screwed if you are dealing with February versus January. One has only got 28 days, so each day is more expensive.
Another way is to do it based on the total days of the year. Again, 365 or 366; it depends on if it's a leap year or not. I have got a video coming out, probably tomorrow or the next day, that's going to show you how to calculate a leap year. It's not that hard. Someone else asked me to do that, so look for tomorrow's video if you are interested in that. I'll put a link to it down below even though it's not finished yet.
But I am going to show you this one, which is the most popular, the most common way to pro-rate something. You have to figure out how many days are in the current month and then you divide up the rent by those. So if you are moving in in January, each day is going to be less expensive than if you are moving in February. That's how most places that I have seen pro-rate rent and stuff like that. This is actually the hardest method to calculate, so if you can do this one, you can very easily do the other ones.
Back to the database.
Let's find that CustomerSince field. I don't want to keep calling it CustomerSince. That's my move-in date, so I am just going to call it D like that: D:. That's called an alias. I have now referenced CustomerSince as D so I can call it D everywhere else.
Now I have got to find the first day of next month so I know how many days are left in this month for my calculation. Since you watched my first day of the month video, you know you could find the first day of next month with that formula right there. I am going to copy that and I am going to paste it right there.
Paste. I zoomed in - Shift+F2. First day of next month, and there you go. There's your formula. Now if I run it, there is my D, there is my move-in date, and there is the first day of next month, and these are all old dates.
Let's make this one more current. Let's go 2022-12-06 as today's date. The first day of next month is January 1, 2023.
Now that I know what the first day of next month is, I can easily calculate how many days are in this month by taking this and subtracting that. Remember, in Access, dates have a value of one for a day. So if you go from January 1 to January 2, that's basically adding one to it. So if I subtract this date from this date, remember, larger dates are in the future - that will give me the number of days between them.
Back to Design view. Right here, I am going to zoom in - Shift+F2. The days left in month is going to be the first day of next month minus D, my original date.
Run it. Now you can see how many days are left in the current month that you are in.
If I change this to 12-30, there are two months that you are liable for the rent, like the 30th. If you are moving in on the 30th, you have to pay for the 30th and the 31st.
Here's one that's February 1; you have to pay 28 days. 2003 is not a leap year. So that number is correct. That's the number of days that you are responsible for the rent.
Now, here is the tricky part. In order to know how much each day is worth, we have to know how many days are in that month. So we have to know if it's February, you have 28 days, so you divide the rent by 28; if it's January, you divide by 31. But you get leap years in there too.
What we are going to do is figure out what the last day of this month is, which will give us the number of days in that month. We are going to subtract one from that date. So this is the first day of the following month. If we subtract one from it, then we get the last day of the current month, and then we can pull its day value out. This will return 31; this will return 28 or 29 if this happened to be 2004.
I think 2000 was supposed to be a leap year, but it wasn't because it's divisible by 100, but it's also divisible by 400, so 2000 was a weird year.
Back to Design view. Zoom in.
The days in the current month are going to be: take the first day of next month and subtract one from it. Then we are going to take the day value of that.
So the first day of next month, right here, let's say is January 1 of next year. Subtract one from it, now we are back to December 31 of this year. Now I'll take the day of that. Remember, you've got day, month, year - the day value is going to be 31, the value of the day.
Let's see what it looks like. Look at that. There are our day values.
Now we know what to divide the rent by to get a value per day. This is the one where you can just substitute a 30.4167 if you are using the average month. If you are using the banker's months, you can just put a 30 in here. But that's the tough one to calculate. That's the one that trips everybody up: how do you know how many days are in this month?
Now, what's the rent? Well, we could just throw it in the query if it's the same for everybody. If not, you could make a field in your customer table called Rent and pull that in here. But I'll just make it Rent. Let's say it's $2,000.
If you are up in Buffalo, New York, where I spent most of my life and grew up, then you are getting a five-bedroom house, three bathrooms, full-size yard, all that. If you are down here in Southwest Florida where I live, it's a one-bedroom shack. Rents are definitely higher down here.
There is your rent. Again, that can be a field out of the database if it's different for everybody, but we will just say it's the same for everyone. Doesn't matter for this example.
Now I know what the rent is. I know how many days are in each month. Now I can calculate what each day is worth.
So right here, zoom in again. Each day worth is going to be the rent divided by the number of days in the month.
Run it. There you go. The little pound symbols just mean that the field's not wide enough. You can round this if you want to. I don't recommend it in this particular case. I would let the rounding go to the end of the calculation. We are going to round it in the next step, because all these little fractions of a penny do add up. If they owe you, you know, 28 days and you got an eighth of a cent right there, it's going to add up after - 0.8 times 20 - and you are going to lose a few cents of rent there, which - oh no!
But then we end up with, you know, the Superman 3 problem, the Office Space problem, and then the federal prison - stealing money and burning down the building. Anyway.
So now we have one step left.
We know what the rent is. We know what each day is worth. Notice how they are all slightly different - 31 day months, each day is only worth $64, but February, 28 days, each day is worth $71.
Now we can multiply what each day is worth times the number of days left in the month that you are responsible for, and that will come up with how much you owe for pro rated rent for this month.
Final column, ready? Pro rated, and it is one word, by the way. I Googled it. I always thought it was at least hyphenated, but it is not, it is one word: prorated rent due. It is going to be: we are going to round this one - round each day worth times days left in month, comma 2 - we round to two decimal places, or in other words, pennies, and there is your pro rated rent due for each person based on how many days are in that month, based on how many days are left in that month.
We then get the total days worth and that's what you owe. You can see right here, anybody that moves in on the first - this person moved in on November 1 - owes 30 days and it should be equal to whatever the rent is, if your math is right.
Just looking down the list, yeah, everybody's okay. Let's change this to, like, the 15th, let's see what happens there. That's slightly off, let's change to the 16th. Perfect, a thousand. Yeah, because 15 would be in the first half of the month, 16 to 30 would be the second half of the month, so that makes more sense.
Like I said, if you are using the other methods, banker's month, the average month, you just substitute this. You won't need the 28, 30, 31 number there. You just put all 30s in that column, and then these numbers will all come out the same.
If you care about the leap years, look for my leap year video. I'll put a link to that down below as well. Again, it's not ready yet, but it will probably be ready soon, within the next day or two. That is next on my list. I'll be recording it tomorrow.
So there's your fast tip for today. See how a lot of these questions that I get are just things that I have already shown you in other videos: how to do all of these things. Nothing in today's video really was new. It's just taking the Legos and putting them together in different ways and me showing you how to take bits and pieces from this lesson and put it with that lesson and put them together and shake it all up.
So if you have any other questions like this, send them in.
Oh yeah, I have to advertise some of my other courses here: Access Expert 27, 28, my DateTime Seminar, if you want to learn a lot more about dates and times and stuff like that. In my full courses, we take a lot more time and go over stuff in more detail. We sit around the fire, we sing Kumbaya, and all that good stuff. So it's a good time.
So that's your fast tip for today. I hope you learned something, I hope this helped you out, and we will see you next time.Quiz Q1. What is the main topic covered in this video?
A. How to apply filters in Microsoft Access
B. How to calculate prorated rent or bills in Microsoft Access
C. How to import data into Microsoft Access
D. How to back up your Access database
Q2. Why might someone need to calculate prorated rent?
A. To increase the rent each month
B. For tenants moving in or out on days other than the first of the month
C. To split the rent equally among all tenants
D. For annual rent adjustments
Q3. Which method of prorating rent was described as the most popular and common?
A. Using a banker's 30-day month for all calculations
B. Using the average month length (30.4167 days)
C. Using the exact number of days in the current month
D. Using a flat daily rate based on the year
Q4. In Microsoft Access, what does subtracting one date from another return?
A. The total weeks between the dates
B. The month difference between the dates
C. The number of days between the dates
D. The year difference between the dates
Q5. Why is it important to know the first day of the next month when calculating prorated rent?
A. To calculate interest for a new lease
B. To determine utilities usage
C. To find the last day and the total days in the current month
D. To set automated reminders
Q6. Which Access functions or concepts does Richard recommend understanding before watching this video?
A. SUM and AVG
B. DateSerial, Month, Day, and ISO date
C. LEFT and RIGHT
D. GROUP BY and HAVING
Q7. When calculating the value of a single day of rent for a prorated month, what do you divide the rent by?
A. 365 or 366 days
B. The average number of days in a month
C. The number of days in the specific month
D. The number of tenants
Q8. What issue can occur if you round before the final calculation when prorating rent?
A. You will need to repeat the calculation
B. You may lose or gain a few cents per tenant, which adds up
C. The calculation will take much longer
D. The database will generate an error
Q9. If someone wants to quickly prorate rent using a standard, simplified method, which value might they use for the number of days in a month?
A. 10 days
B. 28 days for all months
C. 30 days (banker's month)
D. 12 days
Q10. What should you do if you want to account for leap years in your prorated rent calculation?
A. Always use 365 days
B. Ignore leap years entirely
C. Calculate whether it's a leap year and use 366 days when appropriate
D. Use 28 days for February every year
Q11. What is the purpose of using an alias like D: in an Access query in this process?
A. To calculate sums faster
B. To reference the CustomerSince field more easily in later expressions
C. To exclude fields from the results
D. To duplicate data
Q12. When using the "banker's month" method to prorate rent, what assumption is made?
A. Every month has a different number of days
B. Every month has exactly 30 days
C. Every month is a leap month
D. Every month has only 28 days
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-B; 9-C; 10-C; 11-B; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone is all about calculating prorated rent and similar bills using Microsoft Access. I want to address a common scenario asked by one of our members who manages an apartment complex. She needs to calculate rent for tenants who move in or out on dates that are not the first of the month.
To accurately calculate prorated rent, you need a good understanding of how dates work in Access. For those who are not familiar, I highly recommend reviewing some of my other tutorials first, including lessons on calculating the first day of a month, finding the first day of the next month, using the DateSerial function, and understanding Month and Day functions. There's also a video on rounding numbers you may find helpful. All of these resources are freely available on my website and YouTube channel.
For this tutorial, I will use my TechHelp free template. This template comes with a customer table that includes a move-in date for each tenant, so it makes a great example for our calculation.
When pro-rating rent, there are several standard approaches:
1. You can use a banker's month of 30 days. With this method, if a tenant moves in on the 20th, you simply charge for 10 days. The calculation becomes rent divided by 30 times the number of days occupied that month. It's easy and doesn't require you to know how many days are in each specific month.
2. Another method is to use the average number of days in a month, which comes out to just over 30.41. This method ensures consistency across months like February and January, which can have very different day counts.
3. You can also base your calculation on the number of days in a year, whether 365 or 366 if it's a leap year.
4. The most accurate and widely used method, which I will demonstrate today, is to use the actual number of days in the specific month. This reflects the true daily rent value for that month, since a day in February costs more than a day in January.
Let me walk you through the steps in Access:
First, you'll want to work with the move-in date, which is called CustomerSince in my table. For the sake of simplifying future calculations, I'll create an alias for this field, just calling it "D."
Next, you need to find the first day of the next month. Once you have that, you can determine how many days are left in the month after the move-in date. In Access, each date increment equals one day, so subtracting the move-in date from the first day of the next month gives you the number of days remaining in that month.
For example, if someone moves in on December 6, 2022, the next month's first day is January 1, 2023, so you can see exactly how many days they owe rent for in December.
To calculate the total number of days in the month, you subtract one from the first day of the next month, which lands you on the last day of the current month. Then, using the Day function, you can extract the numeric day part from that date, which gives you the actual day count for the month—28, 29, 30, or 31, depending on which month and year it is.
Now that you have both the number of days in the month and the number of days the tenant occupies, you can determine the daily rent. This simply requires dividing the total rent by the number of days in the month. You can use a static rent value or pull this from your database if it varies per tenant.
With the daily rent value calculated, all that remains is to multiply this by the number of days covered. I recommend doing your rounding at this final step to avoid pennies lost due to fractions earlier in the math.
What you'll notice is that in months with more days, the per-day rent is slightly lower, and in months like February, each day costs a bit more. This approach provides an accurate, fair calculation that accounts for the actual length of each month.
To sum up, the formulas and Access queries involved take the move-in date, determine how many days are left in that month, calculate how many days are in the month overall, and use these to produce a fair, precise prorated rent amount.
If you use alternative methods, such as a fixed 30-day month or the average month length, you can simply substitute those values instead of calculating the day count, and the process works the same way.
For those interested in leap years, I will soon have a video explaining how to determine if a year is a leap year in Access.
As you can see, today's tip is less about any new technical process and more about assembling separate concepts into a single, practical solution. Many questions I get use skills I've already covered; it's just a matter of combining them.
If you'd like to learn more about dates and times in Access, check out my Access Expert and DateTime Seminar courses where we look at these topics in much greater depth.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Calculating pro rated rent in Microsoft Access
Creating a query to process move-in or move-out dates
Referencing a table field as an alias in a query
Finding the first day of the next month using a formula
Calculating days left in the current month using date math
Determining actual days in a specific month via date functions
Handling leap years in rent calculations
Entering static rent values in a query for calculations
Calculating per-day rent cost for various month lengths
Multiplying per-day rent by days left for pro rated total
Rounding pro rated rent calculations to two decimals
Adjusting calculations for different move-in dates
Alternative methods for pro rating rent (banker's month, average month) |
