Compund interest

[dandaanilreddy]

Hello Qlik Developers,

I have refund amount for each quarter end date. I want to calculate the compound interest for each quarter and do the sum of the total quarters. For the selected quarter we have to take the no of days in the quarter minus 15 days +1 and should be multiplied by the DOL factor for the quarter in the year based on the interest rate.

For example we have refund amount 350 $ for 3/31/2015 we have to multiply 350* (no of days in quarter -15+1) dol factor from the attached spread sheet. Example: 350*0.006265868 = 2.193051(interest for that quarter for 350 $). Now we have to calculate the interest for till todays date based on the DOL factors. The below logic should be applied for each selected quarter.

Account Number	year		quarter	Refund Amount			no of days in quarter	Formula	Interest
123456		2015	3/31/2011		350		90-15+1 = 76	350*0.006265868	$2.19
123456		2015	6/30/2015		350+2.19		91	352.19*0.007507183	$2.64
123456		2015	9/30/2015		352.19+2.64		92	354.83*0.007589992	$2.69
123456		2015	12/31/2015		354.83+2.69		92	357.52*0.007589992	$2.71
123456		2016	3/13/2016		357.52+2.71		72	360.23*0.005836399	$2.10
Total Interest
Total Refund amount + interest			$12.33
350+12.33

For each selected quarter end date i want to calculate the above mechanism.Can someone help me with the requirement. Many thanks in advance

[swuehl]

I basically started with the information provided in your first post this morning, I am not sure how I should interpret your added QVW.

You should first try to load in your factor details so you can somehow map it to your fact data.

I tried this in attached QVW having two issues:

- your periods seem to be not formatted consistently, I needed to change the format of the last two lines to get them interpreted correctly

- I am not quite sure how you get to 72 for number of days in quarter in the above sample's last line.

I think I managed to create a table for each quarter with the appropriate factors, this is the INPUT table in my sample.

I then created a sample Account / amount table based on your above sample and created a link table to the factors table.

The aggregation is then performed in the front end (could potentially done also in the script).

Hope this helps,

Stefan