2

In step 7 in this tableau tutorial he uses the number of periods as 12 I believe this number should change. So for instance if we started with January to March, the number of period would be 3. Then let's say we calculated the CAGR from January to April, the number of periods would be 4. Therefore, the number of periods in the CAGR formula in the tableau tutorial should be changing and not stay at 12. Do you agree with me?

Btw, CAGR formula is (Ending Balance/ Beginning Balance) ^ (1/n) -1, where n is the number of periods. (Usually this is used for yearly returns, but you can use it for monthly or quarterly, etc. - so instead of each year being 1 period, each month would be 1 period I believe) What do you think?

2
  • This is about using a piece of software, how is this related to personal finance? January to March is 3 (January = 1, February = 2, March = 3) 3 months, not 3-1=2. Jun 11, 2022 at 18:23
  • Ok I edited it, I didn't think that one through properly. Anyways, the underlying concept of CAGR is related to personal finances whether it is used in a software or not. Jun 11, 2022 at 18:35

2 Answers 2

2

In the exponent zn(1/(([Index Across]-[Index Down])/12))

if [Index Across]-[Index Down] must be divided by 12 then the logic is OK.

For example 5% in Jan and 5% in Feb on £100 resulting in £110.25

average monthly return = (110.25/100)^(1/(24/12)) - 1 = 0.05

In this case [Index Across]-[Index Down] = 24 (odd indeed, but could be)

Then with zn = 12 to annualise

CAGR = (110.25/100)^(zn(1/(24/12))) - 1 = 79.6%

Which would be technically correct.

1.05^12 - 1 = 0.796
0

This is what I posted on the tutorial. I figured out how to fix it! (and yes, I believe the n of periods should change.)

The CAGR in this tutorial is not right.

One needs to create a new Calculated Field called "Number of Periods" and have the formula as such -> [Index Across] - [Index Down] + 1.

Therefore, the CAGR equation should be: If ([Index Across]>[Index Down]) then power([Close Price Down]/[Close Price Across], zn(1/(([Index Across]-[Index Down])/([Number of Periods]))))-1 else 0 END.

Why? The number of periods is not constant at 12 all because it is months (months is just a time period like years, therefore January to March would be 3 periods, while January to April would be 4 periods).

Final result for me!: Tutorial Fix

3
  • Why not just the exponent zn(1/[Number of Periods])? Jun 11, 2022 at 20:18
  • Maybe ([Index Across]-[Index Down])/12 actually produces [Number of Periods]. Jun 11, 2022 at 20:20
  • I'll tell you what, it did not produce the proper results and I know my solution absolutely works. Jun 12, 2022 at 13:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .