Premium Calculation - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Premium Calculation

Nathan Maturine

4 years ago

It appears I may have posted this question in the wrong forum. I do apologize.

Here it is....
_______________________________________
I need to calculate the amount of premiums paid by a customer. Over the life of the policy, the customer's premium will change.
The problem i have is the original creator of the database application did not really account for this, so the only way to tell of a premium change is when the next payment is made. There is a payments table which would show what the premium is at the time of the payment.
What I would like to do is:
1. Determine when the Premium changed
2. Calculate how much premium was supposed to be paid based on each premium amount

E.G
If the monthly premium for the Policy was $88.10 when the policy was issued on Jan 15 2016,
Then the premium changed to $102.12 on March 15 2018,
Then the premium changed to $120.18 on August 15 2021.
I need to determine that in the first instance $88.10 was to be paid for 26 Months (No of months between issue date and first rate change) so expected premiums were $2290.60, however the customer missed some payments so the actual amount paid $1762.
And so on for each time the premium changed.
In the payments table, there is issue date, amount paid, premium, payment date, transaction date, among other fields.

Adam Schwanz @Reply

4 years ago

So if you have 26 entries of $88.10, however many entries of $102.12, and however many of $120.18 why can't you just add up all those amounts with an aggregate query? Or are you trying to do something beyond this and I'm not getting it?

Assuming you have your database setup how I would think you'd do it, you should be able to see there were 26 entries for a payment of $88.10. And use some kind of IsPaid checkbox or field to find out if they paid, then you could easily see both the "due" and the "paid". If this is how you have your database setup, I don't see how the premium change would matter, you would still have a field saying $102.12 is due on this day and if it ever got paid or not. If you have some other kind of setup or that isn't what you were asking can you elaborate some more?

Nathan Maturine OP @Reply

4 years ago

In the example I gave, i typically will not have 26 transactions as a customer may miss a month or pay short or double up on payments. Since I have inherited this database, unfortunately it is not setup that way. All that occurs is a transaction gets written to the table with the amount paid and other details, there is no mechanism that really tells if what was received was a full payment.
Would it be possible to create a query in VBA (or manually) to determine the number of different premiums and the corresponding dates when it first occurs. Then using those results calculate the difference in the dates then use that result to calculate the amount I should have received. Then repeat for the actual amount paid?
I know that sounds awfully clumsy (not sure if its doable), but i'm hoping there's a 'better' way to accomplish this.

Adam Schwanz @Reply

4 years ago

Yikes, you might still be able to get the information you want with an Aggregate Query

Doing group by the payment amount you can get the number of times they paid and how much, then you'd need to make some custom expressions that look like a mess.

'Find the first date with a changed amount
ChangeDate: DMIN("PayDate","Table","PaidDate>" & PaidDate & " and Amount<>" & Amount)

'Find the first month paid at that value
FirstPayment: DMIN("PayDate","Table","Amount=" & Amount)

'Then use DateDiff to find the difference of those two fields (you might need to make a second query at this point)
NumberMonths: DateDiff("m",ChangeDate,FirstPayment)

'THEN find out the amount they should of paid
ExpectedPay: NumberMonths * Amount

'now find out the difference using a sum from the aggregate query
Difference: SumOfAmount - ExpectedPay

	Adam Schwanz @Reply 4 years ago
Also, apologies if I butchered the syntax here in my code (hurried) or if I'm doing this in a more complicated way if I misunderstood you. But it sounds like you might have a lot to do. This should get you an idea at least though if I understand correctly.

	Nathan Maturine OP @Reply 4 years ago
Thank you...I will certainly try your suggestion. Thank you

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View