Sample Workbook
The easiest way to try the features of Home Loan Interest Manager is to open the sample workbook and follow the examples outlined below.
To open the workbook select from the menu.
If you are new to Home Loan Interest Manager you may wish to read the Where to Start help topic first.
Quick Tour
One of the main reasons to use Home Loan Interest Manager is for the information provided in the summary screen and the reports. Therefore, the best place to start in the sample file is with the summary screen.
Open the sample workbook now, if it is not already open. In the account list, make sure the "Home Mortgage - Mrs A and Mr A Citizen" is selected.
Within the three panels of the summary screen, you can find the following:
- Latest Information - providing a quick financial overview of the loan. Looking at the sample loan, the closing balance from the last statement was 107,776.72 owing to the bank, the current interest rate is 7.92% and there are 28.95 years remaining to pay on the loan.
- Account Contents - shows the volume of inforamtion loaded for the loan. The sample loan currently contains 2 statements, 96 transactions and 7 interest rate changes. You can see the indivdual details for each of these by selecting to View Statements, View Transaction and View Interest Rates respectively.
- Summary Payout Plan (Pro Version Only) - provides up to date information on how much you can save if you meet your target loan term. The information is updated every time you enter new loan details, so you can always see how much you can save, and what you have to do to achieve it. From the sample loan, you can see that by changing the loan term from 30 years to 25 years you can save approximately 32,107.76 in interest. To achieve this saving you have to increase the weekly repayments by 9.87. If a payout plan is not applicable to all your home loans and mortgages then you can select to "Hide Payout Plan" in the toolbar and it will remain hidden.
- Report Launcher - convinient way to launch any of the reports for the current account.
Now that you have covered the summary screen it is time to take a look at the reports.
From the Report Launcher click on one or more of the reports that you are interested in. You will notice that the header of each report contains the report name and the account name, so that it is easy to identify what you are reporting on.
If you wish to run a report for a different account:
- Make sure all reports are closed.
- Select the required account in the main Accounts list.
- Re-run the report.
For example, click on the "Line of Credit - Mrs A Citizen" account, then run the report.
For a detailed explanation of each report, please refer to the Reports help topic.
Accounts, Statements, Transactions and Interest Rates
Next we will have a quick look at the screens that let you enter your loan details.
- In the main Accounts list, select the account "Home Mortgage - Mrs A and Mr A Citizen".
- Click on the Edit loan button (under the Accounts heading) to view the account details for the selected account. The bottom part of the screen shows the offset account linked to this loan. Click on Cancel when done.
- Select to view the statements and click on the Edit statement button (under the Statement heading) to view the statement details. Click on Cancel when done.
- Double click on a statement to view the transactions for the statement (or select the Transaction view tab).
- Select a transaction and click on the Edit transaction button (under the Transaction heading) to view the transaction details. Click on Cancel when done.
- Select the Interest Rates view screen, and click on the Edit interest rate button (under the Interest Rate header) to view the details for the selected interest rate. Click on Cancel when done.
Other Exercises
Try the following:
- From the menu choose . Edit a category, add a new category and add a wildcard.
- From the menu choose to a Comma Separated Values (CSV) file. Enter a filename and export the workbook. This new file can be opened in other applications on your machine (eg in a spreadsheet, in a word processor, etc).
You have now covered some of the major features of Home Loan Interest Manager.
The remainder of this help topic goes into the details of each account in the sample workfile and how they were set up. If you have the time you can work through them now, or alternatively you can refer to them if you have trouble setting up your own loan information.
If you need help creating your own workbook, refer to My First Workbook.
Contents of the Workbook
The sample workbook contains three accounts:
- Home Mortgage - Mrs A and Mr A Citizen
- Home Mortgage Offset - Mrs A and Mr A Citizen
- Line of Credit - Mrs A Citizen
The following section discusses in detail the accounts from the sample workbook. For each account the loan properties are described first, followed by the way it has been handled within Home Loan Interest Manager
Example 1 - Home Mortgage - Mrs A and Mr A Citizen
This account is the mortgage home loan (and offset account) held in joint names by Mrs A and Mr A Citizen, with the following properties:
- The bank approved the loan for a maximum of 105,000 on the 10th of June, with a loan term of 30 years. The loan was drawn down on the 16th of June.
- The bank set up an offset account, with the same interest rates as the mortgage and with a balance that is 100% offset against the mortgage.
- The interest rate started at 7.82% and changed 7 times over the 12 month period.
- The bank has a policy of applying 365 days in their interest rate calculations (even in leap years).
- A regular deposit of 340.00 per fortnight has been set up from their savings account to the loan.
- Statements are received every six months and are not available electronically.
- The bank charges interest once a month, from the 14th of one month, to the 13th of the next month. The interest transaction is normally debited to the loan on the 14th of the month.
- The account allows Mrs A and Mr A Citizen to make additional repayments on the loan at any time, and any extra funds deposited in the account can be withdrawn at a future date when required. They took advantage of this feature during the 12 month period, selling a considerable amount of their share market stocks and placing the proceeds into the loan. At the end of the 12 month period they decided to invest in real estate, so they withdrew the money to help buy an investment property.
Within Home Loan Interest Manager the loan has been set up as follows:
- the mortgage account and you will see the loan has been set up with a Loan Date of 10 June 2000 (the day the loan was established), a loan term of 30 years and a Loan Amount of -105,000
- The account Interest Details - Days In Year has been set to 365. The Interest Details - Interest Calculated Up To Days Prior to the Transaction has been set to 1. This is because the interest is normally debited on the 14th of the month for the period up to the 13th of the month (that is, one day before the interest transaction).
- Close the account edit screen and select the option for the account. An interest rate has been set up with a start date of 10 June 2000 for 7.82%. The other 7 interest rate changes have also been entered.
- Select the option for the account. Each statement was created manually for the correct date period, then the transactions were typed in manually.
- Select the option for the account. Transactions that appear on the same date automatically had their sort order set to keep them in the same order as they appear on the statement. Refer to Transaction Sort Order for more information.
- The regular payments of 340.00 per fortnight have been allocated a category of DEPOSIT – Regular Deposit. The large amounts from the stock sales have been allocated a category of DEPOSIT – Deposit. By allocating different categories, they will total separately in the comparison reports. Alternatively, a new category DEPOSIT – Stock Sale Proceeds could have been set up and allocated to these transactions.
- The interest transactions on the 16 October 2000, 15 January 2001 and 17 April 2001 have each had their Date Interest Calculated Up To manually set to the 13th of the month. This is because the bank processed the transaction late, so the default of 1 day prior to the transaction date is not appropriate.
- Select the offset account in the Account table and select . You will see the offset account was set up as a new account with a Loan Date of 10 June 2000, no loan term, a Loan Amount of 0.00, and Days In Year set to 365.
- To make the account an offset account (Pro Version Only), the Offset Account flag was ticked, the allowable offset deduction was set to 100% and Loan for this Offset Account was set to the mortgage. This links the two accounts together for the purpose of interest calculations.
- From the view the option was used to copy all the rates from the mortgage to the offset account.
- On 15 April 2001 a deposit transaction for 10,000 was entered into the Offset Account. This is the only transaction on the account.
Select the mortgage account and open the Balance Checker Report, you will see:
- All the balances on this account match the Home Loan Interest Manager estimates.
Refer to Balance Checker Report for more information
If you open the Interest Checker Report, you will see:
- If you are using the Pro version then the report automatically includes the benefit of the offset account in all the Home Loan Interest Manager's estimates. To see a breakdown of the amount of interest the offset account is saving you, click on the Offset Account tab on the report. It can be seen that the 10,000 deposited in the offset account on 15 April 2001 saved 54.19 in interest. If you are not running the Pro version then the amount will be reported as an error.
- Select the Interest Paid tab in the report and you will see that three of the notes (right hand column) inform you that the Date Interest Calculated Up To has been manually set to the 13th of the month. A quick scan down the Interest End Column will confirm that all the date ranges end on the 13th.
- Looking at the Interest Start Date, all the columns start on the 14th of the month, except the bottom transaction. There is a difference of 108.54 between the interest charged and Home Loan Interest Manager's estimate. Further investigation shows that the bank has incorrectly charged interest from the date the account started (the 10th of June) and not the date the loan was drawn down (the 16th of June).
- How much should the bank refund to you if you discovered the error today? If you are running the Pro version, open up the Refund Calculator Report to find out.
Refer to Interest Checker Report and Refund Calculator Report for more information.
If you open the Daily Interest Report (Pro Version Only), you will see:
- The loan is currently costing 19.69 a day (refer to the right hand column, Interest Payable). This is the difference between the 21.86 interest on the mortgage, and the 2.16 saved by having 10,000 in the offset account.
- The report also shows the daily balance for every day of the loan, the interest charged, and the calculated daily rate.
Example 2 - Line of Credit - Mrs A Citizen
This account is Mrs A Citizen's stock investment loan, with the following properties:
- The bank approved the line of credit for a maximum of 180,000 on the 4th of May, with a loan term of 20 years. The funds have been withdrawn each time stocks were purchased.
- The interest rate started at 0.00% until the first withdrawal was made, after which time the bank's standard investment rate of 7.32% applied. However Mrs A Citizen has managed to negotiate a 0.50% discount off the standard rate, so the true rate she pays is 6.82%.
- The bank has a policy of applying 365 days in their interest rate calculations (even in leap years).
- Interest is charged once a month, however it is not guaranteed to be charged on the same day each month, and there is no relationship between the date the interest rate is charged and the period it was charged for. Note: This information can normally be sourced from the Lender's Terms and Conditions, the Loan Contract, or by looking through a few statements to see when the interest was charged.
- Mrs A Citizen has access to her transactions in electronic format from the Internet.
Within Home Loan Interest Manager the loan has been set up as follows:
- the account and you will see loan account has a Loan Date of 04 May 2004 (the day the credit was approved), a loan term of 20 years and a Loan Amount of -180,000.00.
- The account Interest Details - Days In Year has been set to 365. The account Interest Details – Interest Calculated Up To has been left as the default value of 1 (although in this case it won't be used as there is no relationship between the interest transaction date and the interest period).
- Close the account edit screen and select the option for the account. An interest rate has been set up with a Start Date of 04 May 2004 for 0.00%.
- An interest rate has been set up with a Start Date of 12 May 2004, a Lender Rate of 7.32% and a Discount Rate of 0.50%, resulting in an actual rate of 6.82%. This interest rate entry automatically created a transaction in the appropriate statement to show the change in interest.
- Select the option for the account. Each statement was created manually for the correct date period, then the transactions were imported electronically via the import wizard.
- Select the option for the account and select the statement in the drop down list that starts on 04 May 2004. On 12 May 2004 two withdrawals were made to purchase stocks. As the transactions are on the same date, a Sort Order of 1 and 2 have been manually allocated to them so that they appear in the same order as the statement (if you change the sort order, you will notice the impact on the estimated balance). Refer to Transaction Sort Order for more information.
- The first interest transaction was received on 01 June 2004. So that it is included in the interest checker report, it has been assigned to the INTEREST - Periodic Interest category. The transaction has been manually edited and the Date Interest Calculated Up To has been set to 30 May to reflect the date the interest period ended.
- The interest transactions for 01 July 2004, 02 August 2004 and 01 September 2004 have each been manually edited in a similar way.
If you open the Balance Checker Report, you will see:
- All the balances on this account match the Home Loan Interest Manager estimates.
Refer to Balance Checker Report for more information
If you open the Interest Checker Report, you will see:
- There is one note on this account pointing out that a manual adjustment has been made to the interest transaction dated 02 August 2004. To verify this, close the report, find the 02 August 2004 interest transaction and edit it to view the information. Click the Cancel button when done. Refer to Rounding Errors for more information.
- There are no interest errors on the account.
Refer to Interest Checker Report for more information.
Planning:
Mrs and Mr Citizen have decided to try and reduce the total loan term from 20 years to 10 years. They have set up a target payout term of 10 years. The Summary Payout Plan in the Summary view sreen provides them with up to date estimates of how to meet their target. For more detailed information if you open the Payout Plan Report (Pro Version Only), you will see:
- A 10 year payout target means that the Citizens have to raise their monthly payment from 895.28 a month to 1,374.89 a month (an increase of 479.62). If they meet their target they will reduce their outstanding interest bill by 52,181.45.
Refer to Payout Plan Report for more information
Mrs and Mr Citizen are concerned that the interest rate on their line of credit could increase from 6.82% to 7.32% (0.5% interest rate rise) and want to start budgeting for this change. If you open the Interest Rate Rise Report (Pro Version Only) and enter a New Interest Rate of 7.32%, you will see:
- The monthly repayment for the Citizens will rise from 895.28 to 929.99, resulting in an increase of 34.72 per month.
Refer to Interest Rate Rise Report for more information
Advanced Exercises
The following exercise will show you how to create a new statement, load the statement transactions from a file, and then run the auto-matching tool.
The new statement is for the "Line Of Credit - Mrs A Citizen" account, and the details are:
New Statement
- Start Date of 28 September 2004
- End Date of 26 October 2004
- Reporting Year of 2004
- Opening Balance of -115,986.72
Here is what you have to do:
- First of all, make sure you have selected the account "Line Of Credit - Mrs A Citizen", in the main Account list.
- Then select the view and click on the button.
- Enter the details listed above into the "New Statement Details" screen and click Ok when done. The new statement will now have been created, and you now need to add transactions to it.
- To import the transactions from a file, start the "Import Transactions" wizard, browse and select the Mrs A Citizen Transactions.csv file, then select the "Import" option. Follow the "Next" prompts through the wizard until the Last stage and select "Finish". Note that this file contains the entire transaction history for the loan, however the wizard will only import transactions that fall within the statement date range. Refer to Importing Transactions for more information. You will be asked to confirm that you only want to import the two transactions in the date range. Select Yes.
- Once the two transactions have been loaded, you will be asked if you want to run the Auto-Matching tool. This will only match one of the transactions, the other you will need to manually assign the "INTEREST - Periodic Interest" category, edit the transaction and set the Date Interest Rate Calculated Up to 29 September 2004.
You can then see the results in the Balance Checker and Interest Checker Reports.