Solved

Fee calculation DAY COUNT

Posted on 2014-12-20
3
202 Views
Last Modified: 2014-12-21
Experts,

I have a fee that is not calculating how I need it to calculate.  
I know what the problem is but not certain how to fix it.
I have many [Amount] that is disbursed on a [DisbRecdDate]
I need to calculate a Fee (CommFeeAmt_Calc)
The Day Count (see below) is what is the problem.

for example,
I have 10 disbursements and they are received on different [DisbRecdDate] dates.
I need to calculate the Fee based on the Day count from the previous [DisbRecdDate] to the current [DisbRecdDate] and multiply that Day Count times the current [Ending Balance].

[Amount]   [DisbRecdDate]     [EndingBalance]
10                   1/1/10                       10
5                     2/1/10                        15
10                    3/1/11                        25

Fee for the EndingBalance on 2/1/10 would equal
=[Ending Balance]*[Rate]*([Day Count]/360)
=15*[rate]*(2/1/10 - 1/1/10) / 360

in the attached, you can see Q.Disbursement.Day Count: [DisbRecdDate]-[DisbRQDate] is how I am handling it now but it is wrong.  I would need something like Day Count: [DisbRecdDate]- the previous [DisbRecdDate]

I hope it makes sense.  Let me know if you need additonal explanation.
Please see attached db and open Q_Available

thank you
DayCountIssue-v1.accdb
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40511158
Here is how to get the day count.
Then you can apply your rate and then the fee calculation.

/gustav
DayCountIssue-v1.accdb
0
 

Author Comment

by:pdvsa
ID: 40511644
Wow that is amazing.  Thank you Gustav.  You made easy work of that one.  


TrueDateCount: DateDiff("d",(Select Max(DisbRecdDate) From tblDisbursement_Amounts As T Where T.DisbRecdDate < tblDisbursement_Amounts.DisbRecdDate And T.Type=tblDisbursement_Amounts.Type),[DisbRecdDate])
0
 

Author Comment

by:pdvsa
ID: 40511661
Gustav,

I think I am going to ask a follow up to this question.
I believe I need to tweak the formula.  
I shall post in  a few minutes.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question