Solved

Fee calculation DAY COUNT

Posted on 2014-12-20
3
169 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now