Financial Analysis

**msrlg** · 07-04-2009, 06:37 AM

Hello fellow forum members,

I am comparing the returns of several fixed-income funds and since their inception (founding) dates differ, I have columns where some have over 300 values while others have a mere 30.

I need to do the following equation for every column (from A2:A300:
= (1+(A2/100))*(1+(A3/100))...etc...(1+(A299/100))*(1+(A300/100))

I tried to do the first 50 manually, but the forumula ended up being too long.
Would the fixed-income funds that were only recently established (i.e. only have values for A260:A300) screw up any equation because of blank fields?

Please see the excel file for the problems I am having (marked in orange)

Any help is greatly appreciated - just spent 2 hrs googling and it seems most people have problems with basics and not 'complex' equations such as the ones above.

Have a good weekend & thanks in advance!
msrlg

**tigertiger** · 07-04-2009, 07:45 AM

the first question, I think, you should use UDF in VBA

the second question, I do not understand clearly, you may use IF() function for BLANK condition

**msrlg** · 07-04-2009, 07:48 AM

Originally Posted by tigertiger

the first question, I think, you should use UDF in VBA

the second question, I do not understand clearly, you may use IF() function for BLANK condition

1 - Could you please elaborate on the UDF in VBA approach?

2 - The 'constant' refers to a value that remains constant e.g. 5, 7 or 11

With regards to the 'blank fields' - I mean that some columns begin at A230 or A250 while others begin at A2 - so ideally the formula would take that into account when e.g. averaging. (the AVG (A2:A300))

**tigertiger** · 07-04-2009, 01:19 PM

Originally Posted by msrlg

I need to do the following equation for every column (from A2:A300:
= (1+(A2/100))*(1+(A3/100))...etc...(1+(A299/100))*(1+(A300/100))

try the following formula:

=PRODUCT(1+A2:A300/100)

press CTRL+SHIFT+ENTER after type this formula
(excel will add { } - this is an array-formular)

or an alternative (normal formula-- only press Enter)
=SUMPRODUCT(PRODUCT(1+A1:A300/100))

**tigertiger** · 07-04-2009, 03:09 PM

Originally Posted by msrlg

2 - The 'constant' refers to a value that remains constant e.g. 5, 7 or 11

With regards to the 'blank fields' - I mean that some columns begin at A230 or A250 while others begin at A2 - so ideally the formula would take that into account when e.g. averaging. (the AVG (A2:A300))

YOU should post a demo file that others can help you

**tigertiger** · 07-04-2009, 11:45 PM

Plz, see the attached file that I solved it and left the detail explanation and also shortcomings (in Geometric Mean Return - try to see again your formula)

and post your ideas so that others can help to solve the shortcomming