Rent Calculations

[itnchans]

Hi All,

I'm currently involved with setting up a landlord rental payment system.

Can anyone point me in the right direction for a method or function on how to calculate rent owed that covers full and partial months.

 

[MarkK]

Well, to calculate daily rent for a particular month, you divide the monthly rent by the number of days in that month, so ...

DailyRent = MonthlyRent / DaysInMonth(DateInQuestion)

So pretend those are the unknowns, and we can solve them one by one, so days in month ...

Function GetDaysInMonth(d1 As Date) As Integer
    Dim tmp As Date
[COLOR="Green"]    'so we add a month to d1[/COLOR]
    tmp = DateAdd("m", 1, d1)
[COLOR="Green"]    'and we go to the first of that month[/COLOR]
    tmp = DateSerial(Year(tmp), Month(tmp), 1)
[COLOR="Green"]    'and we get the previous day[/COLOR]
    GetDaysInMonth = Day(tmp - 1)
End Function

... and that might be the hard part. So DailyRent, converting it to code, is ...

Function DailyRent(MonthlyRent as currency, DateInQuestion as Date) as currency
[COLOR="Green"]   'calls the GetDaysInMonth function, above[/COLOR]
   DailyRent = MonthlyRent / GetDaysInMonth(DateInQuestion)
End Function

Once you know the daily rent for a month it's a simple subtraction to determine how many days you are charging for, and you have all you need to prorate rent on a daily basis.

hth

 

[Rx_]

LOL - it depends on the contract and the laws that affect your area. You might need to quantify your question first.
For example: in this state it is an actual law that the Deposit must accumulate interest. In the past, I have pulled that on every apartment managent firm who has pretended they didn't know the law. If the contract pro-rates the daily rate on a sliding scale for example

When you are responsible for determining financial payments in the US as a programmer there also may be considerations. I was just informed that in 2013 and Error and Omissions insurance rider is basically required for all contracted programming staff.

Basically, it would help if you had a document that described the requirements and the exact rules along with the exceptions.

I often recommend that a common project like this look into an Off-The-Shelf Access program that provides the source code If there is something of a pre-made package for under $100 to get started, you might be well ahead to consider it and then modify or enhance it.

 

[itnchans]

Hi lagbolt,

Thanks for the reply.

I assume a loop will need to be in place to take account of rent that covers months with 30, 31 days.

For example, say the management company taken on a new property on 21/11/2012 and agree with the landlord that they will get paid on next payment cycle ending 31/12/2012 and not 30/11/2012. The loop in question works out the rent to cover remaining days in November and then full rent for December.

If i'm wrong please advise.

 

[MarkK]

I don't think you'd loop by length of month, since that's calculated after the fact, and it would be 28-31 anyway. More to the point, what is a 'payment cycle' and how does it work? Why does a contract starting 21-Nov not get billed 30-Nov? It appears to me that those rules will control how and when rent is calculated. The variation in length of month is just a necessary evil, not a control structure.