How to access the binder

To use the Loan Repayment Calculator binder, create a new binder selecting 'Loan Repayment Calculator' - see the knowledge article: How to create a binder, for more information.      

Using the Loan Repayment Calculator binder

When the binder is opened in Excel, the 'H12 Loan Summary' record template is automatically inserted into the binder. This acts as a summary worksheet for all chattel mortgage and hire purchase loans. 

The Loan Repayment Calculator worksheet determines the chattel mortgage and hire purchase interest amounts and minimum repayments for the financial year.

To add the record template, 'H13 Hire Purchase Schedule' or 'H11 Chattel Mortgage Schedule', you have two options: 

1. Add the record template against the loan directly and select the loan as the reconciliation field (recommended). 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. 

2. 'Accept' the recommendation in the Summary section of the index sheet. You will be prompted to name the worksheet - we recommend you use the name of the loan. 

Note: In either case, ensure you choose to reconcile against the correct field. For example, current liability, non-current liability, etc. as this will be used to determine whether the worksheet reconciles at the end. 

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.         

Using the loan summary worksheet

To show a summary of all loans, navigate to 'H12 Loan Summary' and right-click anywhere along the top of the task pane, clicking Refresh. 

This will bring through the interest and current and non-current portions for each separate loan, as well as a total for all loans.   

How to insert the worksheets in the Loan Repayment Calculator binder against loan balances in the Accounts & Tax binder

Navigate to the relevant Accounts & Tax binder for the client and open it in Excel. As you would add any other record, select the plus icon to the right of the account. 

The below pop-up screen will appear, select Link to External Value, External Field. 

Search for the relevant Loan Repayment Calculator binder and select it.

Select the relevant worksheet you wish to reference against the account. 

Select the reconciliation field. 

The below pop-up will appear. Input the record title, select the status of the record template and confirm the reconciliation details are correct.  

Click Save and Close. 

This worksheet will populate against the loan account and show a green tick if the amounts reconcile.