• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Calculating Total Amount of future payments based on an expiration date.

I need to determine the total dollar amount of payments made from today to a contract expiration date.

The fields are as follows:

CTRCT_EXPIRATION_DATE

FEBRUARY_PMT

These are ongoing charges paid every month with no interest or increases involved.  Just need to find out (based on the February Payment amount how much we will pay out from February up until the expiration dates of the contract for each record.

Please help!  Need this ASAP for a meeting!

Thanks!
0
brent9696
Asked:
brent9696
1 Solution
 
Steve BinkCommented:
If the dollar amount is the same each month, count the difference in month between February and the expire date, and multiply by the payment amount.

DateDiff("m","2/1/04",[CTRCT_EXPIRATION_DATE]) * [FEBRUARY_PMT]
0
 
jadedataMS Access Systems CreatorCommented:
Greetings brent9696!

  Are these the only fields in this table??
  We're gonna have a bit of a problem figuring this out...

  The table structure itself appears to have some design challenges as well that will complicate this..
 
regards
jack
0
 
brent9696Author Commented:
jadedata@  No, there are many other fields but my query pulls in some simple contact information.  The only fields to be used to calculate are the ones I provided.

routinet@  Your answer looks good and makes sense, but when I plugged it into the query in the QBE pane it didn't work.  In field I entered...

Total Payments: DateDiff("m","2/1/04",[CTRCT_EXPIRATION_DATE]) * [FEBRUARY_PMT]

I get a parameter box.  Is this because the 2 fields are from different tables?

CTRCT_EXPIRATION is from a table called tblContracts
FEBRUARY_PMT is from another table called tblPMTs

I linked both in this query to calculate the total payments based on expiration.

Thanks, Brent9696
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Steve BinkCommented:
That's just a matter of the calling convention.  Post the query and we should be able to correct that line.
0
 
brent9696Author Commented:
Actually, I spelled one of the fields wrong (on this website) so when I cut & paste it gave me the parameter box.  Routinet, your's worked and calulates great in Access, but when I export it to Excel, the number changes completly.  I don't undestand why.  It's the last thing I have to do to get this thing out.

Thanks,

Brent9696
0
 
dds110Commented:
What does the number look like in the excel sheet.  You may need to change the formatting in excel (hate excel, hate excel, hate excel....did I say that outloud).
0
 
brent9696Author Commented:
Thanks, routinet@.  All is well!  It was just sorted different so the Excel Export worked fine after all.  But, now I was just told that there will be a bi-annual rate increase of 6.5%.  How would I add that to your formula?

Total Payments: DateDiff("m","2/1/04",[CTRCT_EXPIRATION_DATE]) * [FEBRUARY_PMT]

The crocodiles are circling here.  Thanks for the help.

Brent9696

0
 
Steve BinkCommented:
Change the field to pull from the following external function.  

Total Payments: =TotalPlusRates(DateDiff("m","2/1/04",[CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])

Function TotalPlusRates(numMonths as integer, FirstPayment as currency) as currency
dim newtotal
dim x
dim curpayment
const newrate = .065

curpayment=firstpayment
newtotal=0
for x = 1 to numMonths
  newtotal = newtotal + curpayment
  if (x mod 6)=0 then curpayment = curpayment + (curpayment * newrate)
next

TotalPlusRates = newtotal

End Function

0
 
brent9696Author Commented:
Would it make a difference to the formula if the rate increases are only in June & Dec every year?
0
 
brent9696Author Commented:
routinet@  Sorry, I am a newbie.  What do you mean by pulling from an external function?
0
 
Steve BinkCommented:
What I mean is instead of binding your control to a specific field, bind to the function TotalPlusRates that you have to create.  You do that by replacing the formula I gave you the first time with this:

TotalPlusRates(DateDiff("m","2/1/04",[CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])


The function that I posted should be cut/paste into a module.  Be sure to put a "PUBLIC" declaration in front of the function:

Public Function TotalPlusRates(numMonths as integer, FirstPayment as currency) as currency
0
 
Steve BinkCommented:
doh!  yes, it will make a difference if the rate increase is done on a specific month.  The new function is shown below.  For the field, replace your original text with this:

TotalPlusRates(Month("2/1/04"),Month([CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])

You can replace the first argument (month("2/1/04")) with whatever start date you want, or even a field from the table that has the start date in it.  For example, if you had a field for [CTRCT_START_DATE], it would look like this:

TotalPlusRates(Month([CTRCT_START_DATE]),Month([CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])

The following code should be pasted into any module in your db:


Public Function TotalPlusRates(BeginMonth, EndMonth as integer, FirstPayment as currency) as currency
dim newtotal
dim x
dim curpayment
const newrate = .065

curpayment=firstpayment
newtotal=0
for x = beginmonth to endmonth
  newtotal = newtotal + curpayment
''''''''''''''
' the next line is the test for Jun/Dec.  Change the month numbers to test for other months
''''''''''''
  if ((x=6) or (x=12)) then curpayment = curpayment + (curpayment * newrate)
next

TotalPlusRates = newtotal

End Function
0
 
brent9696Author Commented:
It's not working.  I'm sure it's me though but here's what I did:

I created a module and saved it as TotalPlusRates.  In the module I pasted this:

Public Function TotalPlusRates(BeginMonth, EndMonth as integer, FirstPayment as currency) as currency
dim newtotal
dim x
dim curpayment
const newrate = .065

curpayment=firstpayment
newtotal=0
for x = beginmonth to endmonth
  newtotal = newtotal + curpayment
''''''''''''''
' the next line is the test for Jun/Dec.  Change the month numbers to test for other months
''''''''''''
  if ((x=6) or (x=12)) then curpayment = curpayment + (curpayment * newrate)
next

TotalPlusRates = newtotal

End Function


Then, In the QBE Pane in the "Field Row" I pasted this:

TotalPlusRates(Month("2/1/04"),Month([CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])

Next I hit the "Run" button but I get this error:

"Undefined TotalPlusRates in Expression".

I feel like I'm almost there.  Thanks!
0
 
Steve BinkCommented:
Rename the module to something different, like RatesModule or something.  Try to avoid using the same name for more than one object, especially a parent/child.  Make sure you save the module.  If necessary, close the database saving any changes.  Go back into the QBE pane, and for the field where you put the expression, delete whatever is in there and paste this instead:

[Total Payments]: TotalPlusRates(Month("2/1/04"),Month([CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])

0
 
brent9696Author Commented:
I did all of the above, but now error says "[Total Payments] is not a valid name".
0
 
Steve BinkCommented:
Change it to another name.  Try this:

[TotalContract]: TotalPlusRates(Month("2/1/04"),Month([CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT])

Try to run it again.  If it gives you a code error, hit the debug button and paste the error msg and the line causing the error here.
0
 
brent9696Author Commented:
doh!  My bad!  I forgot that I started this forum not using the exact names so I needed to edit the names after copy/paste.

I entered the original, TotalPlusRates(Month("2/1/04"),Month([CTRCT_EXPIRATION_DATE]),[FEBRUARY_PMT]) in the QBE pane and it now runs.  However, the amount look way off.  Much less!

For instance, here is sample data from the results of both queries.  Without an increase (the first query) and With 6.5% increase (new query with the module)

**QUERY WITHOUT AN INCREASE**

CTRCT_EXPIRATION_DTE
11/1/2006
4/30/2004


FEBRUARY_PMT
$286.36
$66.00

Total Pmts
$9,449.88
$132.00

**QUERY WITH INCREASE (using the module)**

CTRCT_EXPIRATION_DTE
11/1/2006
4/30/2004

FEBRUARY_PMT
$286.36
$66.00

Total Pmts
$2,956.667
$198.00

**COMMENTS:**
With the increase query using the TotalPlusRates module it is not calculating correctly.  Examining the data the one expiring in 2006 went down!  Then the one expiring in 4/30/2004 went up a little but it should still be $132 because it expires April 30 which is 2 months before the next increase.

Was I supposed to plug in any other numbers to the code?

Thanks so much,

Brent9696
0
 
Steve BinkCommented:
yupyup...the code I gave you before assumes a contract of no longer than 11 months.  Kinda defeats the purpose huh?  Now that I've had a chance to sit down and recode (and test...duh!), I've come up with a working function.  There are a couple of changes.  First, copy the function (at the end of this post) and paste it over the old one.  Make sure the old is gone completely...this replaces it.  Second, change the function reference in the QBE pane to read:

TotalPlusRates("2/1/04",[CTRCT_EXPIRATION_DATE],[FEBRUARY_PMT])

You can change the "2/1/04" to whatever date you need to be the start date for the function.  Third, remove any line that starts with Debug.Print.  You can leave them in without affecting how it runs, but they are unnecessary and were only for testing the routine.  Below is the debug return for your example using "11/1/06" as the ending date.  You can bring up the Immediate window in the VB editor by hitting Ctrl-G, or selecting [ View | Immediate Window ] from the menubar.

Beginning on: 2/1/04
Ending on: 11/1/2006
Base Payment: 286.36
PayDate       Amount        Total
3/1/2004       286.36        286.36
4/1/2004       286.36        572.72
5/1/2004       286.36        859.08
Added interest for Jun/Dec.  New payment amount: 304.9734
6/1/2004       304.9734      1164.0534
7/1/2004       304.9734      1469.0268
8/1/2004       304.9734      1774.0002
9/1/2004       304.9734      2078.9736
10/1/2004      304.9734      2383.947
11/1/2004      304.9734      2688.9204
Added interest for Jun/Dec.  New payment amount: 324.7967
12/1/2004      324.7967      3013.7171
1/1/2005       324.7967      3338.5138
2/1/2005       324.7967      3663.3105
3/1/2005       324.7967      3988.1072
4/1/2005       324.7967      4312.9039
5/1/2005       324.7967      4637.7006
Added interest for Jun/Dec.  New payment amount: 345.9085
6/1/2005       345.9085      4983.6091
7/1/2005       345.9085      5329.5176
8/1/2005       345.9085      5675.4261
9/1/2005       345.9085      6021.3346
10/1/2005      345.9085      6367.2431
11/1/2005      345.9085      6713.1516
Added interest for Jun/Dec.  New payment amount: 368.3926
12/1/2005      368.3926      7081.5442
1/1/2006       368.3926      7449.9368
2/1/2006       368.3926      7818.3294
3/1/2006       368.3926      8186.722
4/1/2006       368.3926      8555.1146
5/1/2006       368.3926      8923.5072
Added interest for Jun/Dec.  New payment amount: 392.3381
6/1/2006       392.3381      9315.8453
7/1/2006       392.3381      9708.1834
8/1/2006       392.3381      10100.5215
9/1/2006       392.3381      10492.8596
10/1/2006      392.3381      10885.1977
11/1/2006      392.3381      11277.5358
Final Total: 11277.5358

Note that the function adds the rate increase for June BEFORE adding the payment to the total.  If you need the first payment reflecting the rate increase to be in July/January, just switch the line that adds it to the total to a position above the If..Then block.  I tested the function by running this sub, which you can paste into the same module if you want to try it out:

Public Sub TestTotalPlusRates()
TotalPlusRates "2/1/04", "11/1/06", 286.36
End Sub

Last, you can alter the constant "NewRate" to reflect whatever interest rate you would like to charge.  Right now, it is set at 6.5%.  If you're feeling especially froggy, you can even try altering the routine to use the interest rate passed as an argument.  That would let you determine the interest rate on the fly without having to edit the routine each time.  As a final note, the return line rounds out the final total to 2 decimal places.  If you would like the raw 4-decimal amount returned, uncomment the line beneath it.  Sorry this took so long...I've had a rough week and didn't really have to time to pay the proper attention to the coding.


Public Function TotalPlusRates(BeginMonth, EndMonth As Date, FirstPayment As Currency) As Currency
Dim NewTotal, CurPayment As Currency
Dim x   ' a counter
Const NewRate = 0.065

CurPayment = FirstPayment
NewTotal = 0
Debug.Print "Beginning on: " & BeginMonth
Debug.Print "Ending on: " & EndMonth
Debug.Print "Base Payment: " & FirstPayment
Debug.Print "PayDate", "Amount", "Total"
For x = 1 To DateDiff("m", BeginMonth, EndMonth)
  ''''''''''''''''''''''''''''''''''''''
  ' This If..Then block processes the rate increase
  ''''''''''''''''''''''''''''''''''''''
  If ((Month(DateAdd("m", x, BeginMonth)) Mod 6) = 0) Then
    CurPayment = CurPayment + (CurPayment * NewRate)
    Debug.Print "Added interest for Jun/Dec.  New payment amount: " & CurPayment
  End If
  '''''''''''''''''''''''''''''''''''''
  ' This line adds the current month's payment to the total
  '''''''''''''''''''''''''''''''''''''
  NewTotal = NewTotal + CurPayment
  Debug.Print DateAdd("m", x, BeginMonth), CurPayment, NewTotal
Next

Debug.Print "Final Total: " & NewTotal
'''''''''''''''''''''''''''''
' This line rounds out the total to 2 decimal places
' To use 4 decimal places uncomment the line beneath it
'''''''''''''''''''''''''''''
TotalPlusRates = Int((NewTotal * 100) + 0.5) / 100
' TotalPlusRates = NewTotal

End Function
0
 
brent9696Author Commented:
routinet@,

I can hardly wait to try this but I'm away from my database today.  I will do it later tonight or tomorrow morning.

Thanks!

Brent9696
0
 
brent9696Author Commented:
routinet@!  This works great, thank you so very much!  You helped me with this immediate problem and now I have something to go on to build into my permanent reports.  You are awesome!

Thanks again!

Brent9696
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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