This template enables users to compile an investment account statement for any fixed term deposit. The investment account statement includes interest calculations that are based on daily investment account balances and capitalized to the investment account on any user defined date. The template provides for multiple investments of funds, withdrawal of funds, interest payments, bank charges, interest rate changes and investment accounts can be compiled for any investment period.

The main purpose of this template is to compile investment accounts and calculate interest based on the same calculation methodology that is applied by most financial institutions. This template can therefore be used to recalculate the amounts on account statements that are received from financial institutions and can also be used to compile investment account statements for informal investments.

Important: If you use this template to compile investment account statements for your clients, the investment account statement and summary should be converted to a PDF format before being e-mailed to clients. You are not allowed to send the template in its current file format (Excel file) to your clients 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 investment account transactions. Additional transaction types can also be added to the default list of transactions codes.
Statement - all investment account transactions should be recorded on this sheet. All the interest and investment account balance calculations in this template are based on the transactions that are recorded on this sheet. Note that the Statement sheet includes 10 columns but only four of these columns require user input (the columns with the yellow column headings).
Summary - includes a monthly summary of investment 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.

Transaction Codes

The TransCode sheet contains a list of 6 default transaction types that can be used to record transactions on the investment account statement. The default transaction types should be left unchanged but you can add additional transaction types to the list if required. Each of the default 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.

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 7 are grouped together in one column on the Summary sheet (column H).

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.

Investment Account Transactions

All investment account transactions need to be recorded on the Statement sheet. This sheet includes 10 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 should be 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 investment account statement. The interest rate that is applicable to the investment 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 investment 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 investment 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.
• Interest is only capitalized to the investment account when you record an interest capitalization transaction (transaction type 4). 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 enter the incorrect transaction type, the interest will not be capitalized to the investment account.
• 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 investment account 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 withdrawals of funds, bank charges and interest payments should be entered as positive values. Withdrawals, bank charges and interest payments should be entered as negative values - if you enter positive values for any of these 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:

Investment of Funds

The Investments of Funds transaction type should be used for all deposits of funds into the investment account. This includes the initial investment of funds and all subsequent deposits into the investment account. You can record as many investments of funds as required by simply entering the appropriate transaction date, selecting transaction type 1 and entering the appropriate amount in the Transaction Amount column (column E). All investments of funds should be entered as positive amounts.  If you enter a negative amount, the amount will be highlighted in orange until the error is rectified.

Withdrawal of Funds

Any withdrawal of a capital amount from the investment should be recorded by using transaction type 2. Note that capital withdrawals exclude bank charges and interest payments. Withdrawals need to be recorded by entering the appropriate transaction date, selecting transaction type 2 from the list box in column B and entering the withdrawal amount as a negative value in the Transaction amount column. If you enter a positive amount, the amount will be highlighted in orange until the error is rectified.

Bank Charges

All bank charges should be recorded by entering the appropriate transaction date, selecting transaction type 3 from the list box in column B and entering the appropriate amount as a negative value in the Transaction amount column. If you enter a positive 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 investment account balances. All transactions except for interest capitalization transactions are included in the daily investment account balance as if the transactions occurred at the beginning of the day. Interest capitalization transactions are deemed to occur at the end of the transaction day. This means that the day on which the interest capitalization transaction is recorded is included in the interest that is calculated. This principle is best explained by way of an example.

Example: If we assume that the previous interest capitalization occurred on the 28th of February and a withdrawal of funds transaction is entered on the 5th of March, a bank charge transaction is recorded on the 25 of March and an interest capitalization transaction is recorded on the 28th of March, the following days will be included in the appropriate interest calculations:
the withdrawal of funds transaction will be deducted from the closing account balance of the 4th of March and interest on this balance will be calculated for 20 days (the 24th minus the 4th). Note that a transaction date of the 4th is used in the calculation because it is deemed that the withdrawal occurred at the beginning of the 5th and the transaction amount therefore effectively forms part of the closing balance on the 4th.
the previous month's closing balance will be included in the interest calculation of the current month for 4 days.
similarly, the bank charge transaction (recorded on the 25th) is deemed to have formed part of the closing balance of the 24th - this is why we deducted the 4th from the 24th and not the 25th.
interest capitalization transactions are however deemed to have occurred at the end of the transaction day. The interest calculation on the day that the interest capitalization transaction is processed (the 28th) will therefore be based on 4 days (the 28th minus the 24th) even though the bank charge transaction was recorded on the 25th.
if you add up the three interest calculations, you'll notice that the interest calculations covered the full 28 day period up to the 28th of March.

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. Interest is calculated by applying the appropriate interest rate to the previous transaction's closing balance and based on the number of days that is calculated by applying the transaction date principles that we've just explained.

The calculated interest accrual is only capitalized to the investment account when you enter an interest capitalization transaction. Note that if you don't record this type of transaction on the investment account, no interest will be capitalized to the investment account and your investment account statement will not be accurate! It is therefore imperative that you add an interest capitalization transaction to the Statement sheet on all the appropriate interest capitalization dates.

Interest can be capitalized on any date whether it is a month end date or any other day of the month. You can therefore capitalize interest on the last day of each month by simply entering an interest capitalization transaction on the last day of each month or you can capitalize interest on (say) the 27th of each month by simply entering an interest capitalization transaction on the 27th of each month. The template automatically calculates the days that should be included in the interest calculation based on the date of the previous transaction that has been recorded.

Interest capitalization transactions can be recorded by entering the transaction date, selecting transaction type 4 and entering a nil amount in the Transaction Amount column. The amount of interest that will be capitalized is calculated in column F and is automatically added to the investment account balance in column G.

Note: When entering an interest capitalization transaction, you should not enter a transaction amount in column E. If you enter any value other than a nil value, the value will be highlighted in orange until the error is rectified.

Note: If the interest that is earned on an investment account is paid out at regular intervals, you should still record the appropriate interest capitalization transactions and also record the interest payments by using transaction type 6. All interest payments should be recorded as negative amounts and will be included in a separate column on the Summary sheet. If you record an interest payment as a positive value, the amount will be highlighted in orange until the error is rectified.

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) changes 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).

Note: When entering an interest rate change transaction, you should not enter a transaction amount in column E. If you enter any value other than a nil value, the value will be highlighted in orange until the error is rectified.

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 investment period. If you omit this transaction, the interest rate that will be used to calculate interest on the initial investment account balance will be nil and no interest will therefore be calculated and capitalized to the investment account 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 the interest calculations. We have added conditional formatting to column D to highlight all input errors in orange.

Other Charges

As we've mentioned before, you can add additional transaction types to the TransCode sheet and select the appropriate transaction types from the list box in column B on the Statement sheet when recording transactions. All additional transaction types will be included in a single column (column H) on the Summary sheet.

Investment Account Statement

All investment account 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 capitalized interest in column F represents the amount of interest that is capitalized to the investment account on the appropriate transaction date. As we've mentioned before, an interest capitalization amount will only be calculated if transaction type 4 is selected in column B.

This amount is the total of all the accrued interest amounts in column J since the previous interest capitalization transaction. The accrued interest is calculated based on the interest rate in column H, the interest days in column I and the previous transaction's closing balance in column G. The investment account balance on each transaction date is calculated in column G.

Investment Account Summary

The Summary sheet includes a complete breakdown of the movements on the investment 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 on the Summary sheet. All subsequent monthly periods on the Summary sheet are calculated based on the first monthly period.

The Investment of Funds column is calculated based on all the transactions with a transaction type of 1 on the Statement sheet. Withdrawals of funds (transaction type 2), bank charges (transaction type 3), interest capitalization (transaction type 4), interest payments (transaction type 6) and other charges (all transaction types greater than 6 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, interest is only capitalized to the investment account when an interest capitalization transaction is recorded on the Statement sheet. If you therefore note that a nil value is displayed for any monthly period in the Interest Capitalized column (column F), it means that an interest capitalization transaction has not been recorded on the Statement sheet for the particular month. If an interest amount should have been capitalized, you can simply record the transaction on the appropriate transaction date

The Summary sheet can be used to analyze the movements on an investment account on a monthly basis. It is therefore an extremely useful tool for reviewing the investment account transaction and balance history.