Rent Calculation

[JTSmith]

I have a calculation I use to know how much rent to bill for the next month (thank you Comment on that one).  I am switching my rental rates from daily to monthly, so I need it to work like this:

 

Rent: $1,000

MoveOut: 12/15/2014

 

It's November, so next month is December.  December has 31 days, thus the rent is $32.26 per day ($1,000 / 31), times 15 days equals $483.87.  I can't seem to figure out how to count the number of days in the next month...

 

Also if the move out is anytime after December, for example, January 5th, then the rate would be $1,000 since it's a full month's rent.

 

Any help would be GREATLY appreciated!

 

 

[comment]

I can't seem to figure out how to count the number of days in the next month...

 

I believe you only need to count the number of days in the month of MoveOut. If the next month is not the month of MoveOut, then its length is irrelevant. The number of days in any month is equal to the day of the last day of the month, and it can be calculated as:

Day ( Date ( Month ( MoveOut ) + 1 ; 0 ; Year ( MoveOut ) ) )

[JTSmith]

Thanks.  This is what you helped me with on daily rates:

Let ( [today = Get ( CurrentDate ) ;m = Month ( today ) ;y = Year ( today ) ;start = Max ( JOBS::move_in ; Date ( m + 1 ; 1 ; y ) ) ;end = Min ( JOBS::move_out ; Date ( m + 2 ; 0 ; y ) )] ; JOBS::rent * Max ( end - start + 1 ; 0 ))

 

I can't seem to figure out how to do the same with monthly...  Your calc works great for determining number of days in the next month, thanks.  Any advice on how to calculate days they are renting?  Sorry for asking, I just can't seem to figure out the calc...

[comment]

I am afraid your question is kind of out of context. Are you trying to calculate the rent amount for the next month (unstored)?

 

Any advice on how to calculate days they are renting?

 

The number of days they are renting (in the month of MoveOut) is =

Day ( MoveOut )

Divide this by the number of the days in the same month (as shown in my previous post) and multiply by Rent to got the pro-rata rent for the final month.

[JTSmith]

Yes, my goal is to calculate next month's rent unstored.  So if their move date is 12/15/2014, it should calculate 15/31 * rent, but if their move out date is 1/1/2015 on, it should be equal to rent.  Or if it's prior to 12/1/14, it should be zero.

[comment]

Ok, so something like =
 

Let ( [
next_month_start = Date ( Month ( Get ( CurrentDate ) ) + 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ;
next_month_end = Date ( Month ( Get ( CurrentDate ) ) + 2 ; 0 ; Year ( Get ( CurrentDate ) ) ) ;
pro_rata = Case (
// MoveOut is next month
next_month_start ≤ MoveOut and MoveOut ≤ next_month_end ;
Day ( MoveOut ) / Day ( next_month_end ) ;
// MoveOut is further in the future
MoveOut > next_month_end ;
1 ;
// otherwise
0 )
] ;
Rent * pro_rata
)

or, if you prefer =
 

Let (
pro_rata = Case (
// MoveOut is next month
MoveOut - Day ( MoveOut ) + 1 = Date ( Month ( Get ( CurrentDate ) ) + 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ;
Day ( MoveOut ) / Day ( Date ( Month ( MoveOut ) + 1 ; 0 ; Year ( MoveOut ) ) ) ;
// MoveOut is further in the future
MoveOut > Date ( Month ( Get ( CurrentDate ) ) + 2 ; 0 ; Year ( Get ( CurrentDate ) ) ) ; 1 ;
// otherwise
0 )
;
Rent * pro_rata
)

[JTSmith]

THANK YOU!!!!

[Ocean West]

In order for things to factor out with the rounding issue I had to round to the 4th decimal place in order for things to balance.

 

Primarily when calculating the "DailyRate"