Oracle® Insurance Rules Palette 10.1.2.0 E59346-01

Cross Rate Currency Calculation

When stocks are exchanged using different currencies and neither currency is the official currency of the country where the exchange quote is given, a cross rate can be used to facilitate the exchange.  

In order to perform a cross rate currency calculation, the following information is required.

• Originating Currency: the three letter currency code the amount is currently in. 

• Amount: a decimal that contains the number of units of originating currency to be converted. 

• Target Currency:  the three letter currency code the amount will be converted to.

• Market Maker:  the market maker that is associated with the current company or plan.

• Exchange Date - allows the user to pass in a reference to a date formatted math variable or field (i.e. "Activity:EffectiveDate”) that will be used to locate the exchange rate the system will use in the conversion exchange calculation.

Steps to Perform a Cross Rate Currency Calculation

1. Retrieve the CrossRateCurrencyCode for the current Market Maker from the AsMarketMaker table.

2. Retrieve an exchange rate with the CrossRateCurrencyCode as the BASE currency for both the Originating currency and Target currency:

1. Retrieve an exchange rate from the AsExchangeRate table where the TERMS currency is the Originating currency, and the BASE currency is the Market Maker’s CrossRateCurrency.

2. Retrieve an exchange rate from the AsExchangeRate table where the TERMS currency is the Target currency, and the BASE currency is the Market Maker's CrossRateCurrency.

3. If a rate cannot be found for BOTH the Originating and Target currency, generate an Exception.

3. Retrieve the DirectTermIndicator for both the Originating currency and the Target currency. This will determine the cross rate calculation method.

4. Calculate the cross rate exchange rate using the two exchange rates retrieved from step two. The calculation performed is always as follows:  

Result = Amount / Denom; where Amount is the amount of Originating currency to be exchanged, and Denom is the number of units of the Originating currency that can be exchanged for one unit of the Target currency.

5. Calculate the Denom in one of four ways, depending on the DirectTermIndicator of the cross rate exchanges:

1. Both currencies are direct (have a DirectTermIndicator of T):

1. Denom = Target Currency Offer Price / Originating Currency Bid Price

2. Example of Cross Rate Currency Calculation:

AUD/EUR (Australian Dollar, Euro) with USD as the CrossRateCurrency, both in Direct terms

• Originating Currency = EUR

• Target Currency = AUD

• Amount = 100000 AUD

• Exchange Date = 06/01/2010

• USD/EUR Exchange = 1.2474 Bid; 1.2478 Offer

• USD/AUD Exchange = 0.7296 Bid; 0.7299 Offer

• Denom = .7299 / 1.2474 = 0.585137

• Result = 100000 / 0.585137 = 170900.1 AUD

2. Both currencies are indirect (have a DirectTermIndicator of F):

1. Denom = Originating Currency Bid Price / Target Currency Offer Price

2. Example of Cross Rate Currency Calculation:

HKD/JPY (Hong Kong Dollar, yen) with USD as CrossRateCurrency, both in Indirect terms

• Originating Currency = HKD

• Target Currency = JPY

• Amount = 100000 HKD

• Exchange Date = 06/01/2010

• USD/HKD Exchange = 7.2 Bid; 7.1 Offer

• USD/JPY Exchange = 109.41 Bid; 109.40 Offer

• Denom = 7.2 / 109.40 = 0.065814

• Result = 100000 / 0.065814 = 1519444 JPY

Note: The Rounding Method here was to round up, and the Rounding Decimal Places was 0.

3. Originating currency is quoted in direct terms; Target currency is quoted in indirect terms:

1. Denom = 1 / ( Originating Currency Bid Price * Target Currency Offer Price )

2. Example of Cross Rate Currency Calculation:

AUD/HKD (Australian Dollar, Hong Kong Dollar) with USD as CrossRateCurrency, AUD in Direct Terms, HKD in Indirect terms

• Originating Currency = AUD

• Target Currency = HKD

• Amount = 100000 AUD
• Exchange Date = 06/01/2010
• USD/AUD Exchange = 0.7296 Bid; 0.7299 Offer
• USD/HKD Exchange = 7.2 Bid; 7.1 Offer
• Denom = 1 / (0.7296 * 7.1) = 0.193044
• Result = 100000 / 0.193044 = 518016 HKD

4. Originating currency is quoted in indirect terms, Target currency is quoted in direct terms:

1. Denom = Originating Currency Bid Price * Target Currency Offer Price

2. Example of Cross Rate Currency Calculation:

JPY/EUR (yen - Euro) with USD as CrossRateCurrency, JPY in Indirect Terms, EUR in Direct terms

• Originating Currency = JPY

• Target Currency = EUR

• Amount = 100000 JPY
• Exchange Date = 06/01/2010
• USD/JPY Exchange = 109.41 Bid; 109.40 Offer
• USD/EUR Exchange = 1.2474 Bid; 1.2478 Offer
• Demo = 109.41 * 1.2478 = 136.5218
• Result = 100000 / 136.5218 = 732.4838 EUR

6. Round the Denom using the CurrencyRoundPlaces and CurrencyRoundMethod from the AsMarketMaker table.

Note: This is very important to the proper calculation of the exchange rate.

7. Divide the Denom INTO the amount.

8. Retrieve the CurrencyRoundPlaces and CurrencyRoundMethod values from the AsCurrency table for the Target currency and round the result.