ReLISTO

New Member
Joined
Jul 30, 2013
Messages
1
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
The following formula works in all your examples:

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

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

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top