Query to create payment schedule (1 Viewer)

AccessNovice123 · Sep 13, 2017

Hello,
I have a form with loan information containing fields: first_pay_date, last_pay_date, pay_amt, term, and balance. The term is not standard and ranges from 12-36. I need to do the following things all based around today's date:
1. Calculate the next payment date based on today's date in an added field, pay_date.
2. Calculate the balance remaining. The balance is reduced by the pay_amt on the pay_date for the orig_term. This could be a new field, bal_remaining.
3. Need to calculate the term_remaining based on the number of payments made and subtracting that from the orig_term.

The original term is the number of months between first_pay_date and last_pay date.

I have tried using expressions with the DateDiff and DateAdd in IIf statements but it is too limiting.
I looked at VBA, possibly a loop but I need the calculation to be done everytime i open the form, whenever I open the form. Maybe a make table query would work but I am little unsure of a way to calculate the pay_date when the term changes.

Can someone please point me in the right direction? Any help is appreciated. Thank you.

plog · Sep 13, 2017

Can you provide some sample data to demonstrate?

AccessNovice123 · Sep 13, 2017

Code:

first_pay_date   last_pay_date   pay_amount   orig_term   balance    

9/15/2017      8/15/2020         $169.10        36        $6,087.60       
9/5/2017       8/5/2020          $84.54         36        $3,043.44       
9/5/2017       8/5/2019          $97.09         24        $2,330.16       
8/25/2017      7/25/2020         $162.16        36        $5,675.60       
9/5/2017       8/5/2019          $107.54        24        $2,580.96       
9/5/2017       8/5/2019          $133.34        24        $3,200.16     
8/25/2017      7/25/2020         $143.12        36        $5,152.32 
9/15/2017      8/15/2018         $53.62         12        $643.44

For example: with the first row, I would expect pay_date = 9/15/2017 based on today's date but for the second row I would expect pay_date = 10/5/2017.
First row, bal_remaining = $6087.60 but second row the bal_remaining = $2958.90 because one payment has been made. Also for the second row, I would expect term_remaining = 35.

Peter Reid · Sep 14, 2017

You learn (or are reminded of) things each day.

DateDiff doesn't calculate 'full' intervals so you need to remove a month if the day of the first pay date is greater than today's day

eg

Code:

SELECT *
, DateDiff("m",[first_pay_date],Date())-IIf(Day([first_pay_date])>Day(Date()),1,0)+1 AS Payments
, [Balance]-([Payments]*[pay_amount]) AS LatestBalance
FROM YourTable;

AccessNovice123 · Sep 14, 2017

Thank you so much! That worked perfectly to calculate my remaining balance. I tested it by changing dates to make them older. I need to add in a statement to account for when balances have been paid in full but I can handle that. I appreciate the help the balance remaining part of my question.

Query to create payment schedule (1 Viewer)

AccessNovice123

Registered User.

plog

Banishment Pending

AccessNovice123

Registered User.

Peter Reid

Registered User.

AccessNovice123

Registered User.

Similar threads

Users who are viewing this thread