Calculating dividend tax and reporting it as a separate cash balance

In this tutorial we'll see how to use LUSID to perform the following task:
 

As a portfolio manager, I want LUSID to automatically calculate dividend tax for upserted transactions representing gross cash dividends, and report the tax amounts due in different currencies as separate cash balances.

We'll see how to:

• Master instruments representing BP and Microsoft equities, each with a tax-related property that LUSID can use to determine the correct rate of dividend tax in different jurisdictions.
• Set up a GB-domiciled transaction portfolio with holdings in BP and Microsoft, register a sub-holding key (SHK) to report dividend tax as a separate cash balance, and attach a tax-related property that LUSID can also use to determine the correct rate of dividend tax, in conjunction with the instrument properties (above).
• Create a tax rule set with rates for different jurisdictions that LUSID can use to automatically calculate and store the amount of dividend tax due for transactions matching the tax rules.
• Create a custom transaction type that triggers the tax rule set, adds the gross dividend amount to the main cash balance, and reports the amount of tax due as a separate cash balance in a holdings report.
• Upsert transactions representing gross cash dividends for BP and Microsoft, and generate a holdings report.

In summary, this tutorial explains how to implement the following scenario in LUSID:

Holding	Start # units	Dividend	Gross dividend amount	Dividend tax %	Dividend tax amount	End # units
BP	1000	50p per share	£500	25%	£125	1000
GBP cash balance	20000	n/a				20500
GBP dividend tax	0	n/a				-125
Microsoft	1000	50c per share	$500	10%	$50	1000
USD cash balance	20000	n/a				20500
USD dividend tax	0	n/a				-50

Contents

• Section 1: Mastering instruments and attaching a tax-related property
• Section 2: Setting up a transaction portfolio and attaching a tax-related property
• Section 3: Creating a tax rule set to calculate different rates of dividend tax
• Section 4: Creating a custom transaction type to trigger and process tax calculations
• Section 5: Loading transactions representing cash dividends
• Section 6: Generating a holdings report
   

Mastering instruments and attaching a tax-related property

We can call the UpsertInstruments API to master BP and Microsoft as equity instruments in the LUSID Security Master. Note the GBP and USD currency instruments are pre-mastered.

At the same time, we can attach a tax-related custom property (with a 3-stage key of Instrument/DividendTax/Country) to each of BP and Microsoft to help LUSID determine the correct rate of tax due for cash dividend transactions in different jurisdictions.

Step 1 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to see the results on the Data Management > Instruments dashboard. Click the Configuration button (top right, highlighted in green) and then Add column to add the Country property (in red) to the default view:

Setting up a transaction portfolio and attaching a tax-related property

We can call the:

• CreatePortfolio API to create a GBP-denominated transaction portfolio that:
  • Has a tax-related property (with a 3-stage key of Portfolio/DividendTax/Domicile) to help LUSID determine the correct rate of tax for cash dividends, in conjunction with the similar instrument properties.
  • Registers a sub-holding key (SHK) that can be used to report dividend tax as separate cash holdings.
• AdjustHoldings API to set initial positions for BP, GBP, Microsoft and USD in the portfolio, backdated to 2 January 2023.

Step 2 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. Once executed, you can see the portfolio details on the Data Management > Portfolios dashboard, here with the Domicile property added to the default view:

To confirm the initial positions on 2 January 2023, navigate to Dashboard > Holdings and set the Effective dropdown appropriately:

Creating a tax rule set to calculate different rates of dividend tax

We can call the CreateTaxRuleSet API to create a tax rule set that, once triggered, enables LUSID to calculate and store the amount of tax due for transactions matching the rules. More about creating tax rule sets.

Our tax rule set:

• Has two tax rules to distinguish between a dividend tax rate of 25% for UK equities and 10% for US equities in a GB portfolio. Tax rules are evaluated in the order specified in the set, and the first matching rule applies.
• Both tax rules have two match criteria, each comparing a static value against a property attached to the underlying instrument and the containing portfolio respectively. Every match criterion must be met for a tax rule to apply.
• Has an output property with a 3-stage key of Transaction/DividendTax/AmountDue to store the calculated amount of tax due for each transaction.

Step 3 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. Tax rule sets are not yet available in the LUSID web app, but we can call the GetTaxRuleSet API to see the definition, here with the output transformed to a Pandas dataframe for clarity:

Creating a custom transaction type to trigger and process tax calculations

We can call the SetTransactionType API to create a custom transaction type that, for each transaction to which it is applied:

1. Triggers the tax rule set to calculate dividend tax and store the amount of tax due. To do this, we hard-code the calculation type to TaxAmounts and pass details of the equity instrument to the tax rule set using the built-in Side1 designed for securities (that is, non-cash instruments).
2. Adds the gross dividend amount to the main cash balance in the underlying instrument currency. To do this, we add a positive movement with a type of CashAccrual that uses the built-in Side2 designed for currency instruments.
3. Reports the dividend tax amount due as a separate cash balance in the underlying instrument currency. To do this, we add a negative movement with a type of CashReceivable that uses a custom side (created using the SetSideDefinition API) to map the tax rule set's output property to the portfolio's SHK.

For much more information on using transaction types, movements and sides to configure the way LUSID reports holdings, see this article.

Step 4 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. Once executed, you can see the definition of the custom side on the System Settings > Transaction Types dashboard (navigate to the Sides tab, highlighted in red):

You can examine the custom transaction type itself from the main System Settings > Transaction Types dashboard:

Loading transactions representing cash dividends

We can call the UpsertTransactions API to upsert two transactions dated 20 September 2023 into the portfolio representing cash dividends for BP and Microsoft, of 50 pence and 50 cents per share respectively. The trades settle on 22 September.

Note that although each transaction represents a cash amount, we create it as a transaction in the equity rather than the currency, since a cash dividend represents a flow (of value) out of a security. The units of the transaction is the number of shares held, the price is the dividend amount per share, and the total consideration is the number of shares multiplied by the dividend per share, so 1000 * 0.5 = 500.

Step 5 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. Once executed, you can see the results by navigating to Dashboard > Transactions in input transaction mode (mode switch highlighted in green) and choosing a window of September (in red):

For audit purposes, we can call the BuildTransactions API for the month of September to see the output transactions automatically generated by LUSID under-the-hood in enrich these input transactions with tax calculation results. You can see the results on Dashboard > Transactions in output transaction mode (highlighted in green below).

Note the following:

• The Type column confirms the custom transaction type has been applied to the cash dividend transactions.
• The Transaction Amount column confirms the amount of the gross dividend payment in the appropriate currency.
• The AmountDue column displays the Transaction/DividendTax/AmountDue tax rule set output property to show the calculated amount of dividend tax due.
• The AppliedTaxRule column displays the Transaction/default/AppliedTaxRule system property to show the scope and code of the tax rule set and identifier of the rule used to calculate the tax amount:

Generating a holdings report

We can now call the GetHoldings API to generate a holdings report.

Step 6 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. Once executed, you can see the results by navigating to Dashboard > Holdings.

If we look at the holdings report for the trade date of 20 September 2023 (highlighted in red below), LUSID has applied the upserted transactions to the portfolio:

• Gross dividend payments of £500 and $500 have been generated for BP and Microsoft respectively (in green), with an (unsettled) holding type of CashAccrual.
• Dividend tax amounts of -£125 and -$50 have been calculated for BP and Microsoft and classified separately using the DividendTax SHK (in yellow), with an (unsettled) holding type of CashReceivable:

If we look at the holdings report for the settlement date of 22 September 2023, we can see that LUSID has settled the outstanding positions:

• The gross dividend payments have been added to the main portfolio GBP and USD cash balances.
• The dividend tax amounts of -£125 and -$50 are now both reported as a separate Cash Balance, and all units are settled units: