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

Updated
  • Updated on Jan 28, 2025
  • Published on Aug 20, 2024

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

You can change this to calculate realised gain/loss for all cash holdings in a portfolio on the transaction 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 transaction 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 transaction date cash balance.

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

LUSID web app

Navigate to Data Management > Portfolios and either create a new portfolio or edit an existing one to choose TransactionDate from the Cash gain loss calculation date dropdown on the Details tab:

LUSID REST API

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"
}'

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"
  }
]'

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;

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 table 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 transaction 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: