Cumlative Annual % of P/L based on Quarterly Field $Amounts

[Tom_David]

I'm stuck. I need a field type and formula to display the cumulative annual profit or loss (P/L) displayed as a percentage (%) and based on a Q1 starting amount and subsequent amounts entered in Q2, Q3, & Q4 fields.

This seemed easy until I got started. Now I'm digging a mental hole I can't climb out of :-). Help... thank you.

[TheTimeSavingCo]

Could you provide screenshots of your table setups and some example data with expected output?

[Tom_David]

I believe this is the correct layout and output (Notes field). Thank you.

[Tom_David]

FYI, $amounts in Q1, Q2, etc. are quarterly beginning amounts for the total account value/balance. The purpose of the % P/L field is only to display the current cumulative % of profits or loss for the quarters reported.

[TheTimeSavingCo]

Hmm and so it's always based off of the value in 'Q1'?  Does this look right?

 

IF(
  Q4,
  (Q4-Q1)/Q1,
  IF(
    Q3,
    (Q3-Q1)/Q1,
    IF(
      Q2,
      (Q2-Q1)/Q1
    )
  )
)

Link to base