How to calculate portfolio performance

I currently calculate my portfolio performance using the formula:

=(H2/H1)-100%    (H1 is beginning value, H2 is current value)

I will soon begin making withdrawals from my investment account. What 
formula would I use to account for money that is withdrawn? Using the 
current formula it would be counted as a loss.

e.g.
1/1/2008 value = $10,000.00
2/1/2008 withdraw $2,000.00
2/28/2008 value = $8,100.00

I made $100.00, not lost $1,900.00. So what formula do I use to correct 
this?

Thanks,

Joe







0
jmcdermo (2)
1/24/2008 2:48:19 AM
excel 39879 articles. 2 followers. Follow

4 Replies
559 Views

Similar Articles

[PageSpeed] 7

To compute your $100 profit, do as you would on paper: =H2-(H1-2000) That 
formula computes 100. If you had 8000 in H2, the formula computes 0, 7900 in 
H2 it computes -100. Don't know if this is what your want or not.

Tyro

"Joe" <jmcdermo@hotmail.com> wrote in message 
news:T%Slj.327$A75.97@trnddc05...
>I currently calculate my portfolio performance using the formula:
>
> =(H2/H1)-100%    (H1 is beginning value, H2 is current value)
>
> I will soon begin making withdrawals from my investment account. What 
> formula would I use to account for money that is withdrawn? Using the 
> current formula it would be counted as a loss.
>
> e.g.
> 1/1/2008 value = $10,000.00
> 2/1/2008 withdraw $2,000.00
> 2/28/2008 value = $8,100.00
>
> I made $100.00, not lost $1,900.00. So what formula do I use to correct 
> this?
>
> Thanks,
>
> Joe
>
>
>
>
>
>
> 


0
Tyro (331)
1/24/2008 3:05:34 AM
On Thu, 24 Jan 2008 02:48:19 GMT, "Joe" <jmcdermo@hotmail.com> wrote:

>I currently calculate my portfolio performance using the formula:
>
>=(H2/H1)-100%    (H1 is beginning value, H2 is current value)
>
>I will soon begin making withdrawals from my investment account. What 
>formula would I use to account for money that is withdrawn? Using the 
>current formula it would be counted as a loss.
>
>e.g.
>1/1/2008 value = $10,000.00
>2/1/2008 withdraw $2,000.00
>2/28/2008 value = $8,100.00
>
>I made $100.00, not lost $1,900.00. So what formula do I use to correct 
>this?
>
>Thanks,
>
>Joe
>
>
>
>
>
>

There are several ways of doing this depending on the information.

First of all, the formulas I will recommend will work better if you adopt a
consistent convention.  One convention is that money you put into your account
has a "negative" sign, and money you take out of your account has a positive
sign.

So your starting balance would be -10,000; your withdrawals and ending balance
would be +2000 and +8100.

Your raw gain/loss would then be the SUM of those transactions:

=SUM(-10000,2000,8100)

Your annual rate of return can be obtained using the XIRR function.


--ron
0
ronrosenfeld (3122)
1/24/2008 3:35:48 AM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:ej1gp35q7gdtnle51t65a2knjd49fo43q1@4ax.com...
> On Thu, 24 Jan 2008 02:48:19 GMT, "Joe" <jmcdermo@hotmail.com> wrote:
>
>>I currently calculate my portfolio performance using the formula:
>>
>>=(H2/H1)-100%    (H1 is beginning value, H2 is current value)
>>
>>I will soon begin making withdrawals from my investment account. What
>>formula would I use to account for money that is withdrawn? Using the
>>current formula it would be counted as a loss.
>>
>>e.g.
>>1/1/2008 value = $10,000.00
>>2/1/2008 withdraw $2,000.00
>>2/28/2008 value = $8,100.00
>>
>>I made $100.00, not lost $1,900.00. So what formula do I use to correct
>>this?
>>
>>Thanks,
>>
>>Joe
>>
>>
>>
>>
>>
>>
>
> There are several ways of doing this depending on the information.
>
> First of all, the formulas I will recommend will work better if you adopt 
> a
> consistent convention.  One convention is that money you put into your 
> account
> has a "negative" sign, and money you take out of your account has a 
> positive
> sign.
>
> So your starting balance would be -10,000; your withdrawals and ending 
> balance
> would be +2000 and +8100.
>
> Your raw gain/loss would then be the SUM of those transactions:
>
> =SUM(-10000,2000,8100)
>
> Your annual rate of return can be obtained using the XIRR function.
>
>
> --ron

Ron,

Thanks, I think this might give me what I want. If I understand what you are 
saying,  in the XIRR function the beginning balance would be entered 
as -10,000 and the ending balance would be +8100. All withdrawals would also 
be positive numbers.

I'll have to play around with some test data to see if I get the desired 
results.

Joe 


0
jmcdermo (2)
1/24/2008 4:19:11 AM
On Thu, 24 Jan 2008 04:19:11 GMT, "Joe" <jmcdermo@hotmail.com> wrote:

>"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
>news:ej1gp35q7gdtnle51t65a2knjd49fo43q1@4ax.com...
>> On Thu, 24 Jan 2008 02:48:19 GMT, "Joe" <jmcdermo@hotmail.com> wrote:
>>
>>>I currently calculate my portfolio performance using the formula:
>>>
>>>=(H2/H1)-100%    (H1 is beginning value, H2 is current value)
>>>
>>>I will soon begin making withdrawals from my investment account. What
>>>formula would I use to account for money that is withdrawn? Using the
>>>current formula it would be counted as a loss.
>>>
>>>e.g.
>>>1/1/2008 value = $10,000.00
>>>2/1/2008 withdraw $2,000.00
>>>2/28/2008 value = $8,100.00
>>>
>>>I made $100.00, not lost $1,900.00. So what formula do I use to correct
>>>this?
>>>
>>>Thanks,
>>>
>>>Joe
>>>
>>>
>>>
>>>
>>>
>>>
>>
>> There are several ways of doing this depending on the information.
>>
>> First of all, the formulas I will recommend will work better if you adopt 
>> a
>> consistent convention.  One convention is that money you put into your 
>> account
>> has a "negative" sign, and money you take out of your account has a 
>> positive
>> sign.
>>
>> So your starting balance would be -10,000; your withdrawals and ending 
>> balance
>> would be +2000 and +8100.
>>
>> Your raw gain/loss would then be the SUM of those transactions:
>>
>> =SUM(-10000,2000,8100)
>>
>> Your annual rate of return can be obtained using the XIRR function.
>>
>>
>> --ron
>
>Ron,
>
>Thanks, I think this might give me what I want. If I understand what you are 
>saying,  in the XIRR function the beginning balance would be entered 
>as -10,000 and the ending balance would be +8100. All withdrawals would also 
>be positive numbers.
>
>I'll have to play around with some test data to see if I get the desired 
>results.
>
>Joe 
>

That will work.  Hope this helps.  Remember that XIRR will give you an
annualized rate of return.
--ron
0
ronrosenfeld (3122)
1/24/2008 2:11:23 PM
Reply:

Similar Artilces:

How to calculate pension contribution on salary?
I'd appreciate help on a formula to calculate pension contributions based on annual salary where contributions are based on 4.5% of the first $41,100 of salary PLUS 6% on the balance of the salary. For example annual salary $50,000 (I wish!!!) Contributions of $1,849.50 (4.5% of $41,100) PLUS $534.00 (6% of $8,900) equal $2,383.50 NOTE While this is example is for a salary > $41,100 the formula who also need to calculate for salaries below $41,100. Thanks for any help Peter -- pgruening ------------------------------------------------------------------------ pgruening's Profil...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

Perform a Return Using Qsrules or QSbridge
Hello everyone. I am trying to perform a return by using the following code but I always seem to get an error. /////// Public Function Process(ByRef Session As Object) Dim trxno As Integer 'Get Transaction Number trxno = Val(InputBox("Enter Transaction Number", "Return")) Session.RecallTransaction(Session.Transaction.RecallType.RecallTransactionForReturn, trxno) 'Other code Return True End Function ////// Nevermind I've found the solution "SIDSolutions" wrote: > Hello everyone....

Report Account Performance versus an Index
Is there a way in MS Money that I can run a report that compares my investment portfolio to an index or indices? I am using Money Plus Premium. ...

Performance with SQL linked tables
Hi, My question is a application performance issue so maybe the wrong forum. I have a SQL Server 2005 Express database and connected Access 2003 clients. The performance seems to have degraded since moving from SQL 7. The users are seeing delays when clicking on menu optiosns that have open bound forms in Access. So I have been using the Profiler to have a look at things. I am filtering events SQL:Batchcompleted and RPC:Completed with duration > 10ms. I found some issues with the forms loading without filerting rows so i have changed it so it does not load any rows on opening. A...

calculation
I have a macro that is inserting lines on 2 sheets in another workbook. The other workbook has 10 sheets. 1 sheet contains the data and the other 9 sheets contain many formulas that are all inter-connected with the various sheets. The lines are inserted in the data sheet and in 1 of the other 9 sheets. The other 8 sheets are not changed at all The problem is when I insert a row on either of the 2 sheets, it takes forever. I have tried changing calculation to manual and then turning it back on to automatic when I'm done running the code but that doesn't seem to hel...

Cell does not calculate automaticaly
A cell contains a linked formula to other cells. The destination cell will only show the information from the source cell if the destination cell is "double clicked" and the OK icon is clicked. The Options / Calculations / "Automatic" option button is enabled. This problem does not occur with other files. The file with the problem is over 11 meg in size, where the other files are less than 6 meg. HELP! Hi do you see the text 'Calculate' in your statusbar and does hitting F9 help?. If yes it could be that you just have to many formulas to allow automatic ...

Automatic Calculations
Hi, For some reason the calculations made in my Excel documents require manual action, instead of being executed automatically. I have tried switching to automatic, but it switches back each time restart Excel. Is this a known problem or can I fix this -- Message posted from http://www.ExcelForum.com Hi The status of the calculation mode is set by the first sheet you open in each session. -- Andy. "Berend Botje >" <<Berend.Botje.17y29y@excelforum-nospam.com> wrote in message news:Berend.Botje.17y29y@excelforum-nospam.com... > Hi, > > For some reason ...

want excel 2007 to stop calculating processors
Hi There I am running excel 2007. I have a sheet with 167000 lines in it, and have to use vlookup to match fields from other files. Once i made a change, the program start with "Calculating: (Processor) which is taking up to an hour to complete. How can i remove that calculating Regards Johann Terblanche Office Button (top left hand corner) Excel Options (lower right hand corner) Formulas (left hand side) then, on the right hand side, under Calculation options, set Workbook Calculation to Manual / OK If my comments have helped please hit Yes. Thanks. ...

Performing a delay inside a worker thread
Hi, I need some help/guidance on improving an application that we have. This application controls an XY cutting table machine and does this by interfacing with a motion control board (the board manufacturer provide an C++ API ). Since it's a cutting machine, some tools must be turned on/off during its operation. This is archived by setting bits in I/O ports calling a function API. This bits must be set on /off in a predetermined order and must be a delay between this callings (some tools have an pneumatic actuator and they take some time to get in position). The API doesn’t have a “...

permissions granted to user are insufficient for performing operat
I'm trying to install CRM 4.0 on a Server 2003 box running SQL 2005 SP2 and I’m getting this SRS error. (Note: SRS is running the box already and is working.) I'm trying to install CRM 4.0 on a Server 2003 box running SQL 2005 SP2 and I’m getting this SRS error. (Note: SRS is running the box already) Setup failed to validate specified Reporting Services Report Server http://pdm/ReportServer. Error: The permissions granted to user 'HMS\Administrator' are insufficient for performing this operation. System.Web.Services.Protocols.SoapException: The permissions granted t...

Principal and Interest Calculation
I setup a loan in Money 2004 and, based on the principal and interest calculations shown in the amortization schedule, it appears that Money is using the actual number of days between due dates to determine the portions of the payment that are principal and interest, which of course varies depending on the number of days in the month. However, my bank calculates the principal/interest split based on a standard 30-day month (360 day year). Is there a way to have Money use this type of amortization schedule instead of the one it's using by default? ...

Calculating without including Hidden Cells
PLEASE HELP I have a 30 page excel worksheet that I need to get a sum for. I have hidden the cells that don't need to be included in the total, but I need to keep them in the work- sheet. How do I calculate these cells without having to delete them. I thought about copy/pasting them into another worksheet to see if they would come without the hidden cells, but thought their had to be an easier solution? Please help...Thanks in Advance Have a look in HELP index for SUBTOTAL -- Don Guillett SalesAid Software donaldb@281.com "LiquidFire" <LiquidFire@discussions.micro...

Calculating Row/Column Headers in PivotTable
Hello, I have a table of data, the first two columns of which are Date and Transaction Amount. What I'm trying to do is put together a table that will summarize total transactions by month/year. My goal is to have 1 through 12 (months) as the column headers, and have the years be the rows. Then I could use Sum of Transaction Amount as my data field. However, I can't seem to figure out how (or if I can) force a pivot table to base both the rows and columns on the same data point. I've right clicked on the column headers and selected both Calculated Item and Calculated Field. W...

Calculating the difference between two fields
Hello, I have a subform in a table which has the following controls: Type of Day....Begin Date.....End Date....Total Days There may be several rows of information in these controls. The form is used to calculate the number of vacation days a person has taken. I have written some code that uses that DateDiff function to give me the difference between Begin Date and End Date. The result is supposed to go into the TOTAL Days control. My problem is that it appears that the calculation is performed only once. So for instance, if John Doe has as his first row Begin Date 3/13/2007 and End ...

How do I calculate ages in excel using date of birth & another dat
Using excel 2003 I wish to insert a column with a date of birth and another column with another date eg 1/1/2005. I would then like to calulate the age of each person in my list as at 1 Jan 05. http://www.cpearson.com/excel/datedif.htm -- Regards, Peo Sjoblom (No private emails please) "Paniac" <Paniac@discussions.microsoft.com> wrote in message news:8BBB2D5E-E558-425C-825F-029A0E16E93C@microsoft.com... > Using excel 2003 I wish to insert a column with a date of birth and > another > column with another date eg 1/1/2005. I would then like to calulate the ...

Performance of savings accounts
One problem that I keep running into with Money is that it has a very clear distinction between investment accounts and checking/savings accounts. So far, I have not been able to find a way to get money to give me a performance report on a savings account or to consider the savings account to be part of my portfolio. When I want to check how well my investments did over a year, I cannot get data about my savings accounts and so the data that is given on my investment accounts does not account for my savings... "Money 2005 Deluxe user" <Money2005Deluxeuser@discussions.micro...

Enter Parameter Value
I have a continuous/tabular form of report with two fields DEBIT & CREDIT I have created two custom calculating controls in form of textboxes as follows: Name of control "DBALANCE" =IIf([DEBIT]>[CREDIT],[DEBIT]-[CREDIT],0) and Name of control "CBALANCE" =IIf([DEBIT]<[CREDIT],-[DEBIT]+[CREDIT],0) They work perfectly, however, now, when I insert: =SUM([DBALANCE]) & =SUM([CBALANCE]) in the report footer to calculate the grand total the report on being turned on asks for parameter value for DBALANCE & CBALANCE Any reason and way to resolve...

function performance
I had heard that functions have a performance issue because they have to be reevalueate the function each time it is called. For example, if we have a function f1 and execute: SELECT f1(name) from employees that is would be slow because it would have to reevalueate the function for each row. If you have a function inside of a function (ie, f1, f2), CREATE FUNCTION f1 (@var1 int) RETURNS INT DECLARE @var2 int SELECT @var2 = F2(@var1) RETURN @var2 and did the SELECT f1(size) from employees. This would be slow because it need to reevalueate f1 and f2 for each...

calculate form control if one of them is blank
I have a report that has two subreports. On each subreport is a calculated field. On the main report I have a textbox that adds the two calculated fields together (basically...[SubReportCalc1]+[SubReportCalc1]). This works fine unless one of the subreport fields are blank, this happens when there was nothing to calculate. How can i get it to still calculate on the main report correctly? See: Bring the total from a subreport onto a main report at: http://allenbrowne.com/casu-18.html The article shows how to use the HasData property of the subreport to avoid the error...

In-table calculation?
Hi! I have a subform going for products returned to the warehouse. Not unlike one would do in Excel, I want to do a simple calculation that does the sum of two columns in the table, with the result in 'column 3'. I'm a bit confused as to how Access handles displaying this information right there in the table: QtyOrdered + QtyShipped = QtyReceived Thanks! ~TPS Access does not do this in a table. This would be a calculated field in a query or as the source of a control on a form or report. General rule is that if it can be calculated and is dependent on other ...

Excahnge 2003 Performance counters.
I believe Mcafee Groupshield 6 was causing a problem were messages were satcking up in the Queues. I was wonder what performance counters can I user to have MOM alert me if this happens again. One of the oquese I would like to monitor is "Messages Pending Submission", I am not sure what perfmon counter to use though. ...

How do I perform linear regression?
How or where do I perform linear regression - in the chart or worksheet? Do you mean simply where do you perform it, or do you mean how to do it in general? "kunlez" wrote: > How or where do I perform linear regression - in the chart or worksheet? kunlez wrote: > How or where do I perform linear regression - in the chart or worksheet? ------------ Lots of possibilities, depending on what you want to get out of it. The simplest way is to just plot your data on an X,Y chart and then ask Excel to add the trend line to the data. Right click on the data line of the chart an...

The performance of Editplus is much better than notepad,Why?
I found editplus's performance is much better than notepad.Editplus uses only 2--2.5M memory whereas notepad uses more than 4M.Why? I'm writing a small application managing my diary which costs more than 3M memory when just opening it wihout opening any diary file,like notepad. Why editplus costs so less memory?And why notepad costs so much? As notepad is implemented with the richedit control?And editplus implemented with other? Read my essay "How big is my program?" Generally, numbers like this cannot be trusted anyway, and trivial differences like 1.5MB are "in ...

You cannot perform this operation at the moment.There is an online operation in progress.
Do you know why is the following message appearings "You cannot perform this operation at the moment.There is an online operation in progress.Please try again later". I have nothing running in the background. I am trying to import a file by downloading from the web and then clicking "Open" button. Some activity is in process, either the one you initiated or another one Money piggy-backed on your request. Money does some auto-processing on imported files, trying to match the file transactions to existing Money transactions. It doesn't let you make any transac...