Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR for short sales using XIRR
I am trying to calculate the annual growth rate for a short sale. I've
entered the first amount as a positive number (because it was cash coming in) and the second amount as a negative number (because it was cash going out). When I use XIRR I get a negative percentage as the annualized return. As a simple example, if I enter 110 on the first date and -100 a year later XIRR gives me negative 9.1% as the annualized return. Since I have 10 more at the end of the year, it seems that my return must be positive. Am I misinterpreting this result or what? Thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR for short sales using XIRR
But you told XIRR that you invested $110 and got back $100. So you lost
money. That's what XIRR is telling you. Remember that to calculate investment returns, only the cash flow is important. The transactions in between have no effect on the return. Normally when you do a short sale, you need to put up 50% margin. Is that what you did in this case? If so, you invested $55 (cash out of your pocket), and received back $100. That's the transaction detail you need to feed to XIRR. Regards, Fred. "oukid" wrote in message ... I am trying to calculate the annual growth rate for a short sale. I've entered the first amount as a positive number (because it was cash coming in) and the second amount as a negative number (because it was cash going out). When I use XIRR I get a negative percentage as the annualized return. As a simple example, if I enter 110 on the first date and -100 a year later XIRR gives me negative 9.1% as the annualized return. Since I have 10 more at the end of the year, it seems that my return must be positive. Am I misinterpreting this result or what? Thanks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR for short sales using XIRR
I thought that my first entry, being positive, indicated cash going into my
account (proceeds of the short sale--actually a short call covered by stock). The second entry was negative indicating that I had bought back the call (cash going out). The account is an IRA so margin is not involved. Except for the stock purchase (which I am ignoring for the time being--I want only the CAGR for selling the call and buying it back), so as far as I know $55 does not enter into the problem. I did not invest $110 and get back $100 thus losing $10. I sold for $110 and bought back for $100 and kept $10. Hopefully, I have explained the example a little more clearly. "Fred Smith" wrote: But you told XIRR that you invested $110 and got back $100. So you lost money. That's what XIRR is telling you. Remember that to calculate investment returns, only the cash flow is important. The transactions in between have no effect on the return. Normally when you do a short sale, you need to put up 50% margin. Is that what you did in this case? If so, you invested $55 (cash out of your pocket), and received back $100. That's the transaction detail you need to feed to XIRR. Regards, Fred. "oukid" wrote in message ... I am trying to calculate the annual growth rate for a short sale. I've entered the first amount as a positive number (because it was cash coming in) and the second amount as a negative number (because it was cash going out). When I use XIRR I get a negative percentage as the annualized return. As a simple example, if I enter 110 on the first date and -100 a year later XIRR gives me negative 9.1% as the annualized return. Since I have 10 more at the end of the year, it seems that my return must be positive. Am I misinterpreting this result or what? Thanks for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR for short sales using XIRR
XIRR (nor any CAGR calculation) cannot do what you are asking it to do. I
know you "did not invest $110 and get back $100 thus losing $10", but that's what you told XIRR, which is why it gave you the results it did. XIRR, like all return on investment calculations, is designed to calculate return on investment. You're trying to calculate the return of a transaction. But the $110 or the $100 you are using in your example is not what you invested. The most common application of XIRR is to calculate the return of the account (the IRA in your eample). You feed XIRR the cash going in, the cash coming out and the final balance, and it will happily calculate the CAGR. As I stated, it's only the cash flow that determines return on investment. The transactions within the account are immaterial. If, for whatever reason, you still want to calculate the return on your transaction, do it this way: 1. Set up (or simulate) an account for this single transaction. 2. Ask your broker what you need to have in an account to do a $110 short sale. He'll tell you you need some amount of cash to cover the margin on the sale. Let's say the cash required is $55. So you make this deposit. 3. Sell short for $110. 4. Cover the short at $100. 5. You now have $65 in the account. 6. Tell XIRR you invested $55, and got back $65. It will calculate the CAGR for you. Regards, Fred. "oukid" wrote in message ... I thought that my first entry, being positive, indicated cash going into my account (proceeds of the short sale--actually a short call covered by stock). The second entry was negative indicating that I had bought back the call (cash going out). The account is an IRA so margin is not involved. Except for the stock purchase (which I am ignoring for the time being--I want only the CAGR for selling the call and buying it back), so as far as I know $55 does not enter into the problem. I did not invest $110 and get back $100 thus losing $10. I sold for $110 and bought back for $100 and kept $10. Hopefully, I have explained the example a little more clearly. "Fred Smith" wrote: But you told XIRR that you invested $110 and got back $100. So you lost money. That's what XIRR is telling you. Remember that to calculate investment returns, only the cash flow is important. The transactions in between have no effect on the return. Normally when you do a short sale, you need to put up 50% margin. Is that what you did in this case? If so, you invested $55 (cash out of your pocket), and received back $100. That's the transaction detail you need to feed to XIRR. Regards, Fred. "oukid" wrote in message ... I am trying to calculate the annual growth rate for a short sale. I've entered the first amount as a positive number (because it was cash coming in) and the second amount as a negative number (because it was cash going out). When I use XIRR I get a negative percentage as the annualized return. As a simple example, if I enter 110 on the first date and -100 a year later XIRR gives me negative 9.1% as the annualized return. Since I have 10 more at the end of the year, it seems that my return must be positive. Am I misinterpreting this result or what? Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sales Invoicing linked to Sales ledger(Accounts Receivable) | Excel Discussion (Misc queries) | |||
Add Sales Goals to Sales Report in Pivot Table | Excel Discussion (Misc queries) | |||
How do I calculate CAGR other than using XIRR function? | Excel Worksheet Functions | |||
How to calculate CAGR for mutual fund performance using XIRR | Excel Worksheet Functions |