Arbitrage: Get realtime lending rates from DeFi platforms
I wrote this medium in complement to the medium Pull Crypto Wallet Balance directly on Google Sheets where you can retrieve almost any cryptocurrency (150+ with biggest market cap) balance from a cold wallet address.
This article will be useful for you, if you want to arbitrage lending platforms. Or if you need to keep a tab/perform some accounting on the cryptocurrency amounts you have earned or the amounts you owe from lending/borrowing cryptocurrencies. I’ll be using Google Sheets, it is a free, web-based program for creating and editing spreadsheets.
At the end of this medium, you will be able to automatically retrieve borrowing and lending rates on the following lending platforms:
MAKERDAO — COMPOUND — COIN — FULCRUM— NEXO — CELSIUS — YEARN —
Some of the cryptocurrencies available:
ETH — DAI — USDC — BAT — ZRX — WBTC — TUSD — USDT — BTC — WBTC — SNX — LINK — PAX — REP — KNC
UPDATE June 2021 #NEW LENDING FUNCTION
I’ve rebuilt the cryptolending() function.
Instead of only being able to request one rate at a time, you can now request arrays of rates which reduces the risk of reaching the Google API limits.
The old version of cryptolending() is now defunct. You will need to refresh your code with the following for it to re-work:
https://raw.githubusercontent.com/Eloise1988/CRYPTOBALANCE/master/CRYPTOTOOLS_V2.gs
New: Compound’s APY Distribution table is also now available.
The CRYPTOLENDING Formula
This is what your Google Sheet will look like once you finish the setup:
The CRYPTOLENDING function returns cryptocurrency lending and borrowing rates from different lending platforms. You will be able to access the borrowing and lending APR (Annual Percentage Rate) through the following formula:
=CRYPTOLENDING(“LENDING PLATFORM”,”TICKER”,”SIDE”, refresh_cell)
I open sourced the code for anyone that wishes to get the rates. The APR Lending/Borrowing rates are directly taken from the lending platforms (MakerDAO-Compound-DYDX) or through subsidiaries CoinMarketCap & CoinInterestRates that have access.
EXAMPLE 1: Arbitrage
Check for arbitrage opportunities on loaning rates
Arbitrage is defined as the simultaneous buying and selling of securities, currency, or commodities in different markets or in derivative forms in order to take advantage of differing prices for the same asset.
In our case, an arbitrage opportunity arrises on interest rates when you can borrow at a lower rate and lend at a higher rate than you borrowed. As shown on the table above highlighted in yellow, there are arbitrage opportunities for USDC and DAI. How can you profit from these 2 opportunities ? We will be using a £10k loan with almost to none exposure risk as an example. Why do I say almost to none ? Because you will still need to deposit some cryptocurrencies like BTC, ETH … in order to collateralize your loan, which exposes you to the price fluctuations on your collateral.
Case DAI**:
By taking a look at Table2 above, the lowest annual rate you can borrow DAI is 8% on NEXO* and the highest annual rate you can lend DAI is 11.48% on DYDX. So you can lend DAI at a higher rate than borrowing it. The difference in rates is 3.48%. So let’s say on Table3, you take a £10k year loan in DAI on NEXO and lend these DAI on DYDX for the same period of time, after a year you will have to reimburse your loan on NEXO with interests £10'800 (£10'000*8% +£10'000) and you will get £11'148(£10'000*11.48% +£10'000) if the counter-party hasn’t defaulted on your loan. So in total you will have made £348 which is 3.48% of your loan.
This is not 100% free risk as the counter-party could default on your loan, and you take the exposition risk of your collateral.
*On Nexo, you can deposit the following crypto assets: BTC, ETH, XRP, LTC, XLM, stablecoins, NEXO and BNB as collateral. Support for other major altcoins is coming later in 2019.
Case USDC**:
As for USDC, the lowest annual rate you can borrow is 8% on NEXO and the highest annual rate you can lend is 9.25% on Celsius (Table2). So you can lend USDC at a higher rate than borrowing it. The difference in rates is 1.25%. So let’s say on Table3, you take a £10k year loan in USDC on NEXO and lend these USDC on Celsius for the same period of time, after a year you will have to reimburse your loan on NEXO with interests £10'800 (£10'000*8% +£10'000) and you will get £10'925(£10'000*9.25% +£10'000) if the counter-party hasn’t defaulted on your loan. So in total you will have made £125 which is 1.25% of your loan.
* IMPORTANT: The borrowing rates may be different from what is shown as more and more lending platforms are adapting their rates on the size/amount of the loan. Due diligence is necessary.
Trinito shows how you can apply the arbitrage in practice .
EXAMPLE Accounting
Compute the interest you earned and paid on your different lending platforms
In this example, the client has loaned on Compound £5.6k of DAI at an annual rate of 7.32%. He received £198.27 for loaning DAI for 176 days.
£198.27=£5615.72 loan x 7.32% APR Lend x 176/365Days
[7.32% APR Lend =CRYPTOLENDING(“COMPOUND”,”DAI”,”APR_LEND”, $B$1) ]
On the 1st of September, he took out a £5.2k loan of DAI on DYDX at an annual rate of 15.25%. He currently owes £50.4 on his DYDX loan.
£50.40=£5245 loan x 15.25% APR Lend x 176/365Days
[15.25% APR Borrow=CRYPTOLENDING(“DYDX”,”DAI”,”APR_BORROW”, $B$1)]
He’s currently profitable on his interests as he made £147.87
Open-Source Setup (5min)
If you want to get in real time anyone’s wallet balance, you’ll need:
- a Google Account for Google Sheets
- to Copy Paste code from Github
- 5 minutes of your time
How to :
- Open a Google sheet where you wish to use CRYPTOLENDING()
2. Go to Tools › Script editor
3. From the Github, copy the content of CRYPTOTOOLS and paste it in the script editor (replace any existing content)
4. Save the script with File › Save, name it CRYPTOTOOLS, then close the script editor
5. Back to your Google sheet, refresh the page, a CRYPTOLENDING menu will appear with parameters, an example and contact info.
Using the formula in Google Sheets
3 Required Parameters + 1 Optional
- {“LENDING PLATFORM”} The exchange on which you want to retrieve the APR lending/borrowing rate. Currently available exchanges: MAKERDAO, COMPOUND,DYDX, NUO, NEXO, CELSIUS
- {“TICKER”} Cryptocurrency ticker associated with the lending/borrowing rate you are searching. Please pay attention to the available tickers on platforms.
- {“APR_BORROW or APR_LEND”} Either APR_BORROW for the APR borrowing rate or APR_LEND for the APR lending rate.
- Optional {EMPTY CELL REFERENCE} ONLY on the 4th argument. Reference an empty cell and change its content to force rates to refresh.
Now the function is available for use! Try it out!
This is ongoing work. If you find errors, please do not hesitate to let me know. Feedback is very welcomed. A telegram chat is also available for support.
Thank you.
Annex: remarks Google sheet’s formula syntax :
In some countries like Italy and Russia, Google sheet’s formula syntax is done with semicolon ‘ ; ’ instead of the comma ‘ , ’
If you get an error message from the formula, it might be the reason. In this case, the formula then becomes :
=CRYPTOLENDING(“LENDING PLATFORM”;”TICKER”;”SIDE”;refresh_cell)
