Accounting and Business Consulting

(0044) 07415 385371

A simple model for blockchain

It is a fairly typical requirement for financial modellers to translate verbal descriptions of relationships into numbers and formulae and it was surprisingly easy to read some articles and then come up with a simple Excel model which captures some of the key features of this technology.

Here’s an explanation of how it works:

Blockchain is often described as a distributed ledger.

Well, what is a ledger? – A ledger is simply a record of transactions and balances. Consider the bank account (or ledger) below as an example:

Trxn #  Date Description Amount Balance
1 31/01/2021    Supplier A payment    -1,000.00    2,500.00
2 01/02/2021 Client X receipt 300.00 2,800.00
3 05/02/2021 Client Q receipt 200.00 3,000.00


These are the features of how the ledger operates:

  1. The balance shows the amount of the cash asset in the account;
  2. The inputs to computing a given closing balance are the previous balance and the amount of the transaction on the day;
  3. To get from these inputs to the closing balance we perform a sum (addition) operation.

Because the current closing balance is coupled by the transactions and the addition process to the previous balances, we can say the balances are to some extent ‘chained’ to one another in a series of consecutive addition operations.

Now let’s look at a comparative example ‘ledger’ which I have come up with that seems to replicate much of what has been described of the behaviour of a blockchain:

Trxn #  Datetime From To Code (“balance”)
1 31/01/2021 10:30    DB1    DB2     qrhngnrt_31/01/2021 10:30
2 01/02/2021 04:16 DB2 DB3 tohijnm_01/02/2021 04:16
3 05/02/2021 17:29 DB3 DB5 kfytbsz_05/02/2021 17:29


These are the features of how this ‘ledger’ operates:

  1. The code field (also called the balance because it is the analog of the bank account balance column) is the descriptor of the asset;
  2. The inputs to deriving the code at any point are the datetime value, the identities of the parties to the transaction (say DB3 and DB5), and the preceding (brought forward) code (or ‘balance’);
  3. To get from these inputs to the closing ‘balance’ we perform an encryption and concatenation operation as follows: we concatenate (in order) the ‘from’ party ID, the ‘to’ party ID, and the preceding code (say into DB3_DB5_ tohijnm_01/02/2021 04:16 for the transaction of 5 Feb) and we then encrypt this; this gives us a string code (say kfytbsz, although actual encryption strings have 32 or more characters), and we then concatenate this with the datetime of the current transaction to get the closing code.

For the purposes of my model, I have imagined the parties to the transaction to be different database servers which have each been equipped with the functionality to complete transactions for this blockchain and to participate in validating the integrity of the chained asset (e.g. the bitcoin).

As for the bank balance ledger above, the ‘balance’ at any point of the blockchain ledger is ‘chained’ to the preceding transactions and balances.

However, here is a key difference between the two ledgers: due to the encryption operation performed at any step along the way, the most recent balance of the blockchain ledger can only be derived by a single, unique set of preceding transactions and balances.

To illustrate:

• The 5 Feb £3000.00 balance of the bank account could have been derived from an opening balance of £10.00 and a receipt of £2,990.00, or of an opening balance of £25,000,000.00 and a payment of £24,997,000.00, or of an infinite amount of other possible combinations.
• But the 5 Feb code of the blockchain ledger could ONLY have been derived from encrypting and concatenating the precise set of inputs in the given order. And similarly for the code of 1 Feb, and the code of 31 Jan before that.

This is important, because in blockchain the ‘ledger’ is distributed and the input values for the transactions are only visible to the parties to the transaction. So, only DB1 and DB2 know the fields for transaction 1; only DB2 and DB3 know the values of the inputs and output code of transaction 2, and only DB3 and DB5 know the values of the inputs and output code of transaction 5.

These features (uniqueness of transactions, chaining of transactions, and distributed knowledge of the input and output values of separate transactions) then makes it possible for the code to be uniquely validated after any transaction by requesting that prior parties to the transaction history of the asset compare their data to determine whether collectively it is consistent with the current code of the asset.

I have not tried to model how the comparison might take place, but it is not difficult to imagine some sort of peer-to-peer checking that would validate the integrity of the chained transactions without aggregating all the data into a single ledger.

So, there it is: a model which allows for a distributed ledger and secure validation of a code descriptor of an asset.

In the case of a bitcoin, the asset is simply a right to economic benefits represented by the code and the economic benefits are the current market price of the ‘coin’.

There are undoubtedly additional complexities to creating an actual cryptocurrency and if anyone wants to comment further, please let me know.

Also, if this simple model does not sync with the features of blockchain at some fundamental level, please let me know – I have worked solely off the basis of some articles with verbal descriptions to come up with what seems to me a practical ‘how it works’ model but I don’t have experience of building blockchains myself.

Lastly, although it should be easy enough for anyone who has read the above to recreate a working Excel model of a blockchain, you can always download the attached file and see what I have done.