For a few years I was in cryptocurrency trading. Eventually, this led me to the wild west of decentralized finance tokens (“DeFi”), an exciting area that I feel has strong potential to be on the cutting edge of technology’s potential to fundamentally reshape our society.

Among the odd beasts you’ll encounter on the frontiers of DeFi is the “Automated Market Maker” (AMM) managing a “liquidity pool”, of which the most prominent current example is Uniswap. This is a decentralized algorithm running on the Etheruem network that serves the same purpose in DeFi markets as the large “market makers” and financial houses that retail traders are buying and selling from in the traditional stock market. In DeFi, instead of being privately run, these are open and communal, and operate by strict rules set up in “smart contracts” on the crypto network. Essentially that’s just a fancy name for a program, except, once a smart contract is set up, it’s basically a permanent part of the network. You can create a new contract that supersedes it, but you can’t change it.

Basically, in any market, traditional or crypto, there needs to be a large pool of liquidity: that’s items to be bought or sold, and cash to buy or sell with. For things to function smoothly, there needs to be enough of both up for offer that people can show up and buy or sell without too much difficulty. That’s what the big financial clearing houses do in the traditional market; in DeFi it’s the AMMs. Because you buy and sell cryptocurrencies through the AMM, it must have those resources. Let’s say you want to buy or sell CrapToken ($CRP). The basic currency of DeFi is Ethereum ($ETH), so you will find a an AMM that offers $CRP/$ETH trades. The AMM has to already have enough $CRP and $ETH to allow this.

So people are enticed to loan their assets to the AMM. You transfer equal amounts of $ETH and $CRP to the AMM’s crypto wallet addresses, and in exchange for this, you get “liquidity pool tokens” showing you have a deposit with the AMM. You may, in some cases, receive a small percentage commission on every transaction that the AMM makes while you hold its LP tokens. At any time, you can do the reverse, redeem the tokens and get back an amount of $ETH and an amount of $CRP in equal values.

Now, the price of $CRP in $ETH at any given time is determined by supply and demand, just like in the real world, the price of anything in $USD is determined by supply and demand. It’s dictated by the ratio of $ETH and $CRP the AMM is holding. As more people want $CRP, they send $ETH to the AMM and receive $CRP from it, thereby increasing the ratio of the AMM’s holdings of $ETH to its holdings of $CRP, which reprices $CRP to cost more in $ETH than it did before the transaction.

The rest of the rules, though, are more complicated. Somehow, the value of the first deposits of either currency must be determined, as must the quantity of LP tokens received for a given deposit, and the value of those tokens when redeemed. These are all determined by complicated preset mathematical rules.

The nice thing about DeFi, though, is that, unlike real-world finance, you can (usually) read the smart contracts yourself to see exactly what the rules are, and because they’re on the blockchain, you know they apply equality to everybody and can’t be manipulated in secret ways to advantage certain players and disadvantage others. That is, if you can read the contract code.

Most of this is totally opaque to most people trading in the market. They don’t actually know know how the value of their holdings is determined. I didn’t.

So, I read the contract code.

The result is an Excel spreadsheet I created that allows you to enter, in rows, DeFi transactions, exactly as if you were initiating those transactions with a real AMM. (I used Uniswap’s contracts to create this, far and away the most popular AMM when I was in DeFi.)

The spreadsheet calculations use the actual contract rules, copied straight from Uniswap’s code, to calculate and display how every single possible transaction affects every single current and subsequent value of the traders’ and AMM’s holdings. You can enter any sequence of adding to or removing from the pool and buy or sell transactions among up to a handful of different people, and watch how the entire market changes every step of the way.

It’s all rather complicated and I’m sure opaque to people who aren’t involved but I had a lot of requests for this from DeFi traders.

The repo containing the spreadsheet is at https://github.com/kupietools/excel-liquidity-pool-simulator