Omni – Loan Analysis

The Loan Analysis program is accessed via the menu item ‘Direct Investments, Loan Analysis’. 

Proposed Loans

1. When clicking the ‘Proposed Loan’ button, a screen is displayed asking for the start date, amount, interest rate and payment frequency. The interest rate defaults to 1% of base rate.

2. Future equal payments (comprising differing amounts of capital and interest) are projected using the interest rate as a true annual rate of interest.

3. Each monthly repayment is rounded to nearest penny.

4. The balance at end of loan is usually zero, plus or minus up to 10p, due to rounding of each payment.

5. The program allows monthly, quarterly or yearly repayments.

6. The results screen shows the required minimum payment. It also gives a schedule showing all future payments broken down between interest and capital (which also proves that the capital elements of each payment finally repay the loan exactly).

7. The schedule of future payments can also be sent to Excel. View example.

8. When sending future payments to Excel, there is a blank row to divide each future loan year. There are also totals of repayments, interest and capital for each future year.

Existing Loans

9. The ‘Review Past Period’ button is used for existing loans where payments are made on a set basis but the exact amount of interest and capital are recalculated at the end of each year (to allow for the actual date of each payment and any arrears). The balance at the end of the period is calculated. The Future payments are also projected, as above.

10. The ‘Future Payments’ button is designed for loans where:

a. the balance at the current review date has been calculated previously and stored (in Investments, Prices, Scheme Specific)

b. it has been decided that this is balance is correct and no adjustment is needed for actual dates of payment

Using that balance and the new interest rate, the future payments are recalculated, as above.

11. If the Start date of the loan is within the last month, the program assumes that the future payments for the loan are being generated and so it doesn’t ask for the date that the current review period ends.

Non-Standard Payments

12. The ‘Future Payments’ option can be run using the payments calculated by Omni (as the minimum necessary to clear the loan over the outstanding term). Alternatively, the ‘Use Calculated Future Payments’ box can be unticked and any total repayment amount can be entered.

13. If the regular payment is higher than needed,

a. the balance is set to zero when the loan has been repaid (no negative amounts).

b. in the month in which the loan is cleared the total repayment is restricted to avoid an overpayment

c. on the results, the remaining months are still shown, with 0 amounts, to highlight the fact that the loan has been repaid early

d. when creating expected transactions, when a zero payment is found no more transactions are created

14. If the regular payment is lower than needed, the process continues to the end of the loan period and then just shows the final amount needed to clear the loan in the ‘Balance’ column.

Generating Expected Transactions

15. After calculating the future payments, Expected Bank Transactions can be created for the next year. A single ‘Loan Repayment’ transaction is created for the total payment due on each date (the total of capital and interest).

16. The amount of capital included within the payment is also stored so that when the Expected Transaction is converted to an actual bank transaction, the capital amount can be seen (by clicking ‘Add Details’).

17. If the interest rate stored for the loan is 3% or less then the analysis assumes that the rate is a margin above base rate (reset each year). Otherwise it assumes that the rate is fixed.

Exporting to Excel

18. There are now 1 or 2 tabs on the Excel spreadsheet. One for future payments and possibly one for past payments on an existing loan.

Storing Balances

19. Once the payments have been calculated, the balances can be stored (but not for proposed loans).

20. If the Review Past Payments option was used, the balance at the current review date can be stored.

21. For all existing loans, the projected balance in one year’s time can be stored. This would be relevant for loans where the interest rate is fixed for the year and all repayments are made on the due dates. This forward balance can then form the basis for next year’s recalculation of the loan payments.

Consequential Changes to Other Omni Functions

22. The capital element of each loan payment is imported automatically from the Expected Transaction. The history of the capital element of each payment is then visible on the Loan Payments screen (Direct Investments, Loan Payments).

23. Accounts data. The loan repayments are listed in the Accounts Data with the interest and any capital element shown in separate columns. The summary identifies the total interest element and the total capital repayments in the period. The interest is included in the Income and Expenditure account but the capital elements are not.