Solved

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

Posted on 2004-04-01
20
370 Views
Last Modified: 2006-11-17
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
Comment
Question by:brent9696
20 Comments
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
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
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
That's just a matter of the calling convention.  Post the query and we should be able to correct that line.
0
 

Author Comment

by:brent9696
Comment Utility
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
 
LVL 8

Expert Comment

by:dds110
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
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
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
Would it make a difference to the formula if the rate increases are only in June & Dec every year?
0
 

Author Comment

by:brent9696
Comment Utility
routinet@  Sorry, I am a newbie.  What do you mean by pulling from an external function?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
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
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
I did all of the above, but now error says "[Total Payments] is not a valid name".
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
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
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
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
 

Author Comment

by:brent9696
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Error message when attempting to run query 14 28
Dealing with Null in VBA sql statement 9 23
Resize text 4 13
Error in SQL Query 36 33
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now