date - MS Access Qry Function to create a rollout schedule

Question

I was wondering if someone can assist me with a function I can use in my query which will allow me to calculate the release amount of a prepayment each month until the prepayments dates elapse.

Example: current period date: 31 Dec 2020

Prepayment Value $170 Term Start Date: 07 April 2020 Term End Date: 06 April 2021

So , as we are in December the amount to be recognised would be equal to the % of the term that has elapsed. I.e. 31 Dec 2020 minus 07 April 2020 = 269/365 days have to be recognised. Thus (269/365)*$170 = $125

I will require a field which tells me the remainder left to recognise I.e $45

then I need fields which lets call “Month +1, month+2 and Month+3” etc In this case month +1 will need the extra amount that will release this will be $14. this needs to go on until the $45 draws down to 0 in Month+4

Would appreciate any help.

question from:https://stackoverflow.com/questions/65937712/ms-access-qry-function-to-create-a-rollout-schedule

Answer 1

Waitting for answers