Help with Solver
 

Goodmorning,

Could you please help me with the following?

I have the following data
Development Year
Accident
Year 0 1 2 3 4
2004 23,2 10,6 3,5 1,6 0,2
2005 25,8 11,5 5,6 2,7
2006 22,1 8,2 0,4
2007 35,9 7,1
2008 34,9

the above triangular data refers to a stream of Payments ( Cij) where
i : Accident Year (rows)
j : Development Year (columns)

e.g. C13 = 1,6 , C01 = 7,1

In order to fill the missing values in the above triangle (e..g C24,
C33, C34, ...C54) i assume that Cij can be approximated by quantities
Xi*Pj whe

Xi : is the total amount of payments in respect of Accident Year i
Pj : is the fixed proportion of the amount Xi paid in Development Year
j, i.e. in payment year i+j

From the data I want to estimate the Xi and Pj, which are, of course,
identified up to a multiplicative constant. The parameters will be
fully identified if, for instance we set:

Sum(Pj)=1 the summation from j=0 to k.

In other words I am looking for Xi and Pj that minimize:

Sum[(Cij-Xi*Pj)^2]

where the summation is taken ocer all occupied cells.

Solutions are given by the following equations:

Xi = Sum(Cij*Pj) / Sum(Pj^2)

where both sums are for all j's


Pj = Sum(Cij*Xi) / Sum(Xi^2)

where both sums are for all i's

where the summations are taken over the occupied cells only,

and under the constrain that Sum(Pj)=1 the summation from j=0 to k.

In other words I would like to use Solver in order to arrive at the
following solution

i 0 1 2 3 4
Xi .. .. .. .. ..
Pj .. .. .. .. ..


I would be grateful if you could provide me with some hints

Yours sincerely

Nikos