C
CFitz
I'll try to explain this the best that I can. Here's a basic example of what
I'm working with...
I currently have a large file of all suits from last year. This file
contains the date filed and the balance of the account. I have a second file
that contains the payment date, payment amount and suit file date for all
payments last year. Based on the date purchased and the payment dates, we
want to figure out what the recovery rate on our purchase is in 30 day
increments. Below is a shortened 6 month version of the layout with play
numbers.
Total $ of Suits
Jan Feb Mar Apr May Jun Total
500 450 750 600 550 400 3250
Total $ Collected
Days from File
Jan Feb Mar Apr May Jun Total
0-30 10 15 10 20 15 15 85
31-60 15 20 15 20 15 85
61-90 20 30 15 25 90
91-120 20 25 15 60
121-150 15 20 35
151-180 10 10
Total % Recovered
Days From File
Jan Feb Mar Apr May Jun Total
0-30 2% 3% 1% 3% 3% 4% 2.6%
31-60 3% 4% 2% 3% 3% 2.6%
61-90 4% 7% 2% 4% 2.8%
91-120 4% 6% 2% 1.8%
121-150 3% 4% 1.1%
151-180 2% 0.3%
The percentages come from the amount collected over the amount filed that
month.
All we really want are the Total Recovery % without having to manually fill
in everything. Is there any function that will singlehandly give us the
Total Recovery % without having to do use several different equations to get
the monthly collected, total collected, monthly filed, total filed, etc.? I
originally built access queries but they became too complex after the 421-450
date range. My manager was hoping there is some statistical or financial
function to do this. Thanks!
I'm working with...
I currently have a large file of all suits from last year. This file
contains the date filed and the balance of the account. I have a second file
that contains the payment date, payment amount and suit file date for all
payments last year. Based on the date purchased and the payment dates, we
want to figure out what the recovery rate on our purchase is in 30 day
increments. Below is a shortened 6 month version of the layout with play
numbers.
Total $ of Suits
Jan Feb Mar Apr May Jun Total
500 450 750 600 550 400 3250
Total $ Collected
Days from File
Jan Feb Mar Apr May Jun Total
0-30 10 15 10 20 15 15 85
31-60 15 20 15 20 15 85
61-90 20 30 15 25 90
91-120 20 25 15 60
121-150 15 20 35
151-180 10 10
Total % Recovered
Days From File
Jan Feb Mar Apr May Jun Total
0-30 2% 3% 1% 3% 3% 4% 2.6%
31-60 3% 4% 2% 3% 3% 2.6%
61-90 4% 7% 2% 4% 2.8%
91-120 4% 6% 2% 1.8%
121-150 3% 4% 1.1%
151-180 2% 0.3%
The percentages come from the amount collected over the amount filed that
month.
All we really want are the Total Recovery % without having to manually fill
in everything. Is there any function that will singlehandly give us the
Total Recovery % without having to do use several different equations to get
the monthly collected, total collected, monthly filed, total filed, etc.? I
originally built access queries but they became too complex after the 421-450
date range. My manager was hoping there is some statistical or financial
function to do this. Thanks!