Results 1 to 10 of 10

Thread: ROI value for an investment portfolio.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Location
    London
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ROI value for an investment portfolio.

    Could I ask for some help from you experts with a formula please. I am having an issue with trying to get the correct result for an investment portfolio that I am building. My isue is obtaining the correct RIO value in cell O27 for the month of July. Here are the cells that are being calculated A81+F81+J81-$L$2/$L$2*100.
    I want to display the RIO value as the data is entered on a daily basis culminating to the last workday of the month. Some of the percentage results I am getting do not appear correct to me. Once I can display the correct result I could copy this down for Aug, Sept etc.


    I have tried to att: a sample sheet but just noticed that with my profile for some reason I am unable to attach. Maybe an adminstrator could explain to me.


    Many Thanks

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    It is difficult to understand what your error could be since we have no data. Are A, F and J percentages? If so can you add them (all same base)? If not percentages, you miss brackets to get a percentage. You realise you add a+f+j-100 (whaterver the value of L2, it does not change anything)?
    Somehow I would expect a (ProfitA+ProfitB+ProfitC)/(CostA+CostB+CostC) formula to get a return on investment.

  3. #3
    New Member
    Join Date
    Jan 2017
    Location
    London
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    Hi Kamilga, thanks for replying to me. I have checked it looks like a permissions issue that I cannot send att: unfortunatly no one from the admin side has offered any support. I feel somewhat disappointed.

    I have sent the headers through so you might get a feel for what I am trying to acheive. columns "A through L" are indicated below.
    No1 Cash Growth Element Holding Total Available Funds P & L Date No2 Cash Growth Element Holding Total Available Funds P & L Joint Holding Value Joint P & L Initial Investment

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    Nobody can add an attachment to their post.
    There are tools to copy-paste excel, I installed an add-in for that but the simplest way is to use the snipping tool (installed with windows) to select the part of the screenshot you want, upload the picture to https://imgbb.com (free and no account needed), then click insert image and use the url given by the website.

    Another possibility is to create a shared file in dropbox or onedrive and paste the link.
    Since your data is value, it could be
    Code:
    =(A81+F81+J81)/$L$2
    or
    Code:
    =(A81+F81+J81-$L$2)/$L$2
    depending on what the numbers include.
    Last edited by Kamolga; Jul 6th, 2019 at 01:49 PM.

  5. #5
    New Member
    Join Date
    Jan 2017
    Location
    London
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    Hello Kamolga, I have setteled on this formula SUM(J3+F3+A3-L2)/$L$2 which is sited in cell "O27"as looks like the type of result that I am looking for. Thanks for the pointers.

    Following on from this, the above formula only operates on one row only. I would like to see the ROI results on a dayly basis then stopping at the end of the month, so I end up with a last day of the month in Cell O27 for July. How could I write a formula that allows this event to occurr? My column headings are listed on an earlier reply for reference. As colunm "E" holds all the weekday dates I couldn't say as I wouldn't know and also the formula would be enormus in length and which cell to reference the above formula to. Or am I overthinking this?
    Do you think you would be able to assist please.
    Last edited by delboy1616; Jul 7th, 2019 at 10:43 AM.

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    Hi, this would sum what is in column A if the date in column E is bigger or equal to 1st July 2019 and and smaller than 1st August 2019

    Code:
    =SUMIFS(A:A,E:E,">="&DATE(2019,7,1),E:E,"<"&DATE(2019,8,1))
    You can therefore use something like
    Code:
    =SUM(SUMIFS(A:A,E:E,">="&DATE(2019,7,1),E:E,"<"&DATE(2019,8,1)),SUMIFS(F:F,E:E,">="&DATE(2019,7,1),E:E,"<"&DATE(2019,8,1)),SUMIFS(J:J,E:E,">="&DATE(2019,7,1),E:E,"<"&DATE(2019,8,1)))/$L$2
    to sum column A, F and J that you divide by L2.

    Instead of typing 7 and 8 every time, then 8 and 9 for another month, I usually work with row numbers or you can use a hidden column with the month that you want. So let say you hide column P and in P27 you put 7 because you want July, then your formula looks like

    Code:
    =SUM(SUMIFS(A:A,E:E,">="&DATE(2019,P27,1),E:E,"<"&DATE(2019,P27+1,1)),SUMIFS(F:F,E:E,">="&DATE(2019,P27,1),E:E,"<"&DATE(2019,P27+1,1)),SUMIFS(J:J,E:E,">="&DATE(2019,P27,1),E:E,"<"&DATE(2019,P27+1,1)))/$L$2
    So if you want august in line 56, you type 8 in P56 and the formula in O56 will calculate for August
    Last edited by Kamolga; Jul 7th, 2019 at 11:49 AM.

  7. #7
    New Member
    Join Date
    Jan 2017
    Location
    London
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    Hello Kamolga, I have elected to use the 3rd formula version that you very kindly offered, as once set up the management would be minimal. As I have a column with the Months name already listed.Cell N6 caters for the month name so your formulas now reads
    SUM(SUMIFS(A:A,E:E,">="&DATE(2019,N6,1),E:E,"<"&DATE(2019,N6+1,1)),SUMIFS(F:F,E:E,">="&DATE(2019,N6,1),E:E,"<"&DATE(2019,N6+1,1)),SUMIFS(J:J,E:E,">="&DATE(2019,N6,1),E:E,"<"&DATE(2019,N6+1,1)))/$L$2. When I run the formula it is returning a 0% and am unsure why this is occurring, do you have any ideas? Thank you.

    A
    100 E 01-Mar F 100 J 200 L N O
    200 02-Mar 200 400 1000
    300 03-Mar 300 600
    400 04-Mar 400 800
    1500 05-Mar 1500 3000
    2000 01-Apr 3000 5000 March 0%
    Last edited by delboy1616; Jul 8th, 2019 at 07:12 AM.

  8. #8
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    One thing could be that March is text. You need to enter it as 01/03/2019 and use format, more number format, select the last one 'custom' and Type "mmmm" (without " "). So you will read March but excel will know it is a date.

    Then you need to enter
    Code:
    DATE(2019,month(N6),1),E:E,"<"&DATE(2019,Month(N6)+1,1)
    If you still got 0%, then it might have something to do wit this -L2 in
    SUM(J3+F3+A3-L2)/$L$2 that is not in the new formula...

  9. #9
    New Member
    Join Date
    Jan 2017
    Location
    London
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    Hi Kamolga, “March” was text, now amended and adapted the formula to suit, all appears good and now working.

    Thank you so much for your help and assistance, much apprecciated.

    Del...

  10. #10
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to obtain the ROI value for an investment portfolio.

    You are welcome, thanks for the feedback

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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