Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Fee calculation DAY COUNT

Posted on 2014-12-20
3
Medium Priority
?
222 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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

610 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