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.
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. <a href="http://www.piateksoftware.com">http://www.piateksoftware.com</a> Best Regards, David Piatek
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.
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.
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
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.
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.
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.
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.