How to calculate portfolio performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 24 Jan 2008 02:48 GMT

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

Reply to this Message

Tyro - 24 Jan 2008 03:05 GMT

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:
>
[quoted text clipped - 15 lines]
>
> Joe

Reply to this Message

Ron Rosenfeld - 24 Jan 2008 03:35 GMT

>I currently calculate my portfolio performance using the formula:
>
[quoted text clipped - 15 lines]
>
>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

Reply to this Message

Joe - 24 Jan 2008 04:19 GMT

>>I currently calculate my portfolio performance using the formula:
>>
[quoted text clipped - 37 lines]
>
> --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

Reply to this Message

Ron Rosenfeld - 24 Jan 2008 14:11 GMT

>>>I currently calculate my portfolio performance using the formula:
>>>
[quoted text clipped - 49 lines]
>
>Joe

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

Reply to this Message