Realising gain/loss for cash holdings on trade date rather than settlement date

Views:

Related resources:

By default, LUSID calculates realised gain/loss for a transaction impacting a cash holding on the settlement date rather than the trade date.

You can change this to calculate realised gain/loss for all cash holdings in a portfolio on the trade date instead. Since payable/receivable 'cash commitment' holdings may settle in a different order to that in which the original transactions were booked, the total cash balance from which an average cost is derived may be different, and so the realised gain/loss amount may also be different.

The difference between the recorded cost and the trade date average cost is reported as the cost normalisation. The amount changes each time a new transaction is booked that alters the average cost of the trade date cash balance.

Setting up a portfolio to calculate realised gain/loss on trade date

You can create a new transaction portfolio with the cashGainLossCalculationDate field set to TransactionDate, for example:

curl -X POST "https://<your-domain>.lusid.com/api/api/transactionportfolios/myexamplescope"
-H "Authorization: Bearer <your-API-access-token>"
-H "Content-Type: application/json-patch+json"
-d '{
 "displayName": "Trade date cash portfolio",
 "code": "TradeDate",
 "created": "2023-11-01",
 "baseCurrency": "GBP",
 "cashGainLossCalculationDate": "TransactionDate"
}'

In Luminesce you can use the Lusid.Portfolio.Writer provider to populate the CashGainLossCalculationDate field, for example:

@table_of_data = select 'Transaction' as PortfolioType, 'myexamplescope' as PortfolioScope, 'TradeDate' as PortfolioCode,
'Trade date cash portfolio' as DisplayName, #2023-11-01# as Created, 'GBP' as BaseCurrency, 
'TransactionDate' as CashGainLossCalculationDate;
select * from Lusid.Portfolio.Writer where ToWrite = @table_of_data;

You can configure an existing portfolio to retrofit this behavior, for example:

curl -X POST "https://<your-domain>.lusid.com/api/api/transactionportfolios/myexamplescope/TradeDate/details"
 -H "Authorization: Bearer <your-API-access-token>"
 -H "Content-Type: application/json-patch+json"
 -d '[
  {
    "value": "TransactionDate",
    "path": "/cashGainLossCalculationDate",
    "op": "add"
  }
]'

Reporting cost normalisation amounts in a valuation

You can include the Holding/CostNormalisation metric when you perform a valuation to report cost normalisation amounts, for example:

curl -X POST "https://<your-domain>.lusid.com/api/api/aggregation/$valuation"
 -H "Authorization: Bearer <your-API-access-token>"
 -H "Content-Type: application/json-patch+json"
 -d '{
   "portfolioEntityIds": [ {"scope": "myexamplescope", "code": "TradeDate", "portfolioEntityType": "SinglePortfolio"} ],
   "valuationSchedule": {"effectiveAt": "2023-11-14T00:00:00.0000000+00:00" },
   "recipeId": {"scope": "myexamplescope", "code": "TradeDateRecipe"},
   "metrics": [
     {"key": "Instrument/default/Name", "op": "Value"},
     {"key": "Valuation/EffectiveAt", "op": "Value"},
     {"key": "Holding/default/Units", "op": "Value"},
     {"key": "Holding/Cost/Pfolio", "op": "Value"},
     {"key": "Valuation/PvInPortfolioCcy", "op": "Value"},
     {"key": "Holding/CostNormalisation", "op": "Value"},
     {"key": "ProfitAndLoss/Realised/Fx/PortfolioCcy", "op": "Value", "options": {"Window": "YTD"}},
     {"key": "ProfitAndLoss/Unrealised/Fx/PortfolioCcy", "op": "Value", "options": {"Window": "YTD"}},
   ]
 }'

The response might look like this, transformed to a Pandas dataframe and with columns renamed for clarity:

Auditing cost normalisation amounts in an ABOR

LUSID categorises journal entry lines representing cost normalisation amounts in a NA_CostNormalisation economic bucket. Note also that LUSID automatically balances cost normalisation journal entry lines by offsetting credit amounts for cash holdings above the trade date average with debit amounts for cash holdings below the average.

You can view journal entry lines to audit cost normalisation amounts, for example:

curl -X POST 'https://<your-domain>.lusid.com/api/api/abor/myexamplescope/tradedateabor/journalentrylines/$query'
  -H 'Content-Type: application/json-patch+json'
  -H 'Authorization: Bearer <your-API-access-token>'
  -d '{
  "start": {"date": "2023-11-01T00:00:00.0000000+00:00"},
  "end": {"date": "2023-11-14T00:00:00.0000000+00:00"}
}'

The response might look like this, transformed to a Pandas dataframe for clarity:

Note the following:

Journal entry lines representing cost normalisation amounts are produced by a LusidValuation rather than a LusidTransaction operation.
They can be of holding types C (unsettled cash commitment), R (unsettled cash receivable), A (unsettled cash accrual) or B (settled cash balance).
They are only calculated in base (portfolio) currency.

You can create a trial balance with a general ledger profile that includes an EconomicBucket level to drill down into accounts containing cost normalisation amounts, for example:

curl -X POST 'https://<your-domain>.lusid.com/api/api/abor/myexamplescope/tradedateabor/trialbalance/$query'
  -H 'Content-Type: application/json-patch+json' 
  -H 'Authorization: Bearer <your-API-access-token>'
  -d '{
  "start": {"date": "2023-11-01T00:00:00.0000000+00:00"},
  "end": {"date": "2023-11-14T00:00:00.0000000+00:00"},
  "generalLedgerProfileCode" : "CostNormDrillDown"
}'

The response might look like this, transformed to a Pandas dataframe for clarity:

Keywords: cost normalisation