Hi - I have a payment schedule that is summarized annually that I would like to expand to show monthly. Additionally, there are two columns that dictate the Start and End date of the lease. The EFFDATE column shows when the rent amounts are effective.
Please see attached input and output.
I believe the tool to use is a multirow tool but having trouble with the expression. Any sample workflows would be greatly appreciated.
Thanks!
Solved! Go to Solution.
Hi @cireost
Here's a WF that accomplishes what you're looking for.
It starts by using a multi-row to get the [next EffDate] from the next row, then generates the monthly rows. The Filter and Select tools remove the months before the lease starts and clean up the results, respectively.
The output looks like this (showing the rows where the payment changes, but all rows are included)
Dan
This is great! Couple tweaks -
1) if the Start date was instead 4/15, this would skip the month of April and move on to May, losing the ability to prorate the month of April. Is there a way to address that?
2) if there is another Property in the list, I think the expression in multi-row tool might need to be tweaked? Not sure how though.
3) If the first effective date is not the first of the month, is there a way to update the loop expression in the generate row tool to return the first of the next month?
Updated example attached.
Thanks!
this is awesome.
for item 3 - looks like you added a formula (8) tool to change the EFFDATE to the first of next month. what I'm actually looking for is not to change that date but the next generate row should be 5/1, 6/1 and so on. please see updated input/ouput. hopefuly this is the last!
Thanks!
Hi @cireost
I've given you a good start with these 2 workflows. Why don't you try making the changes you need from this point on. If you run into specific issues, post your modified workflow and I'll try to help you out.
Dan
hi @danilang - please see attached excel worksheet with example input and output. Also attached is the workflow. I cannot seem to get the pro-rations to work. As a refresher, I am simply trying to expand a Rent Payment schedule to show monthly rent, however, issue come up when the start dates are in the middle of a month. Thanks!