ROI value for an investment portfolio.

delboy1616

New Member
Joined
Jan 2, 2017
Messages
12
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
 

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,168
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.
 

delboy1616

New Member
Joined
Jan 2, 2017
Messages
12
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 ElementHoldingTotal Available FundsP & LDateNo2 Cash Growth ElementHoldingTotal Available FundsP & LJoint Holding ValueJoint P & LInitial Investment

<tbody>
</tbody>
 

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,168
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)/[COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR]L[COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR]2
or
Code:
=[COLOR=#222222][FONT=Verdana](A81+F81+J81-[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]L[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]2)/[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]L[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]2[/FONT][/COLOR]
depending on what the numbers include.
 
Last edited:

delboy1616

New Member
Joined
Jan 2, 2017
Messages
12
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:

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,168
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)))[FONT=Verdana]/$L$2[/FONT]
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:
[COLOR=#222222][FONT=Verdana]=SUM(SUMIFS(A:A,E:E,">="&DATE(2019,[COLOR=#222222][FONT=Verdana]P27[/FONT][/COLOR],1),E:E,"<"&DATE(2019,[COLOR=#222222][FONT=Verdana]P27+1[/FONT][/COLOR],1)),[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]SUMIFS(F:F,E:E,">="&DATE(2019,[COLOR=#222222][FONT=Verdana]P27[/FONT][/COLOR],1),E:E,"<"&DATE(2019,[COLOR=#222222][FONT=Verdana]P27+1[/FONT][/COLOR],1)),[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]SUMIFS(J:J,E:E,">="&DATE(2019,[COLOR=#222222][FONT=Verdana]P27[/FONT][/COLOR],1),E:E,"<"&DATE(2019,[COLOR=#222222][FONT=Verdana]P27+1[/FONT][/COLOR],1)))/$L$2[/FONT][/COLOR]
So if you want august in line 56, you type 8 in P56 and the formula in O56 will calculate for August
 
Last edited:

delboy1616

New Member
Joined
Jan 2, 2017
Messages
12
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
100E 01-MarF 100J200LNO
20002-Mar2004001000
30003-Mar300600
40004-Mar400800
150005-Mar15003000
200001-Apr30005000March0%

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,168
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:
[LEFT][COLOR=#333333][FONT=Verdana]DATE(2019,month(N6),1),E:E,"<"&DATE(2019,Month(N6)+1,1)[/FONT][/COLOR][/LEFT]
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...
 

delboy1616

New Member
Joined
Jan 2, 2017
Messages
12
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...
 

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,168
Re: Trying to obtain the ROI value for an investment portfolio.

You are welcome, thanks for the feedback
 

Forum statistics

Threads
1,085,193
Messages
5,382,242
Members
401,781
Latest member
Dlloyd15

Some videos you may like

This Week's Hot Topics

Top