apportion
Performs a apportioning operation common in accounting and finance. Expects a sequence of transactions. Each transaction is vector of the form [source-id, source-amount, target-id, target-amount]. Relief is performed in the order of transaction appearance.
pebblestream:from(“Transactions”)
pebblestream:apportion(“Buys.id”,”Buys.amount”,”Sales.id”,”Sales.amount”)Consider a Transactions worksheet of paired buy-sale transactions, ordered by transaction date.
| Buy.id | Buy.amount | Sale.id | Sale.amount | Gain/Loss |
|---|---|---|---|---|
| 1 | 1000 | 3 | 200 | -50 |
| 1 | 1000 | 4 | 900 | 100 |
| 2 | 250 | 3 | 200 | -50 |
| 2 | 250 | 4 | 900 | 100 |
Use cartesian product to easily create worksheet from Buy and Sale worksheets.
This will produce the following worksheet "Apportion".
| Buy.id | Sale.id | apportion:source-ratio | apportion:source-amount | apportion:target-ratio | apportion:target-amount |
|---|---|---|---|---|---|
| 1 | 3 | 0.2 | 200 | 1 | 200 |
| 1 | 4 | 0.8 | 800 | 0.889 | 800 |
| 2 | 3 | 0.8 | 200 | 1 | 200 |
| 2 | 4 | 0.2 | 50 | 0.056 | 50 |
Note that ratio of relieve for both the buys (source) and the sales (target) is conveniently provided. Use those ratios to determine the share of Gain/Loss associated with each lot.
Download the example below.
Updated 4 days ago