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 where:
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
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
