How to access the binder

To use the Hire Purchase or Chattel Mortgage Schedule, create a new binder selecting 'Accounts & Tax' - see the knowledge article: How to create a binder, for more information.      

Using the Accounts & Tax binder

When the binder is opened in Excel, add the Hire Purchase or Chattel Mortgage worksheet against the loan account. To do this, add 'H13 Hire Purchase Schedule' or 'H11 Chattel Mortgage Schedule' against the loan directly and select the loan as the reconciliation field. 

For more information regarding how to add a record template, see the knowledge article: How to link various records to a binder. 

Note: We recommend you choose to rollover the record template so when you are preparing the work next financial year, the loan balances will automatically rollover into the worksheet. 

Using the Hire Purchase or Chattel Mortgage Schedule worksheets

In the worksheet, enter the loan details: 

• loan description                    
• financial year end
• start date of loan   
• payment frequency
• payment in advance or arrears
• first payment date
• number of payments
• regular repayment amount
• admin charge
• balloon amount
• loan principal

Note: The 'Loan Principal' must be input for the interest expense to be calculated. 

Once this information is completed in full, the figures will populate accordingly into the 'Reconciliation', 'Summary' and 'Schedule' sections of the worksheet.

Accounting for irregular payments

WARNING: Accounting for irregular payments works best where the client has made a few one-off irregular payments which have replaced the usual repayment for the relevant period. It is NOT designed to cater to scenarios where a client has made payments in addition to their regular repayments, as it does not allow additional lines to be added to the 'Schedule'.      

To account for irregular payments, the 'Schedule' must be updated manually for columns N and L. 

Navigate to the 'Schedule' section of the record template and update the following information:

1. Column N - change one of the regular payments to be the irregular payment amount; 
2. Column L - change the irregular payment date, if required; and
3. Column L - if the date was changed for the irregular payment in step b, update the next date listed on the row below in the 'Schedule' back to its original date.  

Consequences of altering payments

Interest rates

If you wish to retain the original interest rate, copy over the value from the 'Calculated' interest rate to the 'Manual' nominal interest rate. If you wish to continue calculating the interest rate instead, leave it as is. 

Note: If you choose to continue calculating the interest rate, it will change the interest amounts calculated on ALL repayments, not just the repayments after the change, hence it is not recommended.         

Remaining payments

After altering payments, a warning will appear if the principal does not clear by the original loan end date. If the amount is not fully paid out, update the payment schedule or manual interest rate to zero out the variance. 

If the amount is paid out early, zero out all remaining payments and admin charges, if applicable. 

Warnings

Ensure you only update lines already in the 'Schedule' and do not manually insert extra lines. This will cause the formulas to break and no amounts calculated will be correct. 

Additionally, the 'Schedule' does not roll over. If you have made irregular payments, use Excel's 'Move or Copy' function to insert it into your new binder. 

Example

As an example, if a client paid an irregular payment of $2,000 on 2 July 2023, instead of $1,509 on 1 July 2023 as per the schedule, we would need to update the interest rate and the associated line in the 'Schedule'. 

First, navigate to the 'Interest Rate' section and copy over the Calculated Interest Rate to the Manual Interest Rate to ensure all interest amounts remain the same on all previous repayments. 

Next, scroll to the 'Schedule' section of the worksheet and update the Payment Date (for the irregular payment and following regular payment) and Payment Amount.

Once this is complete, scroll to the bottom of the 'Schedule' and adjust the remaining payments using the methods listed under the 'Remaining payments' section above. 

In this case, we will take the sum of the figures outlined in red above and put it as a 'repayment' in column N to ensure the 'Schedule' zeros out.