5 Replies Latest reply on Aug 16, 2013 4:22 AM by kettan

    Chain-ladder table calculation - Question 1

    kettan

      OBS!  This question is continued in   Chain-ladder table calculation - Question 2

       

       

      I want to make a chain-ladder table, and expect to ask for help in several rounds.

       

      Question:

      How do I change the formula in the attached workbook so it calculates only the blue area?

       

      This is current table calculation as marked red in print screen below:

      WINDOW_SUM(SUM([Amount]), FIRST(), 0) / WINDOW_SUM(SUM([Amount]), FIRST(), -1)

       

      chain-ladder table calculations.png

        • 1. Re: Chain-ladder table calculation - Question 1
          kettan

          The formula below works, but has hard-coded values for the dimensions.

          What I need is a dynamic formula without hard-coded conditions.

           

          IF ATTR([Development Year]) = 1 THEN

             WINDOW_SUM(SUM(IF [Incident Year]<=2011 THEN [Amount] END), FIRST(), 0) /

             WINDOW_SUM(SUM(IF [Incident Year]<=2011 THEN [Amount] END), FIRST(), -1)

          ELSEIF ATTR([Development Year]) = 2 THEN

             WINDOW_SUM(SUM(IF [Incident Year]<=2010 THEN [Amount] END), FIRST(), 0) /

             WINDOW_SUM(SUM(IF [Incident Year]<=2010 THEN [Amount] END), FIRST(), -1)

          ELSEIF ATTR([Development Year]) = 3 THEN

             WINDOW_SUM(SUM(IF [Incident Year]<=2009 THEN [Amount] END), FIRST(), 0) /

             WINDOW_SUM(SUM(IF [Incident Year]<=2009 THEN [Amount] END), FIRST(), -1)

          END

           

          chain-ladder table calculation Q1 v1a.png

          • 2. Re: Chain-ladder table calculation - Question 1
            Shawn Wallwork

            Johan I spent a bit of time looking at this and didn't get anywhere. I mention this because I didn't want you to think none of us tried.

             

            Cheers,

             

            BB SW.png

            1 of 1 people found this helpful
            • 3. Re: Chain-ladder table calculation - Question 1
              Dan Huff

              Johan--

               

              The attached has the answer correct. This is a very complicated calculation. I am happy to add captions explaining each of the calcs but for now I just wanted to give you your solution.

               

              Look for an update later tonight or tomorrow.

               

              Hope this helps,

               

              Dan

              2 of 2 people found this helpful
              • 4. Re: Chain-ladder table calculation - Question 1
                kettan

                Thanks Dan,

                 

                These are precious formulas for learning table calculations and hopefully also the first step toward a workable chain-ladder IBNR for the task at hand.

                 

                I will prepare a question 2 which will build on your calculations and also share an Excel spreadsheet with working formulas for easy comparison.

                 

                Jóhan

                • 5. Re: Chain-ladder table calculation - Question 1
                  kettan

                  Thanks Shawn, this was heart-warming.


                  1. Getting feedback without a workable solution feels much better than being ignored.
                  2. Knowing it probably can't be done or will be very time-consuming also makes it easier to switch to a task with high impact and low effort rather than spending more time on a task that needs high effort and possibly no impact.
                  3. It also makes me feel less stupid that I am not alone having difficulties with table calculations.
                    That said, I will be among the first in the line to buy the book "Tableau Table Calculations for Dummies" when it hopefully is released.


                  Jóhan

                   

                  Ps. 2 points for helpful answer which improved my mood