pro-rata figures don't equal original total

G

Guest

I am trying to create a query that allocates values on a pro-rata basis.
However the results never add up to the original value. For instance, 85MM
is allocated across seven funds (L_Alloc). I need to distribute 50MM at the
same proportions as the 85MM to ensure fair allocation. The problem is that
I need to truncate to the nearest 1,000 and from the example below the
allocations (C_Alloc) equate to 50,002 (50,002,000), which is 2,000 units
over.

L_Nom L_Fund L_Alloc C_Nom C_Fund C_Alloc
85000000 LNXA 6002000 50000000 3531
85000000 LNXB 510000 50000000 300
85000000 LNXC 1688000 50000000 993
85000000 LNXD 4800000 50000000 2824
85000000 LNXE 37000000 50000000 21765
85000000 LNXF 15000000 50000000 8824
85000000 LNXG 20000000 50000000 11765

Is there anyway to get the database to round up/down in such a way that the
sum of the allocations equals 50MM? This has been driving me mad. I have
based the allocations on the percentage of the original 85MM which works fine
until the figures are truncated. Please could someone help?
 
J

John Vinson

I am trying to create a query that allocates values on a pro-rata basis.
However the results never add up to the original value. For instance, 85MM
is allocated across seven funds (L_Alloc). I need to distribute 50MM at the
same proportions as the 85MM to ensure fair allocation. The problem is that
I need to truncate to the nearest 1,000 and from the example below the
allocations (C_Alloc) equate to 50,002 (50,002,000), which is 2,000 units
over.

L_Nom L_Fund L_Alloc C_Nom C_Fund C_Alloc
85000000 LNXA 6002000 50000000 3531
85000000 LNXB 510000 50000000 300
85000000 LNXC 1688000 50000000 993
85000000 LNXD 4800000 50000000 2824
85000000 LNXE 37000000 50000000 21765
85000000 LNXF 15000000 50000000 8824
85000000 LNXG 20000000 50000000 11765

Is there anyway to get the database to round up/down in such a way that the
sum of the allocations equals 50MM? This has been driving me mad. I have
based the allocations on the percentage of the original 85MM which works fine
until the figures are truncated. Please could someone help?
Click to expand...

Truncate the value in the expression that divides the value. To
truncate to the nearest 1000, use

1000 * Fix(<expression> / 1000)


John W. Vinson[MVP]
 
Top