Cost calculated due to inflation

[cos]

i have two dates:
StartDate
EndDate

I also have a contract made with a cient so that he pays me one sum on the first month, and that sum multipies every month (due to the inflation, which figures are stored in another table), and i will need a cost at the current month, even if its between the StartDate and the EndDate.

the trick i need to do is to be abe to multiply various values over and over between two dates.

for example:
StartDate is 01/01/12 and the EndDate is 01/01/13.
The first payment is $1000
and i need to know how much to charge this client on the 01/07/12

any ideas at all? thank you very much if you can come up with an idea!!

 

[G37Sam]

Assume you have a monthly inflation rate of 1%. You started with 1,000 in the first month. Then your payment after 5 months would be 1,000 * (1.01) ^ 5

How do you plan on using this information in your database though?

 

[cos]

That's a good idea! However, i need to take into considoration that the inflation changes every month.. so i can't use "to the power of" idea..

any other posible thoughts? im open to all ideas...

 

[Galaxiom]

Create a recordset with the inflation rate for the months to be included in the calculation. Order by the month.

Step through the recordset multiplying the amount by each of the months' inflation value in turn.

I would impliment this in a function.

 

[cos]

Galaxiom... you are a genius!

what i will try to do, is adapt your idea in to:

creating a table: InflationTbl (with no primary key)
01.12|02.12|03.12|04.12|...
_____________________
x% |y% |z% |w% |...


Then create a query: InfltionQry
SortID
StartDate
EndDate
Today: Date()
Pament
Payment1: IIf([StartDate]>[Today],0,IIf(01/01/12<[StartDate]<01/02/12,[Payment],IIf([EndDate]>[Today],0,[Payment]*[01.12])))

Payment2: " "
...

Then create a multy sheet form: InflationFrm
SortID|StartDate|EndDate|Payment|Pament1|Payment2|Payment3|...|Payment 12

so do you think this will work? it'll take me a while to try it out... took me for ever to have any idea of any sort at all to start with =/

 

[Galaxiom]

That isn't a good structure for a table. You would have to keep adding more fields and an ever increasingly complex calculation.

Good structure adds records not fields to extend.

The recordset loop is pretty simple code.

 

[cos]

so what do you think i should do? im a little confused... if there's a hole lot simple way of achiving the same result, what should i do?

 

[Galaxiom]

I'll post a sample with a function in a while.

 

[Galaxiom]

Here is a sample.

IMPORTANT EDIT:
Please note the earlier version of the sample I posted had an error because I neglected to include an order by clause in the SQL of the OpenRecordset command. This could cause the monthly inflation to be applied in a random order.

It has no error handling and should be further developed but it will get you started.

It has an optional argument for the end of the period otherwise it calculates for the current month.

You should include some sensibilities like erroring if asked to calculate backwards in time for example. Also consider testing for the InflationRates data to be complete by counting the number of records between the dates and comparing with what is expected. Otherwise you might eventually forget to add new values and lose revenue.

Note it will inflate the value for any part month at either end of the period even if that is only one day. You might want to refine that. You could add arguments to control this behaviour.

It relies on all the dates in the inflation rate table to be the first of the month.