How to calculate monthly rent (not simple)

[JTSmith]

I have a series of Jobs, each with a move in date, move out date, and daily rent rate. I would like to option to view each month's rent that's due, based on those dates. Most of them jobs will have a full month, for example, Jan 1 - Dec 31 renter will have a full month of July. Some people may move out in mid-July, so I'd like to know the totals. Mainly, somehow to calculate "if Bob is moving out on July 5th, and his rent rate is $100 per day, July's rent due is $500."

Any ideas? Thanks!

[Vaughan]

To work out the total rent for each job use:

( DateOut - DateIn ) * DailyRentRate

The tricky bit is when the job spans more than one month: e.g., Bob moved out on 5 July but moved in on 25 June, and you want to split the rent between months.

In this case, it depends on how you want to report.

The most flexible is to create separate related "Day" records for each day a job is done on, and summarise those dates. This could produce a report by day, week, month etc.

Or perhaps do it the other way: create "Month" records that get related to the jobs by the date fields. Need to think about it a bit....

[No_access]

I think this will work for you

rent = monthly rent field

moveout is the moveout date.. this assumes tenant is current;y up to date on rent..

with minor modifications you can go from one date in a previous month to the actual moveout date..

Let(

[daysofmonth$ = Date (Month (moveout) + 1; 0; Year (moveout));

daysofmonth$ = Date (Month (moveout)+1 ; Day(moveout); Year (moveout))-daysofmonth$;

daysofmonth2$ = Date (Month (moveout)+1; 0; Year (moveout))-Date (Month (moveout); 0; Year (moveout))

];

Round((rent / daysofmonth2$) *daysofmonth$;2)

)

[comment]

This part is not quite clear:

I would like to option to view each month's rent that's due

Do you mean you will select a specific month to view? Or do you just want a breakdown of payments for each job? Or a breakdown of total payments by month?

[JTSmith]

This part is not quite clear:

Do you mean you will select a specific month to view? Or do you just want a breakdown of payments for each job? Or a breakdown of total payments by month?

On July 1st, I enter in everyone who owes money for August into my QuickBooks. So on July 1st, I would like to run a report that says:

August Total Due:

Bob Aug 1 - Aug 31 $100 (daily rate column) $3,100 (total due column)

Sara Aug 1 - Aug 10 $50 $500

Jon Aug 1 - Aug 31 $70 $2,100

Steve Aug 15 - Aug 31 $100 $1,600

Total Due for Aug is $7,300

Jon would actually be $2,170 but you get the idea...

[comment]

Try =

Let ( [

today = Get ( CurrentDate ) ;

m = Month ( today ) ;

y = Year ( today ) ;

start = Max ( StartDate ; Date ( m + 1 ; 1 ; y ) ) ;

end = Min ( EndDate ; Date ( m + 2 ; 0 ; y ) )

] ;

DailyRate * Max ( end - start + 1 ; 0 )

)

This should return, for each job, the sum due for the month following the current one. The calculation must be unstored.

[JTSmith]

Try =

Let ( [

today = Get ( CurrentDate ) ;

m = Month ( today ) ;

y = Year ( today ) ;

start = Max ( StartDate ; Date ( m + 1 ; 1 ; y ) ) ;

end = Min ( EndDate ; Date ( m + 2 ; 0 ; y ) )

] ;

DailyRate * Max ( end - start + 1 ; 0 )

)

This should return, for each job, the sum due for the month following the current one. The calculation must be unstored.

Genius. Thank you (again). I really appreciate your assistance in making my life less stressful...