1 Reply Latest reply on Apr 21, 2016 2:23 PM by diego.medrano

    Daily Balance Calculation

    Jagjit Singh

      Hi All,

       

      I have the attached workbook and am trying to get the rent charge to start from the correct date. I have 2 scenarios where customers might make the first payment along with the tenancy start date and in some cases make the payment after the tenancy start date.

       

      Below is some information on how the rent calculation works:

       

      Current Rent Calculation: (While reading the below please refer to the table desired output)

      The system currently calculates rent weekly in advance. When the tenant account is setup, the user enters the no of days the system needs to calculate the rent apart from the week rent in advance i.e prior to the first Monday. For Example, let’s take tenant 52535. The tenancy started on 09/03/2016 but the system will only reflect the rent on 14/03/2016 which is the first Monday for this tenant and that’s when all the accounts are updated. Since the tenancy started mid week, the first transaction will be commencement debit which is calculated as below:

      1. Calculate no of days between tenancy start date and first Monday/Rent Debit ( rent debit is specified by payment type 1)
      2. Calculate daily rent from the weekly rent ( for example 210/7 gives daily rent 30)
      3. Commencement debit is No of days * daily rate.

      The tenancy started on the 09/03/2016, the commencement debit is 09/03/2016 – 14/09/2016 ( 14 March is the first Monday for this tenancy). i.e 5 * 30 = 150. The means the customer owes 150 prior to the rent debit which is a Monday and 210 in advance for the current week. So in total the customer owes 360. This how the current system calculates the rent.

       

      Considerations:

      1) Tenancy Start Date: Tenancies start at different dates and at time tenant make payments on the first date and at time after the tenancy start date. Have a look at starting of accounts 52535 and 51481.

      2) Adjustments: Adjustments with Form_Type value 40 need to be ignored as its used for commencement debit and we are calculating this as in the above calculation in RED.

      3) Payment Reversal: Another thing to consider is the error like below. As you can see below the user added 210 for this tenant using paymenttype 14 and then against reversed the payment using paymenttype 14 which zero’s out the payment.

       

      Required Output: The Rent Balance values as in the below table is what needs to be achieved

       

      1) The First rent charge date is 09/03/2016

      2) Please consider the multiple transaction on 21/03/2016.

       

       

      52535_required.PNG

       

      For the below customer the first transaction needs to start on 07/03/2016 but needs to be charged when Form_type = 10.

      51481_required.PNG

       

       

      Thanks

      Jag

        • 1. Re: Daily Balance Calculation

          Hey Jagjit,

           

          Thanks for posting this question and including so much information. Screenshots and packaged workbook are always appreciated. It looks like this question has slipped through the cracks but you may have more success contacting support by creating a case at customer.tableau.com.

           

          -Diego