How to calculate and track the real APR with an Excel template

Deceptive APY/APR‘s are quite a common occurrence in yield farming. Whether incorrect rates are displayed by accident (even trustworthy developers make mistakes) or deliberately (in order to deceive investors), incorrect rates can result in opportunity costs and even loss of your deposit fees. In this article you will learn how to calculate the APR of a farm on your own with a simple excel table.

If you are in some doubt as to the real rewards produced by a pool, you can try depositing a very small amount at first in order to measure the returns – this can protect you against the losses described above. This method is described in this article.

To track the real reward you are receiving, you can use a simple Excel script to calculate the time interval between the different claims. This will help you to decide if the farm rewards are worth your investment of funds and time.

Step 1: Open the excel document #

So starting with a clean excel, we will now add some basic functionality to calculate the APR.

Step 2: Add the deposit amount #

First you have to add a field where you write down the amount of your deposit as the base for the reward calculation.

Step 3: Calculate the time interval #

The next two fields need to be filled with the date and time of the last and the new reward in order to calculate the time interval.

To get the time interval between the last and the new reward you can simply calculate the difference of the two dates and multiply the results with 1440 divided by 60 which gives you the hours between the two rewards. The function is shown in the following codeblock.

=(B6-B5)*1440/60

Step 4: Calculate the yield for the new reward #

The next step is to add a field for the received rewards and add the formular to calculate the % between the reward and the initial deposit.

=D6/C2

In this example the yield is 2,5% which is the results of $2,50 / $100 = 0,025.

Step 5: Calculate the yield per day #

With this information, we can calculate the % per day by dividing 24 by the time interval and multiply this with the 2,5% calculated in the steps before

=24/C6*E6
// 24 / 1 * 0,025 = 0,60 = 60,0% per day

In order to know which field are filled automatically and which have to be filled manually you can set a different color to the custom fields.

Now you are able to calculate your own APR and don’t have to rely on the user interface (UI) of the farm when making important decisions with your funds.