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.
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