I am here to ask you a suggestion about how to correctly handle this situation related to how to calculate the earned money into an financial investment.

Here you can find a basic Excel sheet related about how I am modeling the problem:


Now I will try to explain you the context in details:

  • It represents cryptocurrencies financial investment. In this case this specific sheet is related to Ethereum currency.

  • The IN MOVEMENTS section on the left contains the input movements: the user is buying Ethereum currency using euro, the quantity of euro for each input movement is into the B column having *IN-VALUE** as label. The quantity of bought ethereum for each movement is into the D column (having QUANTITY as label). The € to ETH change is into the E column having CHANGE VALUE as label. For some moovements I pay a fee in € represented by the value of the F column (haivng FEE as label).

  • In the OUT MOVEMENTS on the right I have the output movements. These movements represent when I change a specific quantity of ethereum currency (represented by the K column, having QUANTITY as label) into another cryptocurrency (because I use Ethereum currency to buy other cryptocurrencies on the exchange). So for each output movement I have the K column representing the amount of the Ethereum that is going out to buy another currency (de facto these ethereum are not destroyed or lost but are transformed in something different that will be considered into another Excel sheet) and the value in € of this amount of ethereum (the value into the I column) in the specific time of this movement (this last step is very important for my reason).

  • Finally into the N13 cell I have the last value of Ethereum currency in €.

Now my problem is: how to calculate how much I am earning?

I started thinking: it is simple and I just have to do something like this:

REWARD = [(SUM(D clumn values) + SUM(K column values)] * N13 + SUM(I column values)

So, usign this formula, the reward in €) is the final number of the Ethereum coin that I have (the sum of the coin that I have bought + the negative number of the cin that are went out buying other currencies) multiplied for the current value of ETH coin (in €) + the negative sum of the € related each out movement.

But in this case I have a big problem, I try to explain you. Looking my Excel example there are 2 possible situation:

1) The red background scenario in which I buy some Ethereum and immediately I move these quantity of Ethereum (the exactly same quantity) on a Market and I buy another currency. So the IN-VALUE and the OUT-VALUE have the same values (or otherwise very similar). And it is ok, I think that can be handle with my previous REWARD formula.

2) All the other situation (that are problematic). For example, using my Excel as Example, I do 3 different IN MOVEMENT (in these date: 08/12/2017 and 15/12/2017 and 21/12/2017)

Then in the 16/12/2017 I do an out movement where I transfer -0,3353050000 Ethereum to another market. The problem is that during this tume the value of Ethereum is changed (in this case it was increased) so I have a value in € of -198,96 € that broke my previous reward formula, infact:

REWARD = [(SUM(D clumn values) + SUM(K column values)] * N13 + SUM(I column values)


REWARD = 0,55363224 * 728,454911165 - 292,94


REWARD = 110.356124207 €

The problem is that I think that it is a very bad approximation and the problem is related in this section of my REWARD formula: SUM(I column values).

Infact here I am adding the negative out value in € at the moment I am doing the out transaction to buy another currency. This value embody the actual conversion ETH to € constant. If the value of ETH is increased a lot after that I have done my last input transaction this section SUM(I column values) should take a too hight value. If the value of ETH is decreased a lot ater that I have done my last input transaction this section of the formula should take a too low value. So my final value can be not meaningful. Is it my reason correct?

So I am thinking to solve the situation in this way:

For each out movement I will calculate something like an actualized value (into the L column, now labeled with XXX), in this way:

I consider the date of the current out movement (for example 16/12/2017), then I take all the IN MOVEMENTs having anantecedent date (so the input movements related to these dates: 08/12/2017, 15/12/2017, 21/12/2017). I use these movements to calculate the weighted average of the ETH to € conversion (so if I have 3 input movements in which I bought 3 different quantity of ETH at 3 different prices, I will calculate the weighted average of the ETH to € conversion). I want to use the weighted average because if in a movement I buy a greater quantity of ETH with a specific price it have to weigh more on the avarage).

So now I have a weighted average of all the previous input movement and I put it ino my L3 (labeled with XXX) cell of the current output movement. I know how much ETH coins I have moved out in this transaction (the K3 value), so multiply the K3* value for the new **L3 value and I use these L coumn values into my REWARD formula instead the I column values.

So my formula will be:

REWARD = [(SUM(D clumn values) + SUM(K column values)] * N13 + SUM(L column values)

I think that in this way I should obtain a more accurate reward value.

Does my idea make sense?


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.