How to calculate portfolio performance

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

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
 
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
 
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
Click to expand...

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 Rosenfeld said:
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
Click to expand...

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
 
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
Click to expand...

That will work. Hope this helps. Remember that XIRR will give you an
annualized rate of return.
--ron
 
Back
Top