Help with a formula to calculate growth rate

[Scot_Taylor]

Hello all,

I am a total newbie, recent Notion convert. I am completely blown away by Coda itself, as well as the amazing community around the application.

I’m trying to create a template for researching investments and want to be able to calculate the growth rate for certain items over one, five and ten years. The one year value was easy but I’m having trouble figuring out a formula to make this happen for the five and ten year values.

I’ve searched the forums and Reddit with no luck, could any of you offer insight?

Thanks so much!

[Agile_Dynamics]

@Scot_Taylor

how is the yearly data stored?
is there a single row of data for each year?

the solution depends greatly on seeing how the data is stored in your tables.

you will get the best help from the community if you can share a sample doc (with dummy data) here so we can see how the data is structured.

max

[Scot_Taylor]

Yes, for sure…thanks for your help!

Here’s a screenshot of how the data is arranged. If this is not adequate, I would be happy to create a dummy doc and share it. It’s pretty straightforward though.

I have created it in a way that I can just copy and paste the data from a financial website.

I want to find the 5 year and the 10 year growth rate of the sales, equity, EPS and free cash flow.

[Scot_Taylor]

Here’s the formula that I used for the single year.

[Scot_Taylor]

Also, if it helps, this is the formula from Excel that I need to replicate. Just can’t figure out how to make it work in Coda.

5 Year EPS ((13.99 / 5.49)^(1/5) - 1)*100

I know that I need to use the Power( ) formula but I can’t figure out how to represent the exponent.

[Christiaan_Huizer]

@Scot_Taylor , the structure of your table defines how easily you can make it work for you. I remember an observation of @Federico.Stefanato in this post:

Also, Coda - as well as other relational oriented data tools - favorites a row-based approach rather that a column-oriented.
Meaning that it is usually easier add a row than a column and if you follow this principle you’ll take full advantage of the tool (also provided in the sample).

This means that we can reorder the data you provided as below and also get the delta values applying the logic you see :

The logic you described I do not master nor do I understand your intentions , maybe @Agile_Dynamics knows how to deal with it.

5 Year EPS ((13.99 / 5.49)^(1/5) - 1)*100

Anyway the structure of your table is the starting point.

Hope it helps (a bit) , Cheers, Christiaan

[Scot_Taylor]

Thank you so much for taking the time to formulate this reply Christiaan!

There is so much to learn about how formulas work in Coda and you have given me a lot of good information here. Unfortunately, it doesn’t appear to provide the answer that I am looking for.

The purpose of the doc that I am creating is to evaluate potential investment opportunities. The data that I am inserting is gathered from a financial website and simply pasted into Coda, so I don’t need to calculate the growth from year to year, just the percentage of growth over specific periods of time.

The value that I am after is the Compounded Annual Growth Rate (CAGR).

The formula in Excel is:

EB/BB^(1/n)-1

This is what it looks like in Notion, if that is helpful in any way:

I truly appreciate your help!

[Christiaan_Huizer]

@Christiaan_Huizer

5 Year EPS ((13.99 / 5.49)^(1/5) - 1)*100

wel @Scot_Taylor , I like to learn new stuff.
What is according to the other applications the outcome you would like to see ?

[Agile_Dynamics]

in most programming languages the raising of x to the power y is

x^y

in notion this becomes

pow( x , y )

and in Coda it is

Power( x, y )

so your formula

becomes

Round(
    10000 * 
    Power( 
        EndValue / StartValue ,
        1 / Years
    )
    -1
)
/ 10000

max

PS:

The inverse of Power() is Root() but Coda does not have that function.

So, if you do need to get the Nth root of X you need to use

Exponent( Ln( X ) / N )

ie: get the log of X
divide that by N
and use the inverse of Ln(), which is Exponent()
to bring it back to a real number

max

[Scot_Taylor]

That worked! Thank you so much @Agile_Dynamics and @Christiaan_Huizer, you guys are the best!

I did have to change the position of the -1 in the formula from:

10000 *
Power(
EndValue / StartValue ,
1 / Years
)
-1
)
/ 10000

to

10000 *
Power(
EndValue / StartValue ,
1 / Years
)
/ 10000
)
-1

[Hendrik_TnB]

Very informative! Great as usual @Agile_Dynamics & @Christiaan_Huizer

[Breno_Nunes]

Hi, @Agile_Dynamics. I believe you can do root() in a simpler manner.

Y√ X = X^(1/Y) = Power(X,Quotient(1,Y) )

I still have bad dreams of my high school teacher class on neperian number(e) and I try to avoid that route.

[Scot_Taylor]

Quick followup question for @Agile_Dynamics or anyone else that might be able to advise.

This formula works perfectly for making the calculation but there is still something that is not quite right. I have created this as a template to be used when evaluating investments.

I have added a simple Conditional Format to turn the cell green when the result is above 10% and red when it is below 10%. This works just fine on the 1 year column, which is a much simpler formula, but does not work on the 5 year and 10 year columns.

When the data cells are empty, this is what the formula cells look like.

Also, when I look at the formula for the 5 year and 10 year average once the template has been inserted into a page and data added to the table, I get the following error message.

The output is correct and there are no indications that there is an error unless you actually look at the formula itself.

Thank you all once again!

[Jean_Pierre_Traets]

Dear @Scot_Taylor,

The “wrong argument type” message is to my opinion because the columns 2021 and 2017 are with the property “text” and accordingly to formula for Power() it expecting a Number property for the columns 2021 & 2017.

Could you clarify the formula used for 1 year conditional formatting, or even a dummy doc without sensitive data to give a helping and to the one picking up on your request?

[Scot_Taylor]

Yes sir, for sure…thanks for your help @Jean_Pierre_Traets!

I’m new to Coda so I’m not sure how to include a dummy doc but I can figure that out if necessary.

The formula for conditional formatting is exactly the same as the 5 year and 10 year. The original formula to calculate the 1 year CAGR is:

That’s odd about the formula showing those columns as text. In the template and in the actual document those columns are all set up as $ numbers.

[Jean_Pierre_Traets]

Dear @Scot_Taylor

How to share your dummy doc can be found here;

Sharing your doc

A proper solution needs sometimes also for us just to play to find the solution.

Hopefully soon a solution will pop-up

[Scot_Taylor]

Perfect, thanks again for your help @Jean_Pierre_Traets!

[Scot_Taylor]

Actually, never mind! I just figured it out.

I forgot to add the value of 10 to the “is less than” part the formulas. Such a noob…