?
Solved

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

Posted on 2004-04-01
20
Medium Priority
?
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
20 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10735590
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
ID: 10735591
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
ID: 10735692
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 51

Expert Comment

by:Steve Bink
ID: 10735770
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
ID: 10735824
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
ID: 10735878
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
ID: 10736017
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 51

Expert Comment

by:Steve Bink
ID: 10736183
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
ID: 10736306
Would it make a difference to the formula if the rate increases are only in June & Dec every year?
0
 

Author Comment

by:brent9696
ID: 10736327
routinet@  Sorry, I am a newbie.  What do you mean by pulling from an external function?
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10736406
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 51

Expert Comment

by:Steve Bink
ID: 10736474
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
ID: 10736737
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 51

Expert Comment

by:Steve Bink
ID: 10736841
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
ID: 10736918
I did all of the above, but now error says "[Total Payments] is not a valid name".
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10736979
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
ID: 10737316
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 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 10745984
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
ID: 10760114
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
ID: 10770228
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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