+ Reply to Thread
Results 1 to 8 of 8

Financial Analysis

  1. 07-04-2009, 05:37 AM #1
    msrlg
    msrlg is offline
    Registered User
    Join Date
    07-04-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    3

    Financial Analysis

    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
    Attached Files Attached Files
    Last edited by msrlg; 07-08-2009 at 02:03 AM.
    Reply With QuoteRegister To Reply
  2. 07-04-2009, 06:45 AM #2
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Financial Analysis using excel

    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
    Reply With QuoteRegister To Reply
  3. 07-04-2009, 06:48 AM #3
    msrlg
    msrlg is offline
    Registered User
    Join Date
    07-04-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Financial Analysis using excel

    Quote Originally Posted by tigertiger View Post
    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))
    Reply With QuoteRegister To Reply
  4. 07-04-2009, 12:19 PM #4
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Financial Analysis using excel

    Quote Originally Posted by msrlg View Post
    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))
    Last edited by tigertiger; 07-04-2009 at 12:30 PM.
    Reply With QuoteRegister To Reply
  5. 07-04-2009, 02:09 PM #5
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Financial Analysis using excel

    Quote Originally Posted by msrlg View Post
    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
    Reply With QuoteRegister To Reply
  6. 07-04-2009, 10:45 PM #6
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    re: Financial Analysis

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-06-2009, 10:42 PM #7
    msrlg
    msrlg is offline
    Registered User
    Join Date
    07-04-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Financial Analysis

    Thank you! Thank you!
    You saved a part of my dissertation!
    I hope your karma bank account is credited for this benevolent act!
    Reply With QuoteRegister To Reply
  8. 07-07-2009, 01:22 AM #8
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Financial Analysis

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

    thanks your appreciate !
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1