This chapter describes how to set up the Asset Liability Management (ALM) – Auto-balancing process and demonstrates how the process works.
Topics:
· Introduction to Autobalancing
The auto-balancing process within Oracle Asset Liability Management simulates the following banking activities:
· Purchase of overnight funds to fill in cash shortfalls or sale of overnight funds to invest excess cash
· Roll net income into retained earnings
· Pay dividends
· Pay federal and local tax
· Accumulation of unrealized currency gain/losses in equity
The following set-up must be performed Oracle Asset Liability Management (ALM), before the generation of auto-balancing results:
1. Selection of all auto-balancing accounts in ALM Application Preferences. This includes:
§ Auto-balancing Asset (plug account)
§ Auto-balancing Liability (plug account)
§ Retained Earnings
§ Local Taxes
§ Federal Taxes
§ Dividends
§ Accumulated Translation Account
2. Definition of tax rates and dividend amounts (if applicable) per modeling bucket in the Active ALM Time Bucket rule. This includes:
§ Federal tax percentage
§ State tax percentage
§ Dividend amount <or>
§ Dividend percentage
3. Set-up of Product Characteristic information for the auto-balancing asset plug member and auto-balancing liability plug member.
NOTE:
Auto-balancing is done at Reporting Currency. For example, if reporting currency is JPY (Japanese Yen), then auto-balancing plug members need to be defined for JPY. Else, define Default Currency.
4. Verify that all auto-balancing products <leaf> members are tied to Common COA Ids with the correct account type designation. The following mappings are required:
|
Auto-balancing Account |
Account Type Attribute (through Common COA) |
|---|---|
|
Asset |
Earning Asset |
|
Liability |
Interest Bearing Liability |
|
Retained Earnings |
Equity |
|
Dividends |
Dividends |
|
Federal Taxes |
Taxes |
|
State Taxes |
Taxes |
|
Accumulated Translation Balance |
Equity |
5. Percentage taxable information is correctly defined in Product Characteristics for each product <leaf> member.
Auto-balancing available for the following types of ALM Processing:
· Dynamic Deterministic Product-only output
· Dynamic Deterministic Product Currency output with Consolidated option selected
· Earnings at risk output
NOTE:
Auto-balancing is done at Reporting Currency and would be populated in a consolidated table. When the output dimension is Product, and Consolidated table is not output, the Auto-balancing is output to RES_DTL and FSI_O_RESULT_MASTER. When the output dimension is Product Currency, the Auto-balancing is output to CONS_DTL and FSI_O_CONSOLIDATED_MASTER table.
The standard cash flow process generates results for the accumulated translation amount financial element for all modeling buckets and all product members assigned to the Current Rate method. The Current Rate method is one of three accounting methods available in the application for the treatment of currency gain/loss amounts.
The Accumulated Translation Account is a special auto-balancing member, assigned by the user within ALM Application Preferences. The balance for this product <leaf> member holds the aggregated accumulated translation amounts resulting from all product leaf members included in the process. The Accumulated Translation product must have an account type of equity.
The following steps are performed first because other auto-balancing processes rely on these results. The auto-balancing process generates the financial element rows, as follows:
1. Generate an aggregate value for the accumulated translation amount financial element for every modeling bucket and scenario. When aggregating data from product members, include results only from the following account types, with the sign as defined in the parentheses:
§ Earning Assets (+)
§ Other Assets (+)
§ Interest Bearing Liabilities (-)
§ Other Liabilities (-)
§ Equity (-)
2. Select the Current Balance for the Accumulated Translation Account from Result Master for the start date index equal to 0. Note that this data may not exist.
3. Transform the accumulated data into beginning, ending, and average financial element data for each modeling bucket and scenario.
§ The aggregated accumulated translation financial element will equal the ending balance for each bucket and scenario.
§ The beginning balance in all buckets beyond the first bucket will equal the ending balance in the previous bucket.
§ The current balance selected from Result Master will serve as the beginning balance in bucket 1 for all scenarios. If no data exists in Result Master, the beginning balance is zero.
§ The average balance will equal the beginning balance from the same bucket.
4. Because the Accumulated Translation Account is an Equity account and Equity accounts do not generate gap results, no data needs to be generated for Gap financial elements.
5. Within Result Master, the CUR_PAR_BAL and CUR_NET_PAR_BAL need to be generated for all scenarios and start dates. The CUR_PAR_BAL and CUR_NET_PAR_BAL will also be the same for all start dates/scenarios. To determine the proper balance amount for a start date, follow the subsequent logic:
§ If the start date is equal to a bucket end date, the Ending Balance for that bucket is used.
§ If the start date is equal to a bucket start date or falls within the modeling bucket, the Beginning Balance is used.
The generation auto-balancing assets and liabilities requires that a cash account is maintained over the modeling horizon. This cash balance results from principal, interest, and non-interest payments. For each modeling bucket, the process simulates overnight investment of cash or purchase (borrowing) of required cash.
The process requires the following accumulated values from the results:
Cash Balance: The cash excess/ shortfall for each modeling bucket from 0 to 240, or maximum modeling bucket.
Net Income: Net income for each modeling bucket from 1 to 240, or maximum modeling bucket.
Net Taxable Income: Net taxable income for each modeling bucket from 1 to 240, or maximum modeling bucket.
The following steps accumulate the cash balances in each modeling bucket.
1. For Cash Balance0, accumulate the CUR_NET_PAR_BAL + CUR_DEFER_BAL_C from Result Master for each scenario for the rows where start_date_index = 0 (the current position rows). The proper sign for each account type is listed as follows. Account types that are not listed are not be included in this accumulation.
§ Earning Asset (-)
§ Interest-bearing Liability (+)
§ Other Asset (-)
§ Other Liability (+)
§ Equity (+)
2. For Cash Balance1 to Cash Balance240, accumulate the ending balance financial element and the ending deferred balance financial element in each modeling bucket and each scenario. The proper sign for each account type is the same as shown earlier. Account types that are not listed are not be included in the accumulated values.
3. For Net Income1 to Net Income240, accumulate net income per scenario and modeling bucket and net taxable income (Net Taxable Income) per modeling bucket as follows. Accumulate the following financial elements:
§ Interest accrued net
§ Deferred runoff
§ Non-interest income
§ Non-interest expense
§ Realized currency gain/loss on net interest
§ Realized currency gain/loss on principal
§ Taxes
§ Dividends
Depending on the account type, either add or subtract these values. The sign for each account type is as follows:
§ Earning Asset (+)
§ Other Asset (+)
§ Off-Balance Sheet Receivable (+)
§ Interest Income (+)
§ Non-Interest Income (+)
§ Interest Bearing Liability (-)
§ Other Liability (-)
§ Off-Balance Sheet Payable (-)
§ Non-Interest Expense (-)
§ Taxes (-)
§ Dividends (-)
For Net Taxable Income, accumulate the same financial element values given earlier. Also, multiply each value by the taxable percent for the product <leaf> member. The taxable percent is found each product member, in Product Characteristics.
The Net Incomebplus the Cash Balance equals the available cash balance at each bucket end date through the modeling horizon.
Two additional events affect the balance of the cash account.
The cash account itself will earn or pay out interest.
1. At the end of each modeling bucket, taxes and dividends will be computed on the net income and net taxable income and paid out of cash.
The calculation of interest earned or paid out from the cash account must first be simulated, because this value also impacts the net income.
The first step is to derive an initial cash amount for every bucket end date, equal to Cash Balanceb plus Net Incomeb. Let's call this amount Total Cashb. Total Cash0, is equal to the starting cash amount, is equal to just the Cash Balance0. In addition to this variable, there are four additional variables.
· Total Cashb is the initial cash amount for every bucket end date, equal to Cash Balanceb plus Net Incomeb. Total Cash0, equal to the starting cash amount, is equal to just the Cash Balance0. This amount does not include adjustments for interest on the cash account or taxes and dividends paid out of the cash account.
· Daily Cashbn is the Daily cash balance for day n in each modeling bucket b. The variable n designates the day into the modeling bucket, for example, n = 1 is the modeling bucket start date.
· Net Interest On Cashb is the total interest earned over the modeling bucket b on the cash account.
· Incremental Cash is the incremental additional cash added due to changes in activity on the balance sheet. Because this value is reset for every modeling bucket, it does not require a b subscript.
· To Date, Cash is a running total of cash added from processing the cash account in previous modeling buckets. Because this value is a running total for all modeling buckets, it also does not require a b subscript.
· Rateb is the forecasted interest rate for the interest rate code associated with the Auto-balancing Asset or Liability account for the current modeling bucket and scenario. Note that the sign of the current balance will determine whether the interest rate code for the auto-balancing asset or the auto-balancing liability account should be used.
For each modeling bucket, interest can be calculated as follows:
N = number of days in the current modeling bucket
Description of formula to calculate the interest for each modeling bucket follows
After processing of cash account, taxes and dividends must be calculated and deducted from the cash account before calculation of the final cash balance.
StateTaxesb = StateTaxRateb * (NetTaxIncb + NetIntOnCashb)
FedTaxesb = FedTaxRateb * (NetTaxIncb + NetIntOnCashb )
Dividendsb = DivAmountb + DivRateb * (NetIncb + NetIntOnCashb )
The To Date additional cash must be adjusted for the start of processing of the next modeling bucket:
ToDateCash = ToDateCash + NetIntOnCashb - StateTaxesb - FedTaxesb - Dividendsb
The Ending Balance of the current modeling bucket must be adjusted for the cash paid out for taxes and dividends
DailyCashbN= DailyCashbN - StateTaxesb - FedTaxesb - Dividendsb
For calculation of the financial elements for each modeling bucket,
Beginning Balance = DailyCashb1
Ending Balance = DailyCashbN
Average Balance = For n = 1 to N: (
DailyCashbn ) / N
Interest Cash Flow = NetIntOnCashb
Interest Accrued = NetIntOnCashb
Beginning Net Rate = Rateb * DailyCashb1
Ending Net Rate = Rateb * DailyCashbN
Average Net Rate = Rateb
* DailyCashbn
NOTE:
The calculation of the average net rate is slightly more complicated than as shown earlier. The process must track the rate used for each day of the modeling bucket, whether it is an asset or a liability.
Once taxes and dividends have been calculated for the modeling bucket, the Ending Balance of the Retained Earnings account can be calculated as
RetainedEarningsb = RetainedEarningsb-1 + NetIncb + NetIntOnCashb - StateTaxesb - FedTaxesb - Dividendsb
Once all calculations have been performed, the following financial elements need to be output for the auto-balancing accounts into the Result Detail Tables.
Taxes State
Tax
Taxes Federal
Tax
Dividends
Dividend
Auto-balancing Asset and Auto-balancing Liability
Beginning Balance
Ending Balance
Average Balance
Beginning Net Rate
Ending Net Rate
Average Net Rate
Interest Accrued Net
Interest Cash Flow Net
Retained Earnings
Beginning Balance
Ending Balance
Average Balance
Accumulated Translation Account
Beginning Balance
Ending Balance
Average Balance
If Gap was processed for the run, the Auto-balancing Asset and Liability accounts must include an output for each start date, equal to the balance as of that date. The value will appear as the financial element Total Runoff and Total Principal Runoff.
In the Result Master table, the Auto-balancing Asset and Liability accounts, the Accumulated Translation Account, and the Retained Earnings Account will display output to the CUR_PAR_BAL, CUR_NET_PAR_BAL columns, equal to the balance as of the start date. Additionally, the Auto-balancing Asset and Liability accounts will include output to the CUR_NET_RATE column, equivalent to the rate as of that date.