+ Reply to Thread
Results 1 to 5 of 5

Teasing Monthly Returns from intra month returns

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2013
    Posts
    31

    Teasing Monthly Returns from intra month returns

    Hi All,

    This has been frustrating me most of the afternoon. I have an array of returns from different points in a given month. The one constant is I have a return from some point in the month to the end of each month.

    For example... I have the returns from 3/1/2012 through 3/4/2012 and 3/5/2012 through 3/31/2012. I want to geometrically link (1+r) the two returns to give me a monthly return for March.

    I am trying to find a way that will take the product of all the returns in a given month no matter how many discreet returns I have within that month.

    I tried a conditional array formula using if(and) without any luck.

    I greatly appreciate any help.

    Thanks,
    Brad

    I have attached a simple spreadsheet.Monthly Returns Example.xlsx

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,070

    Re: Teasing Monthly Returns from intra month returns

    Use this array formula

    =PRODUCT(IF(MONTH($A$2:$A$8)=MONTH(E2),1+$B$2:$B$8))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    10-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2013
    Posts
    31

    Re: Teasing Monthly Returns from intra month returns

    Nice Ace,

    I appreciated that I don't need the hpr+1 column using your formula. I can see where I will run into problems when the year shifts. Do you see what the problem is with this fix...I think I read that array formulas can have only 1 condition...

    =PRODUCT(IF(AND(MONTH($A$2:$A$10)=MONTH(E6),YEAR($A$2:$A$10)=YEAR(E6)),1+$B$2:$B$10)) ?

    A9 = 3/5/2013
    A10 = 3/31/2013

    Thank for your help,
    Brad

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,070

    Re: Teasing Monthly Returns from intra month returns

    Aah..I should have known you'd need multiple years

    =PRODUCT(IF((MONTH($A$2:$A$10)=MONTH(E6))*(YEAR($A$2:$A$10)=YEAR(E6)),1+$B$2:$B$10))

    Ctrl+Shift+Enter

  5. #5
    Registered User
    Join Date
    10-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2013
    Posts
    31

    Re: Teasing Monthly Returns from intra month returns

    Works great. I'm going to have to wrap my head around the logic of the formula, but I get the gist. month*year must be equal to month*year. Very nicely done.

    Thanks again,
    Brad

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating Monthly Returns
    By duration in forum Excel General
    Replies: 17
    Last Post: 09-18-2012, 07:33 AM
  2. We Pay 50%--100% Returns Monthly For 1 yearr on investment. You need to know this!
    By INCOME---DAILY in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-06-2006, 11:00 PM
  3. [SOLVED] INVESTMENTS OPPORTUNITES: Get 10.5% or 50% Monthly returns
    By Israel Fagbemi -- Top Rank Business Associates Gro in forum Excel General
    Replies: 0
    Last Post: 11-06-2005, 05:55 AM
  4. INVESTMENTS OPPORTUNITES: Get 10.5% or 50% Monthly returns
    By Israel Fagbemi -- Top Rank Business Associates Gro in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 11-06-2005, 05:55 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1