Excel to calculate return

Use solver and solve for R:

Deposits of 10,000 - one year ago and 10,000 - 6 months ago. Withdrawal of 1,000 - 3 months ago.

Ending pf value = 20,000.

Solve for E2 (annualised rate of return) eqn given in F4. Be careful with your exponents when spanning multiple years.
In this case E6 = A6 when R = 6.81% p.a.

This is how HP-12 and 17 solve (via iteration) for TVM (depends in what is being solved - closed form soln for some), CFLOW and IRR menus.

Individual trade returns depend on how much capital is allocated per trade and how much gain/loss is realised to give you the holding period return for each trade - very different.

You're welcome.
:D
 

Attachments

Quote from sandaq:

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.
More...

not to be rude, but I doubt if you're going to find anyone to write this function for you for free. If you want it for free, you have to write it yourself, which involves cost of time, so it still isn't free!
 
I don't just need cash flow return, there's also the aspect of how much return I've gained on my trades.
More...

Sandaq,
This is what XIRR answers to : Said otherwise :
  • I've deposited cash several times, at different dates;
  • I have also withdrawn (or not) cash, at different dates.
  • Between the first day and now, I have also made some trading operations which produced results (positive or negative ones : they actually ARE part of my final value)
  • and I've now got a final value for my portfolio, and want to know to which annualized rate of return this final value corresponds, taking into account all these moves, and the time they happened"

And _again_ XIRR is the solution.

Taking the same number and dates as the ones Equalizer used, then :
OpenOffice's XIRR gives 7.24% (annualized) (computing details in the first picture linked) ;
Additionnaly, the TWR (Modified Dietz methode) answers : 7.20% (annualized) (Weighted contirbutions and withdrawals in the seconfd picture).

Google a bit for both XIRR and Modified Dietz to find more about them. They definitely are the solution to your need.

Olivier.
 

Attachments

Quote from Pro_Trader720:

not to be rude, but I doubt if you're going to find anyone to write this function for you for free. If you want it for free, you have to write it yourself, which involves cost of time, so it still isn't free!
More...

Yes, I agree, but I prefer to spend money on my self, then to give it to someone else :D
 
Quote from oTzt:


Taking the same number and dates as the ones Equalizer used, then :
OpenOffice's XIRR gives 7.24%
More...

Not quite,

Your dates are different. Your first time difference is 7 months, then 2 months then 3. In my example I used 6, 3, 3.

You second date needs to be 15/11/2006 instead of 15/12/20006. Then XIRR shall yield 6.83% annualised. It probably uses solver internally.

Different dates will produce a small difference depending on the daycount. Using XIRR he will not need to worry about time percentages, just plonk the actual dates to Excel. Sounds good to me.
 
Equalizer, sorry for the mistake :
I may have read your post a bit too quick. For the rest you're right : Excel uses an iterative algorithm to solve for XIRR (maybe not the solver itself, but the idea is the same. My own code required 74 iterations to compute it).

Sandaq: Your number look really weird :
Your first line seems to be the original cash value of your portfolio (entered as a negative number : correct).
Then all the following ones should be only cash additions and withdrawals, not the outcome of your trades (following the same rule than for the first one : additions are entered as negative numbers, withdrawals as positive numbers),
Then the last line shoud be the actual value of your portfolio (as a positive number : this is what you could withdraw tody to close the portfolio).

Looking at your numbers it seems the final value is 1500 on the second line. If true, it should then be positive (it's a "potential" withdrawal, not an addition). In order to follow a certain logic, it should also be written on the last line.

So what the hell are all those little numbers (53.907, 98.9064, etc.) ? Did you add or withdraw these amounts as cash, several times a day (the 13th of may for example) ?
I suspect they are trade results. If true, you must NOT write them : They are soon in your final value, as stated in my previous post.

Try to change your spreadshhet numbers this way (final value is positive, remove trade results, just keep additions and withdrawals made in cash to your account), and things will go ok.

Olivier.
 
XIRR will do what you want if I read the thread correctly.

However, most people really want the time weighted rate of return for the overall portfolio, i.e. one that is insensitive to cash flows... A simple example is to think of a mutual fund. If it's 100 at year 0, 50 at year 0.5, 150 at year 2.... you have a 50% trailing 1 year return.

But if you deposited most of your money at year 0.5, you have a much larger trailing 1 year XIRR.

So depends on what you want to do. To my knowledge, nothing in Excell natively supports calculating the time weighted return. This is the # that all portfolio managers, financial advisors, etc. report.
 
Quote from oTzt:

Sandaq: Your number look really weird :
Your first line seems to be the original cash value of your portfolio (entered as a negative number : correct).
Then all the following ones should be only cash additions and withdrawals, not the outcome of your trades (following the same rule than for the first one : additions are entered as negative numbers, withdrawals as positive numbers),
Then the last line shoud be the actual value of your portfolio (as a positive number : this is what you could withdraw tody to close the portfolio).
Olivier.
More...

It still doesn't work. I followed your instructions.
first number (negative) is initial deposit
second number (negative) second deposit
third umber is my current value

gives me 800% return

how are the dates being calculated? (If the first date is 1/1/07 the return is 26%, which is still too high)
 

Attachments

You must log in or register to reply here.
Back
Top