Solved

Fee calculation DAY COUNT

Posted on 2014-12-20
3
192 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
  • 2
3 Comments
 
LVL 49

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 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