Calculate interest on interest in script

[#1]

Hi,

Is there an easy way to calculate interest on interest in the script, per date and aggregated over a set time period.

The interest or return will differ for each date and is shown as a per cent where below 1 is negative interest and above 1 is positive interest.

Example

0,990,99

Date	Interest
2015-01-01
2015-01-02	0,95
2015-01-03	1,1
2015-01-04	1,04
2015-01-05	1,01
2015-01-06	1
2015-01-07	0,98
2015-01-08	0,96
2015-01-09

For each row the accumulated interest on interest should then be calculated.

In a chart I am using I use

(exp(sum(log(Performance ))))

Now, I want to precalculate this so every date get its accumulated return on return (interest on interest) for a set time period. In reality my time period will be one year, but in this example we can use today -2 days.

So in the above example 2015-01-03 should get 1,03455 ( 0,99 * 0,95 * 1,1)

while 2015-01-04 should get  1,0868   (0,95 * 1,1 * 1,04).

Any ideas?

[petter]

Sure - you will have to create a calculated field in your LOAD statement. This should use the Peek()-function to refer to the last loaded value. You add that to the current interest for the date and row being read. You will have to make sure you make the LOAD in sorted order by ascending date...

[petter]

like this:

LOAD

     InterestDate,

     Interest,

     If( RecNo() = 1 , 1 , Peek('AccInt') ) * Interest AS AccInt

RESIDENT

     DateAndInterestTable

ORDER BY

     Date;

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabTestData:
LOAD Date(Today()-1000+IterNo()) as Date,
    Num(Round((Rand()*0.2)+0.9,0.01),'0.00') as Interest
AutoGenerate 1
While IterNo()<=1000;

tabData:
LOAD *,
    IoI1/IoI2 as InterestOnInterest;
LOAD Date,
    Interest,
    Interest*Alt(Peek(IoI1),1) as IoI1, 
    Alt(Peek(Interest,-365),1)*Alt(Peek(IoI2),1) as IoI2
Resident tabTestData;

DROP Table tabTestData;

(just drop the auxiliary fields IoI1 and IoI2 if not needed)

hope this helps

regards

Marco

[#5]

Thanks Marco. That seems to work fine. In my solution I have a great number of stocks to calculate this and different scenarios, but I guess that can be handled by grouping and sorting.

I also have problems with the -365 as I done have interests on all dates, any hints there or do I have to fill out the empty dates with interest of 1?

As of Petters solution, I think that will work to calculate interest over time, but it does not take the 365 day cap into account.

Thanks for the suggestion though.

Kind regards

Peter