Calculating Values based on dates and indexes!

**Nienaber** · 02-17-2010, 02:35 PM

Hello there!

I´m having some trouble on calculating some restatements..

I´ve several indexes (in %) for each month/year and I need to calculate a final value based on that.. the result would be the Initial value plus the value based on the sum of the indexes for the period described. For example:

Initial Value: U$ 1,000.00
Beg date: May/2009
End Date Oct/2009

Then I have a column filled with the indexes for each year/month, follows:
May/2009 - 0,07%
Jun/2009 - 0,10%
Jul/2009 - 0,43%
Aug/2009 - 0,36%
Sep/2009 - 0,42%
Oct/2009 - 0,05%
Sum of the period informed = 1,74%

The calculation would be the sum of these indexes (1,74%) multiplied by the initial value, plus itself. (U$ 1000 + 1000*1,74%) But I don´t know how to make excel understand the interval beetwen the period and sum the according indexes, once I have different values, dates and indexes for calculation informed.... do I have to use VBA on that?

I don´t know if I made myself clear, but if anyone has any idea about it i can answer your doubts! I would really appreciate!! I can attach an example if need as well!

Thank you so much!!

**DonkeyOte** · 02-17-2010, 03:42 PM

A sample file would help but for the sake of demo. let's assume the following:

Initial Value: A1 (numeric value)
Beg Date: A2 (date value - 1st of month)
End Date: A3 (date value - 1st of month)

Index Table C1:Dx
x is last row - unknown - for arguments sake let's make this 13
Column C contains Months as date values (1st of month)
Column D contains the Index % values

Result to go in A4 based on A1:A3 in related to C1:D13

A4: =A1*(1+SUM(INDEX(D1:D13;MATCH(A2;C1:C13)):INDEX(D1:D13;MATCH(A3;C1:C13))))

not quite sure where you get the 1.74% - I get 1.43%

**rwgrietveld** · 02-17-2010, 04:41 PM

DO,

I think it is interest OVER interest. In dutch we call in "samengestelde" interest. Don't know how it is called in english but this example will clarify.

For the same interest (7%) for 5 years:
DO: 1000 * 1+5*0.07
ME: 1000 * (1+0.07)^5

That would make your solution:
=A1*(PRODUCT(1+INDEX(D1:D13,MATCH(A2,C1:C13)):INDEX(D1:D13,MATCH(A3,C1:C13))))

CSE (array)

I might be wrong.

**Nienaber** · 02-17-2010, 04:58 PM

DonkeyOte, your tip has worked perfectly for me! sorry my dumbness... thank you very much!

rwgrietveld, thank you for your help too, certainly your solution will be useful as well!