0
$\begingroup$

Problem:

Given a typical general ledger where balances are posted as "deltas", such that the total net of amounts (debit-credit, below simplified as "amount", equals the ending balance), we want to add a Report Date feature, such that clients can report multiple balances (mark to markets) for a given month's end as more information is known throughout time. Treating no report date as lowest possible report date. Imagine client can enter any report date they want.

Requirements:

  1. The net balance must be the last best known MTM.
  2. The balance as of each Valuation Date must be the correct latest reported MTM.
  3. The balance as of each Report Date must be the correct latest MTM as of report date.

A very simple example:

| # | Valuation Date | Report Date | Amount     | MTM        | IsAdjustment |
|---|----------------|-------------|------------|------------|--------------|
| 1 | 2017-12-31     |             | 5,000,000  | 5,000,000  | false        |
| 2 | 2018-05-31     | 2018-06-05  | 10,000,000 | 15,000,000 | false        |
| 3 | 2018-04-30     | 2018-05-05  | 5,000,000  | 10,000,000 | false        |
| 4 | 2018-05-31     | 2018-06-05  | -5,000,000 |            | true         |
  1. We start with a balance of 5 million and no report date.
  2. We post a MTM of 15m for 2018-05-31 reported on 2018-06-05. We calculate the MTM as of (2018-05-31, 2018-06-05). Thus we add a delta of 10m to the GL.
  3. We post a MTM of 10m (we forgot) for 2018-04-30 reported on 2018-05-05. We calculate the MTM as of (2018-04-30, 2018-05-05). Thus we add a delta of 5m to the GL.
  4. But we need to adjust the next MTM so its balance is correct, and so our ending balance is 15m.

Our ending balance is 15m (our last reported MTM). We can still get each MTM for each valuation date. We can also get the correct latest valuation as of report date.

Find a working algorithm that works with the following order of mark to markets

| Valuation Date | Report Date | MTM             |
|----------------|-------------|-----------------|
| 2/28/2011      | 1/15/2019   | $100,000,000.00 |
| 12/31/2010     | 1/15/2019   | $50,000,000.00  |
| 1/31/2011      | 1/10/2019   | $75,000,000.00  |
| 11/30/2010     | 1/10/2019   | $30,000,000.00  |
| 10/31/2010     | 1/10/2019   | $28,000,000.00  |
| 9/30/2010      | 1/14/2019   | $28,000,000.00  |
| 9/30/2010      | 1/14/2019   | $27,000,000.00  |
| 9/30/2010      | 1/15/2019   | $27,500,000.00  |
| 2/28/2011      | 1/1/2019    | $90,000,000.00  |
| 1/31/2011      | 1/1/2019    | $70,000,000.00  |
| 8/31/2010      | 1/1/2019    | $26,000,000.00  |

Here is a workbook to help with calculating the balances.

What I thought was the solution was

  • First calculate your MTM getting balance as of the tuple (valuation date, report date).
  • Then "self adjust" if there are valuations in the past with report dates higher than yours. Get the net values of the deltas per report date higher than yours of the past, and negate them.
  • Then adjust forward your new entries (negate their amounts). Adjusting the next entry with same valuation date higher report date OR just higher valuation date (which ever comes first in time), and take the MAX of it's report date and the entry your are adjusting for.

But there are some odd scenarios where this doesn't hold :(

Need help!

$\endgroup$
  • $\begingroup$ A very nice question with lots of detail. Could you please credit the source of the problem in the question? If the problem is original, please claim so as well. $\endgroup$ – Apass.Jack Feb 12 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.