How to calculate direct treasury custody rate

Question

How to calculate direct treasury custody rate

Navigation

#1 by (7 votes)
#2 by (2 votes)

9

Consider the following investment:

Capital inicial (C)   = 1000,00 reais
Juros (J)             = 12% ao ano
Período (n)           = 8 anos
Taxa de Custódia (TC) = 3% ao ano

The custody fee (TC) is a fee charged annually on the amount accumulated until then. It is charged separately and does not affect the amount.

For example, in the 3rd year the amount will be M3 = C*(1+J)^n = 1404,93 and the custody amount will be VC3 = M3*TC = 42,15 .

Below is a table with the custody values calculated for each year:

IcancalculatethetotalamountIwillpayforcustodybyaddingthe8rowsinthetableabove.

Thatis:

SomaCustódias(SC)=VC1+VC2+VC3...SC=M1*TC+M2*TC+M3*TC...SC=TC*(M1+M2+M3...)

Thus,wearriveatthefollowingformula:

My question is:

Is it possible to get an Excel formula that calculates the sum of the custody values?

I would like a formula that could be placed in an Excel cell and refer only to variables C , J , n and TC , without having to create a table with partial calculations.

To be clearer:

I just want to fill in the four variables mentioned at the beginning of the question ( C , J , n and TC ), each in a cell, and I want to have another cell that calculates the result. No need for tables.

excel

asked by anonymous 05.11.2014 / 17:07

2 answers

2

After several attempts and a lot of research, I came to a conclusion: It is impossible to reduce this to a single formula in excel.

What to do then?
To make your life easier, I suggest leaving the first 3 columns separate for validation.

The first column will represent the year (fill in as many years as you think necessary), the second column the amount and the third the custody fee paid in that year.

In the second column, use the SE function to check if the corresponding row year is less than or equal to the period. Ex: =SE(A1<=H2;F2*(1+G2)^A1;0) in this case, true case it will calculate the amount, if false it will leave it as 0.

Now, just use the formula you already have to calculate the Rate paid in the third column =B1*I2 as you already checked in the second column the period does not need to check again, because the result will always be 0;

Finally use a cell to calculate the sum of the third column with the final result: =SOMA(C:C)

It is not a form to do all the calculation, but it is a way to have the result that you want, being able to change only the 4 variables that you quoted.

It will look like this:

Correction

Possible with the @Viniciusmss response formula

05.11.2014 / 19:34

Is there a difference between strtr and str_replace? How to integrate AngularJS with common scripts?

score 7 · Accepted Answer

Formula to solve the initial example:

=B4*SOMARPRODUTO(B1*(1+B2)^LIN(INDIRETO("1:"&B3)))

Considering that the values of the variables are in the following columns:

C   => B1
J   => B2
n   => B3
TC  => B4

Formula to calculate custody rate in DIRECT TREASURE:

The custody fee charged by the direct treasury is provisioned daily. Therefore, the above formula does not apply, since it provisions annually. Below is the development of a more appropriate formula for calculating the custody rate for Treasury Direct (LTN) fixed rate securities.

Since the rate is provisioned daily, we have to convert the initial variables to daily values:

J   =>  (J+1)^(1/365)-1
n   =>  n*365
TC  =>  (TC+1)^(1/365)-1

Replacing the formula looks like this:

=((B4+1)^(1/365)-1)*SOMARPRODUTO(B1*(1+(B2+1)^(1/365)-1)^LIN(INDIRETO("1:"&B3*365)))

Bypassing opposing operations, it looks like this:

=((B4+1)^(1/365)-1)*SOMARPRODUTO(B1*((B2+1)^(1/365))^LIN(INDIRETO("1:"&B3*365)))

There is one more detail: the custody fee begins to be charged at D + 2. This is why we changed the part INDIRETO("1:"&B3*365) to INDIRETO("3:"&B3*365)

Finally, we arrive at the definitive formula:

=((B4+1)^(1/365)-1)*SOMARPRODUTO(B1*((B2+1)^(1/365))^LIN(INDIRETO("3:"&B3*365)))

Important Notes:

The result of the formula is only an estimate, as it considers that the value of the security will evolve uniformly according to the interest reported. However, in fact, the evolution of the security's value is also influenced by the market's expectation regarding the future of the SELIC rate. Either way, I think it's the best way to estimate the total custody fee that will be charged over the course of the application. It is a very close to the real, especially if the bond maturity is not very long term and SELIC does not vary much. This formula is very useful for estimating the real profitability of a Treasury Direct fixed-interest investment.

The custody fee charged by BM & FBOVESPA is actually 0.3% per year.

In addition to the custody fee there is also a fee charged by the intermediary financial institution. It is usually somewhere around 0% to 0.3%. There is a rate ranking on the following link: link

You can adapt the formula to also calculate (or together) the total rate of the financial institution, since it is usually calculated in the same way.