Daily rolling rent calculation

[tezza]

Hi

I'm just after a simple formula to show what the current amount of rent is due for the current month which rolls over to the next month.

Each row is a new month which should show 500 at the end of the month

Today should show on the row for December 5*(500/number of days in December) (5 as it's the fifth today, 6 tomorrow and so on)

Hope you can help

 

[jasonb75]

Something like this maybe

=DAY(TODAY())*500/DAY(EOMONTH(TODAY(),0))

Note that this will reset on the first day of each month. To make it an actual live amount, you would need a fixed starting date with a payment history to deduct.

 

[tezza]

In Column A I have the 1st day of each month, so this works for December so how do I go about telling it that the previous months have passed and that the value should be 500?

 

[tezza]

I think this works for what I need:

=if(month(today())<>month(A22),"", DAY(TODAY())*500/DAY(EOMONTH(A22,0)))

Thank you.

 

[jasonb75]

Looking at your formula, I may have interpreted it slighly differently, where yours will be blank for all but the current month, this formula will show 0 for future months, 500 for past months or amount to date for current month.

=MEDIAN(0,500,(500/DAY(EOMONTH(A22,0)))*(TODAY()-A22+1))

 

[tezza]

I've seen median being used but struggle to understand it's logic sometimes lol, in this instance how does it know if it's past, present or future?

 

[jasonb75]

Removing median from the formula, you will be left with

=(500/DAY(EOMONTH(A22,0)))*(TODAY()-A22+1)

Which will return a due figure of more than 500 for past, or a negative amount for future dates.

Median will take the middle of 3 values, 0, 500 or the result of the calculation above.
If the calculation is negative (future) then the middle value of the 3 will be 0 ( 0 is more than a negative number but less than 500).
If the calculation is greater than 500 (past) then it will return 500, more than 0 but less than the calculation.
If the calculation is between 0 and 500 (present) then the calculation amount is returned.

 

[tezza]

Removing median from the formula, you will be left with

=(500/DAY(EOMONTH(A22,0)))*(TODAY()-A22+1)

Which will return a due figure of more than 500 for past, or a negative amount for future dates.

Median will take the middle of 3 values, 0, 500 or the result of the calculation above.
If the calculation is negative (future) then the middle value of the 3 will be 0 ( 0 is more than a negative number but less than 500).
If the calculation is greater than 500 (past) then it will return 500, more than 0 but less than the calculation.
If the calculation is between 0 and 500 (present) then the calculation amount is returned.

That makes a lot more sense, thank you for clarifying.