ROI value for an investment portfolio.

[delboy1616]

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]

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]

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. [TABLE="width: 1136"]
<tbody>[TR]
[TD="class: xl73, width: 111"]No1 Cash Growth Element[/TD]
[TD="class: xl74, width: 87"]Holding[/TD]
[TD="class: xl66, width: 87"]Total Available Funds[/TD]
[TD="class: xl69, width: 78"]P & L[/TD]
[TD="class: xl67, width: 112"]Date[/TD]
[TD="class: xl73, width: 113"]No2 Cash Growth Element[/TD]
[TD="class: xl75, width: 79"]Holding[/TD]
[TD="class: xl66, width: 92"]Total Available Funds[/TD]
[TD="class: xl71, width: 98"]P & L[/TD]
[TD="class: xl70, width: 79"]Joint Holding Value[/TD]
[TD="class: xl76, width: 76"]Joint P & L[/TD]
[TD="class: xl77, width: 124"]Initial Investment[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

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

=(A81+F81+J81)/[COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR]L[COLOR=#333333][FONT=Verdana]$[/FONT][/COLOR]2

or

=[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.

 

[delboy1616]

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.

 

[Kamolga]

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

=SUMIFS(A:A,E:E,">="&DATE(2019,7,1),E:E,"<"&DATE(2019,8,1))

You can therefore use something like

=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

[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
​

 

[delboy1616]

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[TABLE="width: 1084"]
<tbody>[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD="align: right"] 01-Mar[/TD]
[TD="align: right"]F 100[/TD]
[TD][/TD]
[TD][/TD]
[TD]J[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD]L[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02-Mar[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03-Mar[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04-Mar[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05-Mar[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]March[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1084"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

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

[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]

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]

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

You are welcome, thanks for the feedback