anniversery update

[shamsu203]

Hi
I have various properties let out to various tenets . The tenancy are of various periods eg some may be 1 year or others may be 3 ,5, 10 years when date the rent increase may be yearly or every 2 or 4 years from the start of the contract. Is it possible to have an excel spreadsheet to show the monthly rent and also the date and amount of the increase rent on the annversery date..

 

[Joe4]

Re: annversery update

Welcome to the Board!

Yes, that should be possible. If you need help with doing that, we would need a bit more details, like the structure of your data and the "rules" you want to impose.

 

[shamsu203]

Re: annversery update

Welcome to the Board!

Yes, that should be possible. If you need help with doing that, we would need a bit more details, like the structure of your data and the "rules" you want to impose.

[TABLE="width: 805"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Hi
I give below the structure and rules this is basic and if you have any suggestions it will be appreciated
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Deposit[/TD]
[TD="align: right"]₹ 500,000.00[/TD]
[TD] [/TD]
[TD="align: right"]₹ 100,000.00[/TD]
[/TR]
[TR]
[TD]Tenanacy Details[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property Tax[/TD]
[TD="align: right"]₹ 3,614.00[/TD]
[TD] [/TD]
[TD="align: right"]₹ 3,195.00[/TD]
[/TR]
[TR]
[TD]Tenanacy Start Date[/TD]
[TD="align: right"]01-02-18[/TD]
[TD="align: right"]01-01-18[/TD]
[TD="align: right"]01-12-18[/TD]
[/TR]
[TR]
[TD]Agreed Rent[/TD]
[TD="align: right"]₹ 15,000.00[/TD]
[TD="align: right"]₹ 5,000.00[/TD]
[TD="align: right"]₹ 10,000.00[/TD]
[/TR]
[TR]
[TD]Rent Increase Interval months[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24

[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Rent Increase %[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD]Current Rent[/TD]
[TD]this is either the agreed rent or the rent after increase. this will change at the next interval period i.eg 15000 become 16500 on 1-2-19, then18150 0n 1-2-20[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Next Rent Increase date[/TD]
[TD]date next incrase is due based on the increase interval eg based on start date the next rent incrase is due 1-2-19 then 1-2-20 once 1-2-19 is reached[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] Regards
shamsu
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: annversery update

OK, let say the the key values are in the following cells:
B5: Tenancy Start Date
B6: Agreed Rent
B7: Rent Increase Interval Months
B8: Rent Increase %

Then the formula for Current Rent would be:

=B6*(1+B8)^INT(IF(TODAY()>B5,DATEDIF(B5,TODAY(),"m"),0)/B7)

As for the next rent increase date, I had to create my own function in VBA to do it, like this:

Function NextDueDate(dteStart As Date, intInterval As Integer) As Date
'   Calculate next due date
'   - dteStart: start date
'   - intInterval: interval of increase (in months)


    Dim i As Long
    Dim calcDate As Date
    
    For i = 0 To 1000
        calcDate = DateAdd("m", intInterval * i, dteStart)
        If calcDate > Date Then
            NextDueDate = calcDate
            Exit For
        End If
    Next i


End Function

Then, you would just use it like any other Excel function on your sheet, i.e.

=NextDueDate(B5,B7)

There maybe a way to do it without using VBA to create your own function, but I cannot quite figure it out.
If you want to play around with it, take a look at these threads, as I think you will probably want to do something along these lines:
https://www.mrexcel.com/forum/excel-questions/963649-calculate-next-due-date-recurring-tasks.html
https://www.mrexcel.com/forum/excel-questions/590524-how-can-i-calculate-next-payment-date.html

 

[shamsu203]

Re: annversery update

OK, let say the the key values are in the following cells:
B5: Tenancy Start Date
B6: Agreed Rent
B7: Rent Increase Interval Months
B8: Rent Increase %

Then the formula for Current Rent would be:

=B6*(1+B8)^INT(IF(TODAY()>B5,DATEDIF(B5,TODAY(),"m"),0)/B7)

As for the next rent increase date, I had to create my own function in VBA to do it, like this:

Function NextDueDate(dteStart As Date, intInterval As Integer) As Date
'   Calculate next due date
'   - dteStart: start date
'   - intInterval: interval of increase (in months)


    Dim i As Long
    Dim calcDate As Date
    
    For i = 0 To 1000
        calcDate = DateAdd("m", intInterval * i, dteStart)
        If calcDate > Date Then
            NextDueDate = calcDate
            Exit For
        End If
    Next i


End Function

Then, you would just use it like any other Excel function on your sheet, i.e.

=NextDueDate(B5,B7)

There maybe a way to do it without using VBA to create your own function, but I cannot quite figure it out.
If you want to play around with it, take a look at these threads, as I think you will probably want to do something along these lines:
https://www.mrexcel.com/forum/excel-questions/963649-calculate-next-due-date-recurring-tasks.html
https://www.mrexcel.com/forum/excel-questions/590524-how-can-i-calculate-next-payment-date.html

tks the first part worked beautifully. I did not use the vba as am not familiar with the same

 

[Joe4]

Re: annversery update

I did not use the vba as am not familiar with the same

You don't need to know anything about VBA to use what I wrote for you.
Just follow the first four steps under "Insert VBA code to Excel Workbook" in the following link to add my VBA code to a module:
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Then, you can use it like you would any other Excel function, like I showed in my previous post.

 

[shamsu203]

Re: annversery update

You don't need to know anything about VBA to use what I wrote for you.
Just follow the first four steps under "Insert VBA code to Excel Workbook" in the following link to add my VBA code to a module:
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Then, you can use it like you would any other Excel function, like I showed in my previous post.

Hi
I copied and pasted the VBA code given and changed the cell ref as required . I get an error message=name? Pls let me know what needs to be done remove this error.
Tks

 

[Joe4]

Re: annversery update

I copied and pasted the VBA code

What is the name of the module where you have posted this VBA code that I gave you?

 

[shamsu203]

Re: annversery update

What is the name of the module where you have posted this VBA code that I gave you?

the name of the module is NextDueDate

 

[Joe4]

Re: annversery update

the name of the module is NextDueDate

Not the name of the function, but rather the module.
They usually have named like Sheet1, ThisWorkbook, Module1, Module2, etc unless you physically changed the default name of your module.
The reason why I am asking is because if you place it in the wrong module (i.e. one of the Sheet modules) and try to access it from another sheet, it won't work and you will get the error you are describing.

What that error is telling you is that it cannot find any function with that name which means one of three things:
- you placed it in the wrong area
- you mis-typed the function name somewhere (either in VBA or on the sheet, so there isn't an exact match)
- Macros/VBA are not enabled on your workbook