Excel to calculate return

S

sandaq

Hello all,

This is my first post after reading here for a couple of months, I'd like to tell you I enjoy this forum very much. Sorry if my english is messed up, specially with financial lingo, it's not my mothers language.

On to my question.

I'd like to have an excel function or vba code that will calculate the % of my gains or losses over a years long, giving several deposits and withdraws (with there date) and the current value of my account.

For example, if I deposited 10K at the beginning of the year, then deposit 10K more at half year, then withdraw 5K at 3/4 or the year, I think I'll need this functions to calculate what is the % of gain or loss:

10R^1+10R^0.5-5R^0.25=M

M=current portfolio+cash value

So I need a function to calculate this for as many deposit/withdraw I'd like, but I'd settle for a way to calculate the example above.
 
Quote from dpiatek:

If you are open to using an application instead of excel to track these values, PortfolioTK handles this, as well as, a number of other calculations and graphing.

Best Regards,
David Piatek
More...

a. It costs money, thats a no no.
b. doesn't work in the market I work in.
c. I enjoy the sheet I build to my self, just need this one function.
 
Quote from sandaq:

Hello all,

This is my first post after reading here for a couple of months, I'd like to tell you I enjoy this forum very much. Sorry if my english is messed up, specially with financial lingo, it's not my mothers language.

On to my question.

I'd like to have an excel function or vba code that will calculate the % of my gains or losses over a years long, giving several deposits and withdraws (with there date) and the current value of my account.

For example, if I deposited 10K at the beginning of the year, then deposit 10K more at half year, then withdraw 5K at 3/4 or the year, I think I'll need this functions to calculate what is the % of gain or loss:

10R^1+10R^0.5-5R^0.25=M

M=current portfolio+cash value

So I need a function to calculate this for as many deposit/withdraw I'd like, but I'd settle for a way to calculate the example above.
More...

Here's a suggestion...
XL comes with many financial specific functions. Open the XL help index and search for "Financial Functions". This may help getting you started.

Since you say you need a function to calculate this for as many deposit/withdraw I'd like, the exact layout of your data would be needed, to minimize redo.
 
Quote from osorico:

Here's a suggestion...
XL comes with many financial specific functions. Open the XL help index and search for "Financial Functions". This may help getting you started.

Since you say you need a function to calculate this for as many deposit/withdraw I'd like, the exact layout of your data would be needed, to minimize redo.
More...

Sorry, but excel does not have a function to calculate this.

What layout do you need?
I have a cell with the current account value
a column for deposit, a column for deposit date
A column for withdraw, a column for withdraw date
 
Quote from sandaq:

Sorry, but excel does not have a function to calculate this.

What layout do you need?
I have a cell with the current account value
a column for deposit, a column for deposit date
A column for withdraw, a column for withdraw date
More...

Maybe multiple functions?? hello.

I guess Im confused. Is there interest or something involved, or is this a simple check register. You want the PnL of what? Addition and subtraction of funds is simple arithmetic.
 
Quote from tneub:

hello...what you need to do is easy...i figured it out...

the function is called XIRR and its internal rate of return...its a function in excell....it relates a statement of cash flows against time....i use it in an awesome spreadsheet that i built to manage my accounts...i also use quotecat...so i can pull in stock quotes into the spreadsheet....a VP of private equity at JPM taught me how to utilize this function...and its great...most brokerages just give you a lame % gain or loss...but you do not get an internal rate of return based on a 365 day year...this is the true metric in the business as he explained....for each position that i hold...i create a statement of all purchases...and sales......dividends...foreign tax withheld...(anything that effects cash) and relate it to the time frame that these debits and credits occured...don't forget to include the current value of the position that you are carrying....you can email me at tdneubr@hotmail.com...and i can help you further if you need...i can send you an example...
More...


I'd like to see how XIRR calculate return based on different deposits. Would be great if you could upload an example.

From what I understand about this function it doesn't calculate return and it require a cash flow with at list one negative value. I don't have withdraws yet, so it won't work.
 
Quote from osorico:

Maybe multiple functions?? hello.

I guess Im confused. Is there interest or something involved, or is this a simple check register. You want the PnL of what? Addition and subtraction of funds is simple arithmetic.
More...

I'd like to calculate the return.

For example, in cell A1 (the account value) I have 1100.

In cell B1 (The deposits) I have 1000 and cell C1 has date 1/1/07

ok, now I can calculate

1000R^(365/365)=1100

Or R=(1100/1000-1)*100=10% which is my return and indeed I can calulate that easily.

However, if cell B2 has 1000 and cell C2 has date 1/6/07

my return will be

1000R^1+1000R^(185.5/365)=2100

Which is harder to calculate.

Now consider I have 5 deposits (not at the same size) and 2 withdraws. It's beggining to be impossible to calculate.
 
Sandaq,

As long as you wanna use Excel, Tneub gave you the answer : it's the XIRR function.
XIRR computes a so called "money weighted return" as opposed to a "time weighted return".

Have a look at XIRR's help in Excell, do the example given, and you'll find your solution.

You''l find the algorithm used by Excel either in the help file, or on the web at :http://office.microsoft.com/en-us/excel/HP052093411033.aspx
Olivier.
 
Quote from oTzt:

Sandaq,

As long as you wanna use Excel, Tneub gave you the answer : it's the XIRR function.
XIRR computes a so called "money weighted return" as opposed to a "time weighted return".

Olivier.
More...

I'm sorry, but It's not what I need and can't calculate this type of return.

I don't just need cash flow return, there's also the aspect of how much return I've gained on my trades.

I'll explain again:

I deposit 100
traded and gained 10%
now I have 110 (and I can gladly brag that I made 10% return)

now I deposit another 100
I gained another 10%, because I'm such a great trader, so I have 231

now I withdraw 191
I'm left with 40

But it's too complicated to calculate my return and go tell my friend about it.

If XIRR can do this, please let me know how, because I couldn't figure it out after raeding the help file several times.
 
You must log in or register to reply here.
Back
Top