Prorate Rent

[ReLISTO]

Hello.
Help needed... I am trying to create a formula to determine the amount of rent due for not only the start of the lease but for the full term of the lease itself.. For the following problems I would like to have a formula that could be utilized over and over again.

Lease Starts On 7/26/2013 | Lease End On 7/25/2014 | Rent is $3000.00/month

Formula should result in the following answers:

1. Rent Due 7/26/2013 through 7/31/2013 = 6 Days or $600.00
2. Rent Due 8/1/2013 through 6/30/2014 = 11 months or $33,000.00
3. Rent Due 7/1/2014 through 7/25/2014 = 25 days or $2500.00

Lease Starts On 2/26/2013 | Lease End On 2/25/2014 | Rent is $3000.00/month

Formula should result in the following answers:

1. Rent Due 2/26/2013 through 2/28/2013 = 3 Days or $300.00
2. Rent Due 3/1/2013 through 1/31/2014 = 11 months or $33,000.00
3. Rent Due 2/1/2014 through 2/25/2014 = 25 days or $2500.00

Lease Starts On 4/1/2013 | Lease End On 3/31/2014 | Rent is $3000.00/month

Formula should result in the following answers:

1. Rent Due 4/1/2013 through 3/31/2014 = 12 months or $36,000.00

___________________________________________

Here are the criteria:

1. Use a 30 day calendar. This is San Francisco, daily rent equals 1/30th of the monthly rent. If rent is $3000.00 then rent would be prorated at $100.00 a day
2. First and last day count in the equation.. If the lease starts on 7/29 this is considered day one. If they leave on 7/31 this counts as the last day as they are allowed possession of the property all the way to midnight.
3. Prorate using the 1/30th value..
   1. 7/29/2013 then 3 days of rent would be due for the remainder of July.. (the day they move in counts as day one)
   2. 9/29/2013: 2 days of rent would be due ($200.00) for the remainder of Sept
   3. 2/28/2013: 1 day of rent would be due ($100.00) for the remainder of Feb

_________________________________

I have searched a great many formulas, but many if not all use the actual number of days in the month and/or don't count the first and last day in the equation.

THANK YOU!!!!!

Eric

 

[JLeno]

The following formula works in all your examples:

=DAYS360(B1-1,C1)*$D$1

where B1 is the Start Date, C1 is the End Date and D1 is the daily rent