Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Fee calculation DAY COUNT

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
pdvsa
Asked:
pdvsa
  • 2
1 Solution
 
Gustav BrockCIOCommented:
Here is how to get the day count.
Then you can apply your rate and then the fee calculation.

/gustav
DayCountIssue-v1.accdb
0
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now