• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rent Income increment one year after commencement

J

jan

New Member
Hi,


Need some help with my income budget and cashflow for a retail shopping centre.


Have over 100 shops each with different lease start dates , the rental increase is exactly one year after the start date, based on CPI or at a rate stated in the contract.

I want to budget monthly rental income by month, with increase in the particular month ( mid month)when the rent increased. Is there a formula I can copy and paste or do I have to do it manually.


Eg Annual rent is say $1000

Increase is 4%

Review date - rent increase 8/09/2011


July to Aug will be $100/12


Sept will be sum of (7 days@ old rent and rest 23days at new increased rent)

Oct - Jun will be new rent


Can someone please please help. I can attach spreadsheet will all info .


Jan
 
Hi Jan ,


Posting your workbook will certainly help ; please do so , if your data is not confidential.


As I understand you , you want that the formula for calculating the monthly rent , should take into account the date from which the rent is to be increased , and change the calculations accordingly. Not a problem.


An alternative to uploading your workbook , is to copy + paste about 10 rows of data from your workbook ; preferably , let the sample data include all possibilities.


Narayan
 
Hi Jan,


Do you keep a separate table of Stores, Start Rent, Change Date, Change By ?


With a table like this it is possible to calculate the Rental Income.
 
Hi Jan ,


Thanks for uploading. Please confirm that what you want is just the formulae to calculate the values in columns T through AE.


Narayan
 
Hi Jan ,


Try out the following formula :


=IF(T$7<($L11-DAY($L11)+1),

ROUND($C11/12,0),

IF(T$7=($L11-DAY($L11)+1),

ROUND($C11/12,0)*(DAY($L11)-1)/DAY(EOMONTH(T$7,0))+ROUND($Q11/12,0)*(DAY(EOMONTH(T$7,0))-DAY($L11)+1)/DAY(EOMONTH(T$7,0)),

ROUND($Q11/12,0)))


There seems to be a difference between the totals you had earlier and the ones which are a result of this formula ; you have to decide whether this formula is 100 % accurate or anything needs to be changed.


What is being done is :


1. Compare the date in T7 with the first of the month for the review date e.g. if the review date is September 8 , 2011 , the dates in T7 , U7 ,... will be compared with September 1 , 2011 ; if less , then take the old rent.


2. If the date is equal , it means the rent review is in that month ; take the pro-rata revision based on the review date.


3. If not , it means the month of the review date is past ; take the revised rent.


Narayan
 
its not working, comes up with the error name- in the month when there is a rent review and also, if there is no review date is past (not in the financial year) means they are on special deal and wont pay the rent increase.
 
Hi Jan ,


There was really nothing wrong with the formula , except that I had inserted some carriage-return characters ( using ALT ENTER ) to make the formula readable ; this should not have created problems , but it appears to have.


Copy this one :


=IF(T$7<($L11-DAY($L11)+1),ROUND($C11/12,0),IF(T$7=($L11-DAY($L11)+1),ROUND($C11/12,0)*(DAY($L11)-1)/DAY(EOMONTH(T$7,0))+ROUND($Q11/12,0)*(DAY(EOMONTH(T$7,0))-DAY($L11)+1)/DAY(EOMONTH(T$7,0)),ROUND($Q11/12,0)))


Another thing - this formula is only for the columns T through AE ; the formula for the year's total ( in column AF ) should be a simple SUM formula.


Narayan
 
Hi Narayan,


This is not working as well. All the cells are ok except the month when the increment is due. #NAME?.


thanks,


Jan
 
The Eomonth function requires the Excel Analysis Toolkit addin

If your using Excel 97 - 03 you will need to load the Excel Analysis Toolkit addin which is not installed by default

Goto Tools, Addins and select Analysis Toolkit
 
You must log in or register to reply here.
Back
Top