This template enables users to compile a loan account statement for any loan that is repaid on a monthly basis and subject to daily interest calculations. The template was specifically designed for home loan calculations but can be used for any loan where the principles of monthly repayment and daily interest are applied.

The main purpose of this template is to calculate loan balances and interest amounts based on the same calculation methodology that is applied by most financial institutions. This template can therefore be used to recalculate the amounts on the loan account statements that are received from financial institutions and can also be used to compile loan account statements for informal loans. The template accommodates multiple interest rate changes (variable interest rates), increased instalments and ad hoc loan repayments.

Note: This template is a simplified version of our Loan Calculation & Analysis template. The difference between the two templates is that the Loan Statement template only includes the statement and summary features and does not include a comprehensive forecast of interest charges and loan account balances for the remaining loan repayment period. We also recommend downloading our free Loan Amortization template if you only want to compile a basic loan amortization table.

Important: If you use this template to compile loan account statements for your customers, the loan account statement and summary should be converted to a PDF format before being e-mailed to customers. You are not allowed to send the template in its current file format (Excel file) to your customers because this would fall outside the scope of a single user license and result in a copyright infringement.

Worksheet Summary

The main purpose of each worksheet is as follows:

TransCode - includes the default transaction types that need to be selected when entering loan account transactions. Additional transaction types can also be added to the default list of transactions codes.
Statement - all loan account transactions should be recorded on this sheet. All the interest and loan account balance calculations in this template are based on the transactions that are recorded on this sheet. Note that the Statement sheet includes 12 columns but only four of these columns require user input (the columns with the yellow column headings).
Summary - includes a monthly summary of loan account transactions that is calculated from the Statement sheet. No user input is required on this sheet.

Note: The template includes a lot of complex formulas and has been designed within a specific worksheet structure. We therefore do not recommend adding additional rows or columns within the existing worksheet framework because it may result in errors or inaccurate calculations being encountered. If you therefore want to add your own calculations to the template, we recommend inserting a new worksheet for this purpose.

Template Set-up

Aside from recording loan account transactions, the template does not require a lot of set-up. We recommend that you start by reviewing the transaction types on the TransCode sheet. The default template includes 7 transaction types of which the first 5 should be left unchanged. Each of these transaction types has a specific purpose and if you therefore change the type of transaction that is associated with the default transaction code, you may encounter inconsistencies in the template calculations.

The Admin Fee and Insurance Premium transaction codes have been added as separate transaction types because these costs are frequently included as separate line items on home loan account statements and usually form part of the monthly loan repayment amounts. If you do not require these items, you can delete them or replace them with other costs that are included on your loan account statement. Note that both transaction type number 6 and 7 are reported separately on the Statement and Summary sheets and if you change the default transaction type descriptions, the new descriptions will be included in column C on the Statement sheet but you have to enter the new descriptions in columns G and H on the Summary sheet.

All the transaction types on the TransCode sheet are included in list boxes in column B on the Statement sheet. Additional transaction types that are added to the default list of transaction types are also available for selection from these list boxes. You will therefore be able to enter the transactions relating to these transaction types on the Statement sheet but note that all transaction types with transaction codes greater than or equal to 8 are grouped together in one column on the Summary sheet (column I).

Note: The contents on the TransCode sheet have been included in an Excel table. A new transaction code can therefore be created by simply entering the new code in the first empty cell below the table in column A. The table will then be extended automatically to include the new transaction type.

Loan Account Transactions

All loan account transactions need to be recorded on the Statement sheet. This sheet includes 12 columns but only four of these columns require user input (columns with a yellow column heading). The contents of this sheet have been included in an Excel table which means that the columns with light blue column headings (containing formulas) will automatically be copied for all new transactions that are recorded.

When you therefore record any transaction, you will need to enter a date (in column A), select a transaction type from the list box in column B, enter a new interest rate (only if an interest rate change transaction is being recorded, otherwise enter 0%), enter a transaction amount in column E and the formulas in all the other columns will automatically be copied from one of the existing rows in the Excel table.

Note: New transactions can be recorded by simply entering a transaction date in the first blank cell below the Excel table in column A. The table will then automatically be extended to include the new transaction and all the formulas in the columns with a light blue column heading will automatically be copied into the new transaction row.

The following important guidelines should be followed when recording transactions on the Statement sheet:

• The first transaction that is recorded on this sheet should always be an interest rate change transaction (transaction type 5) and this transaction should be recorded on the same date as the first transaction on your loan account statement. The interest rate that is applicable to the loan should be entered in column D and the transaction amount in column E should be nil (the transaction amount for all interest rate change transactions should always be nil). This entry establishes the interest rate that should be used to calculate interest from the beginning of the loan period.
• All the transactions that are recorded on the Statement sheet should form part of a continuous cell range. There should therefore be no empty rows between transactions, otherwise the loan account balances and therefore also the interest calculations will be inaccurate.
• All transactions should be recorded or sorted in an ascending date sequence (oldest transactions first). You'll notice that if you enter a transaction that is out of sequence, the transaction date will be highlighted in red. This error can be corrected by simply sorting all the transactions on the Statement sheet in an ascending order by the date in the first column. By sorting the transactions in the correct order, you will ensure that the date sequence is rectified and that all interest calculations are accurate.
• The monthly interest on the outstanding loan account should be capitalized on the last day of each month. In order to accomplish this, simply record an interest capitalization transaction (select transaction code 4) for the last day of each month. The transaction amount in column E should be nil because the monthly interest capitalization calculation is performed automatically through the formula that has been entered in column F. This interest calculation only applies to transaction type 4 - if you therefore specify the incorrect transaction type, no interest will be capitalized to the loan at the end of the appropriate month. Also note that the date that is entered should be the last day of the month - if you enter any other date, the interest calculation will not be accurate. The transaction date in column A will be highlighted in orange if the incorrect interest capitalization date is specified.
• It is imperative that you don't replace any of the formulas in the columns with light blue column headings with alternative values because it will result in inaccurate calculations. We recommend that you save the template under a default file name after downloading it and save each loan statement under a different file name. You will then be able to revert back to the original template if you delete any of the formulas by accident.
• All transaction amounts except for loan repayments and ad hoc repayments should be entered as positive values. Loan repayments and ad hoc repayments should be entered as negative values - if you enter positive values for any repayment type transactions, the transaction amounts will be highlighted in orange.

The following section covers the default transaction types that should be recorded on the Statement sheet:

Loan Advances

Loan advances are amounts that are received from the financial institution who grants the loan. You can record as many loan advances as required by simply entering the appropriate transaction date, selecting transaction type 1 and entering the appropriate loan advance amount in the Transaction Amount column (column E). Loan advances should be entered as positive amounts.

Loan Repayments

For the purpose of distinguishing between compulsory and voluntary loan repayments, we have assigned scheduled loan repayments and ad hoc loan repayments to different transaction types. Scheduled loan repayments are determined by the terms in a loan agreement, while ad hoc loan repayments are effected at the borrower's own discretion.

Scheduled loan repayments need to be recorded on the Statement sheet by entering the appropriate transaction date, selecting transaction type 2 and entering the appropriate loan repayment amount as a negative value in the Transaction Amount column. Note that if you enter a positive loan repayment amount, the amount will be highlighted in orange in the Transaction Amount column until it is rectified.

Ad Hoc Repayments

The template accommodates an unlimited number of ad hoc loan repayments. It also makes no difference whether the ad hoc repayment is an increased instalment or a lump sum repayment.

Ad hoc repayments can be recorded on the Statement sheet by simply entering the appropriate transaction date, selecting transaction type 3 and entering a negative amount in the Transaction Amount column. If you enter a positive ad hoc loan repayment amount, the amount will be highlighted in orange until the error is rectified.

Interest Capitalization

The interest calculations in this template are based on the daily loan account balances. Transactions are included in the daily loan account balance as if the transactions occurred at the beginning of the day. For example, if a loan repayment is dated on the 2nd of January, the repayment is deducted from the loan balance of the previous day and included in the interest calculation from the 2nd of January. For month end interest capitalization purposes, the loan repayment on the 2nd will therefore be included in the monthly interest calculation for a period of 30 days.

All interest calculations are automatically performed based on the transactions that are recorded on the Statement sheet. As we've mentioned before, it is imperative that all transactions are recorded in the correct date sequence (ascending date order), otherwise the interest calculations may not be accurate. All interest calculations are performed based on the balances calculated in the Transaction Closing Balance column by applying the appropriate interest rate and the number of days that the balance is in effect to this amount.

The calculated interest is capitalized to the loan account balance when you enter an Interest Capitalization transaction for the last day of the appropriate calendar month. Note that if you don't enter this transaction, the interest calculated for the particular month will not be added to the loan account balance and your loan account statement will not be accurate! It is therefore imperative that you add an Interest Capitalization transaction to the Statement sheet for every month that forms part of the loan period.

Also note that this transaction has to be dated on the last day of the month - if you use any other date, the interest calculation will not be accurate. We have implemented conditional formatting in column A to highlight the interest capitalization transactions that are not dated on a month end date in orange. When you change the transaction date to the appropriate month end date, the formatting is automatically removed.

Interest capitalization transactions can be recorded by entering the appropriate month end date, selecting transaction type 4 and entering a nil amount in the Transaction Amount column. The calculated interest is reflected in column F and is automatically added to the outstanding loan balance in column G.

Interest Rate Changes

This template accommodates an unlimited number of interest rate changes. An interest rate change can be recorded on the Statement sheet by simply entering the effective date of the interest rate change in column A, selecting transaction type 5, entering the appropriate new interest rate in column D and entering a nil amount in the Transaction Amount column. You'll notice that the interest rate in column H (used in all interest calculations) will change to the new interest rate that has been recorded. Interest on all subsequent transactions will be calculated based on the new interest rate (until the next interest rate change transaction is recorded).

As we've mentioned before, the first transaction that should be recorded on the Statement sheet is an interest rate change transaction. This transaction establishes the interest rate that should be used to calculate interest from the beginning of the loan period. If you omit this transaction, the interest rate that will be used to calculate interest on the initial loan balance will be nil and no interest will therefore be calculated and capitalized to the initial loan account balance until an interest rate is defined.

Interest rate changes can only be recorded by using transaction type 5 - if you enter a new interest rate in column D and select any other transaction type, the interest rate change will not have any effect on interest calculations.

Other Loan Costs

As we've mentioned before, other loan costs (like admin fees and insurance) can be recorded by using transaction codes 6 and 7 or adding the appropriate transaction codes to the TransCode sheet and selecting the appropriate transaction type from the list box in column B on the Statement sheet. These transactions usually relate to costs that are charged to the loan account and should therefore be entered as positive values in the Transaction Amount column. Financial institutions typically include these costs in scheduled loan repayments and the costs are therefore deducted from the loan account balance when you record scheduled loan repayments (transaction type 2) on the Statement sheet.

Loan Account Statement

All loan transactions should be entered on the Statement sheet in accordance with the guidance provided in the previous section. After reading through the previous section, you should therefore be able to record all the appropriate transactions on the Statement sheet and we'll now provide guidance on the calculated columns (columns with a light blue column heading) on this sheet.

The transaction description in column C has been included on the Statement sheet so that it is easy to identify the transaction types that are selected from the list boxes in column B. The list boxes only include the transaction type numbers - we've therefore included the description in column C to enable users to review the transaction type that has been selected.

The calculated interest in column F represents the amount of interest that is capitalized to the loan account at the end of every calendar month. All scheduled loan repayments consist of an interest and capital portion - this column includes the interest portion that is calculated based on the daily loan account balances during the particular monthly period. This column should therefore only contain one calculated interest amount per calendar month.

The monthly interest amount that should be capitalized is the total of all the accrued interest amounts in column L for the particular month. The accrued interest is calculated from the interest rate in column H, the interest days in column J and the transaction closing loan balance in column K.

The outstanding loan balance on each transaction date is calculated in column G. This amount represents the capital balance that is still outstanding on the loan.

The monthly capital portion of the loan repayments can be calculated by adding the interest and other costs together and deducting the sum of the scheduled loan repayments and the ad hoc loan repayments from this amount. Note that it may therefore be easier to calculate this amount from the totals on the Summary sheet.

Loan Account Summary

The Summary sheet includes a complete breakdown of the movements on the loan account for all monthly periods. All the totals on this sheet are calculated from the transactions that are entered on the Statement sheet. No user input is required on this sheet - users are only required to extend the Excel table for the appropriate number of new rows in order to add the required number of monthly periods to the summary.

Note: The contents of this sheet have been included in an Excel table. The table can be extended by clicking the arrow in the bottom right corner of the table and dragging the table border downwards for the required number of rows. All the formulas that are included in the table will be copied automatically.

The first monthly period on the Summary sheet is determined by the transaction date of the first transaction on the Statement sheet. The transaction date in row 4 on the Statement sheet therefore determines which monthly period is displayed in the first row of the Summary sheet. All subsequent monthly periods on the Summary sheet are calculated based on the first monthly period.

The Loan Advance column is calculated based on all the transactions with a transaction type of 1 on the Statement sheet. Scheduled loan repayments (transaction type 2), ad hoc repayments (transaction type 3), interest capitalized (transaction type 4), admin fees (transaction type 6), insurance premiums (transaction type 7) and other costs (all transaction types greater than 7 that have been added to the TransCode sheet) are also calculated based on the transaction types of the transactions that are recorded on the Statement sheet.

As we've mentioned before, an interest capitalization transaction should be recorded on the month end date of every monthly period that forms part of the loan period in order to calculate interest accurately. If this transaction has been omitted for any monthly period, the loan account balances and subsequent interest calculations will be inaccurate. We therefore recommend that you review the loan summary for confirmation that an interest capitalized amount (column F) has been calculated for all the months that are included on the summary. If a nil value is displayed in any month, simply enter an interest capitalization transaction (transaction type 4) for the particular month on the Statement sheet.

Note: We have added conditional formatting to the Interest Capitalized column in order to highlight cells that contain a nil amount in orange.

The Summary sheet can be used to analyze all loan account transactions on a monthly basis for the duration of the loan period. It is therefore an extremely useful tool for reviewing the loan account transaction and balance history.