XIRR question  beginner
XIRR question  beginner
This is my first attempt to use XIRR. I'm stuck and need some guidance. I'm trying to determine rate of return on one RO/IRA account for 2012. This is what I've done so far.
I opened an Excel worksheet. I input VALUES in column A. A1 is negative for account balance at 12/31/2011. A2 is positive for account balance at 12/31/2011. Then A3 through A12 list various withdrawals throughout the year. A13 is negative for account balance at 12/31/2012.
Column B lists the dates  manually entered in format 2011,12,31 for 12/31/2011. Etc.
Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
Can someone straighten me out? What have I done wrong?
Thanks for any help.
ML
I opened an Excel worksheet. I input VALUES in column A. A1 is negative for account balance at 12/31/2011. A2 is positive for account balance at 12/31/2011. Then A3 through A12 list various withdrawals throughout the year. A13 is negative for account balance at 12/31/2012.
Column B lists the dates  manually entered in format 2011,12,31 for 12/31/2011. Etc.
Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
Can someone straighten me out? What have I done wrong?
Thanks for any help.
ML
Re: XIRR question  beginner
I think 10% has to be 0.1lethean46 wrote:This is my first attempt to use XIRR. I'm stuck and need some guidance. I'm trying to determine rate of return on one RO/IRA account for 2012. This is what I've done so far.
I opened an Excel worksheet. I input VALUES in column A. A1 is negative for account balance at 12/31/2011. A2 is positive for account balance at 12/31/2011. Then A3 through A12 list various withdrawals throughout the year. A13 is negative for account balance at 12/31/2012.
Column B lists the dates  manually entered in format 2011,12,31 for 12/31/2011. Etc.
Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
Can someone straighten me out? What have I done wrong?
Thanks for any help.
ML
P.S. I just used it for the first time today as well.

 Posts: 1624
 Joined: Thu May 26, 2011 9:36 pm
Re: XIRR question  beginner
The starting balance would be positive and in the top most cell. Contributions are positive
Ending balance would be negative. Withdrawals are negative.
10% should be entered as .10
Enter the dates as 12/31/2012
Ending balance would be negative. Withdrawals are negative.
10% should be entered as .10
Enter the dates as 12/31/2012
Last edited by FinancialDave on Tue Jan 01, 2013 2:33 pm, edited 1 time in total.
I love simulated data. It turns the impossible into the possible!
Re: XIRR question  beginner
A2 is positive; A13 is negative. A1 is negative, but is not part of the range of XIRR.FinancialDave wrote:The starting balance would be positive.
Ending balance would be negative
10% should be entered as .10
Enter the dates as 12/31/2012
P.S. you can switch the starting and ending values from positive to negative and it makes no difference as long as the contributions and withdrawals match. I tried it this morning. If you have a column of entries, create a new column of entries with the signs reversed. I got the same answer either way, or did I get a negative XIRR?
Last edited by sscritic on Tue Jan 01, 2013 2:36 pm, edited 1 time in total.

 Posts: 1624
 Joined: Thu May 26, 2011 9:36 pm
Re: XIRR question  beginner
your range does need a starting balance on the first line and ending balance (negative) on the last line of the range.
I love simulated data. It turns the impossible into the possible!

 Posts: 1624
 Joined: Thu May 26, 2011 9:36 pm
Re: XIRR question  beginner
"Gummy" has a great tutorial on the use of the XIRR function:
http://www.financialwebring.org/gummystuff/xirr.htm
fd
http://www.financialwebring.org/gummystuff/xirr.htm
fd
I love simulated data. It turns the impossible into the possible!
Re: XIRR question  beginner
A2 is positive for account balance at 12/31/2011. ... A13 is negative for account balance at 12/31/2012.
XIRR(A2:A13,B2:B13,10%)
Re: XIRR question  beginner
Also, Microsoft recommends specifying dates using the =date() function rather than entering as text.
It is easy to do my regular 401K contributions as I just add 14 (biweekly paycheck) to the previous date.
It is easy to do my regular 401K contributions as I just add 14 (biweekly paycheck) to the previous date.
Re: XIRR question  beginner
Another PS.
As I discovered this morning as noted in another thread, XIRR will give you the wrong answer in a leap year if you use 12/31 to 12/31. XIRR only uses 365 days in a year (thanks to jonnyc for solving my confusion). You have to use 1/1/12 to 12/31/12 if you actually want to know your return for this year.
As I discovered this morning as noted in another thread, XIRR will give you the wrong answer in a leap year if you use 12/31 to 12/31. XIRR only uses 365 days in a year (thanks to jonnyc for solving my confusion). You have to use 1/1/12 to 12/31/12 if you actually want to know your return for this year.
Re: XIRR question  beginner
Test: Which of these four gives the correct answer?
Code: Select all
01/01/12 1000
12/31/12 1100
10.00%
Code: Select all
12/31/11 1000
12/31/12 1100
9.97%
Code: Select all
12/31/11 1000
12/31/12 1100
9.97%
Code: Select all
01/01/12 1000
12/31/12 1100
10.00%
Re: XIRR question  beginner
Wow. Thanks a lot.
I changed 10% to .10 and still get a #VALUE! result.
I don't know how to enter dates using the date function. EXACTLY, how would I do that? What I did was manually enter the dates in each cell using what I thought was the correct format. 2011,12,31 for 12/31/2011.
I've read a few of the gummy's and other links and still don't get it. I'm doing something real basic wrong, I think.
The good news is that this is something that I've wanted to learn how to do for a LONG time. And today is the day to start. The Poll asking for 2012 returns was a motivation, too. I don't want to guess. I want to get this right. And then I plan to go back to previous years.
The dates might be the issue. I don't know how to use the date function for Column B.
ML
I changed 10% to .10 and still get a #VALUE! result.
I don't know how to enter dates using the date function. EXACTLY, how would I do that? What I did was manually enter the dates in each cell using what I thought was the correct format. 2011,12,31 for 12/31/2011.
I've read a few of the gummy's and other links and still don't get it. I'm doing something real basic wrong, I think.
The good news is that this is something that I've wanted to learn how to do for a LONG time. And today is the day to start. The Poll asking for 2012 returns was a motivation, too. I don't want to guess. I want to get this right. And then I plan to go back to previous years.
The dates might be the issue. I don't know how to use the date function for Column B.
ML
Re: XIRR question  beginner
Excell 2007 help.Syntax
XIRR(values,dates,guess)
Values is a series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365day year. The series of values must contain at least one positive and one negative value.
Dates is a schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
Guess is a number that you guess is close to the result of XIRR.
FWIW I don't use the date function. Just make sure the field is formatted as "date".
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
Re: XIRR question  beginner
For dates I just enter 12/31/2011.lethean46 wrote:Wow. Thanks a lot.
I changed 10% to .10 and still get a #VALUE! result.
I don't know how to enter dates using the date function. EXACTLY, how would I do that? What I did was manually enter the dates in each cell using what I thought was the correct format. 2011,12,31 for 12/31/2011.
I've read a few of the gummy's and other links and still don't get it. I'm doing something real basic wrong, I think.
The good news is that this is something that I've wanted to learn how to do for a LONG time. And today is the day to start. The Poll asking for 2012 returns was a motivation, too. I don't want to guess. I want to get this right. And then I plan to go back to previous years.
The dates might be the issue. I don't know how to use the date function for Column B.
ML
Re: XIRR question  beginner
Start simple. Can you do my example with only two dates and two values (1000 and 1100)?lethean46 wrote:Wow. Thanks a lot.
I changed 10% to .10 and still get a #VALUE! result.
 Aptenodytes
 Posts: 3752
 Joined: Tue Feb 08, 2011 8:39 pm
Re: XIRR question  beginner
Don't use commas in a date field. Dashes or slashes.
Re: XIRR question  beginner
XIRR(A2:A13,B2:B13,0.05)
Stupid spreadsheet newbie question: what if you have, say, quarterly subtotals in D5,G5, and J5, i.e. what's the syntax where instead of a contiguous range A2:A13 your values are in multiple nonadjacent cells?
I'm not guessing the right phrasing for google or the included help, apparently.
This is Openoffice Calc, FWIW, but an excel answer might well apply.
Stupid spreadsheet newbie question: what if you have, say, quarterly subtotals in D5,G5, and J5, i.e. what's the syntax where instead of a contiguous range A2:A13 your values are in multiple nonadjacent cells?
I'm not guessing the right phrasing for google or the included help, apparently.
This is Openoffice Calc, FWIW, but an excel answer might well apply.

 Posts: 1624
 Joined: Thu May 26, 2011 9:36 pm
Re: XIRR question  beginner
My OpenOffice spreadsheet has the same XIRR formula that appears to work the same way.
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
If you want to calculate quarterly XIRR then you need to still follow the above rules for each starting and ending value  otherwise the intermediate quarterly totals have no value for the yearly IRR.
fd
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
If you want to calculate quarterly XIRR then you need to still follow the above rules for each starting and ending value  otherwise the intermediate quarterly totals have no value for the yearly IRR.
fd
I love simulated data. It turns the impossible into the possible!
Re: XIRR question  beginner
I don't think you can do that. Can you give an example of what your spreadsheet looks like?whomever wrote:XIRR(A2:A13,B2:B13,0.05)
Stupid spreadsheet newbie question: what if you have, say, quarterly subtotals in D5,G5, and J5, i.e. what's the syntax where instead of a contiguous range A2:A13 your values are in multiple nonadjacent cells?
Re: XIRR question  beginner
SUCCESS!
I wrote a more expansive reply and then was interrupted. When I came back to the computer, I saved a draft of my reply still on the screen. Then I had to log in again.
Where can I find my reply as the draft that was saved?
ML
I wrote a more expansive reply and then was interrupted. When I came back to the computer, I saved a draft of my reply still on the screen. Then I had to log in again.
Where can I find my reply as the draft that was saved?
ML
Re: XIRR question  beginner
Just to double check, I moved to another computer where I have openoffice.FinancialDave wrote:My OpenOffice spreadsheet has the same XIRR formula that appears to work the same way.
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
Code: Select all
01/01/12 1000
12/31/12 1100
0.10
Re: XIRR question  beginner
If I lend you money, your first transaction is in, my first transaction is out. Every time you pay me, your transaction is out, my transaction is in. At the end when we use XIRR to compute the actual interest rate, will we get different answers? No, if you get 3% starting with your positive in, I will get 3% starting with my negative out.
Re: XIRR question  beginner
I do results quarterly, so column A might be values on 01Jan12 (rows are the individual funds or whatever). Column B is additions that quarter, column C is gain/loss for the quarter. Column D starts over for the 2nd quarter (Mar/Apr/May).
The rows are grouped by cash/bond/stock, and subtotaled. Let's say the bond totals are in row 10. To get my bond return, I'd take the initial value from A10 then use the additions from B10 (1Q additions), E10 (2Q additions), H10 (3Q additions), ..., with the corresponding end of quarter dates (if I understand correctly).
It's not as accurate as getting the date of each individual addition, but that would be hard  there are bimonthly additions from payroll deductions, and random ones from whenever I notice there is enough spare case floating around. I only get quarterly statements from some of the funds, so just aggregating by quarter is easiest.
I can make a column X that has values =A10,=B10,=E10, ..., and a corresponding column Z with the dates, then use xirr(X1,Xwhatever, Z1,Zwhatever, 0.05). I was just hoping there was some syntax where e.g. [A5,A6,A7] or somesuch was the same as A5:A7, so I could pass [A10,B10,E10] as a sequence/array/set/whatever the spreadsheet term is to XIRR().
The rows are grouped by cash/bond/stock, and subtotaled. Let's say the bond totals are in row 10. To get my bond return, I'd take the initial value from A10 then use the additions from B10 (1Q additions), E10 (2Q additions), H10 (3Q additions), ..., with the corresponding end of quarter dates (if I understand correctly).
It's not as accurate as getting the date of each individual addition, but that would be hard  there are bimonthly additions from payroll deductions, and random ones from whenever I notice there is enough spare case floating around. I only get quarterly statements from some of the funds, so just aggregating by quarter is easiest.
I can make a column X that has values =A10,=B10,=E10, ..., and a corresponding column Z with the dates, then use xirr(X1,Xwhatever, Z1,Zwhatever, 0.05). I was just hoping there was some syntax where e.g. [A5,A6,A7] or somesuch was the same as A5:A7, so I could pass [A10,B10,E10] as a sequence/array/set/whatever the spreadsheet term is to XIRR().

 Posts: 1624
 Joined: Thu May 26, 2011 9:36 pm
Re: XIRR question  beginner
sscritic wrote:If I lend you money, your first transaction is in, my first transaction is out. Every time you pay me, your transaction is out, my transaction is in. At the end when we use XIRR to compute the actual interest rate, will we get different answers? No, if you get 3% starting with your positive in, I will get 3% starting with my negative out.
As noted XIRR doesn't care (nor does your financial calculator) whether you use + or  for withdrawals, you just need to be consistent and the starting and ending balance need to be opposite polarity and the starting balance should be same polarity as a contribution (money into account) and ending balance should be same polarity as withdrawals (money out of account).
Even though money is not necessarily going out of the account you need to make sure the sign of the balance is correct.
fd
I love simulated data. It turns the impossible into the possible!
Re: XIRR question  beginner
I think we agree, which is why I didn't understand why you kept writing the the opening balance had to be a positive. As I understand XIRR, there is an initial investment, and at the end of the time period, everything is sold (only logically, as you say; for tax reasons the real money is left in the account). But where does that money come from and where does it go? Obviously, you need a noninterest bearing checking account. Your initial investment purchase is a withdrawal from your checking account, and when you sell everything at the end of the period, the money goes back into the checking account.FinancialDave wrote: As noted XIRR doesn't care (nor does your financial calculator) whether you use + or  for withdrawals, you just need to be consistent and the starting and ending balance need to be opposite polarity and the starting balance should be same polarity as a contribution (money into account) and ending balance should be same polarity as withdrawals (money out of account).
Even though money is not necessarily going out of the account you need to make sure the sign of the balance is correct.
Looking at your own ability to spend money by writing checks, you start with a negative and end with a positive. It doesn't matter if you look at your checking account starting with a negative and ending with a positive or your investment account starting with a positive and ending with a negative. You got the same return in your checking account as you did in your investment account (actually your investment account was logically closed; all your return is in the checking account). All you have to do is be consistent with what's a plus and what's a minus.
Re: XIRR question  beginner
Great. What made it work?lethean46 wrote:SUCCESS!
I wrote a more expansive reply and then was interrupted. When I came back to the computer, I saved a draft of my reply still on the screen. Then I had to log in again.
Where can I find my reply as the draft that was saved?
ML
Re: XIRR question  beginner
I consider myself pretty with it with Excel, I have my spreadsheet in excel, etc. I read the explaination a couple of time and didn't get it. Had I tried it it would make more sense I'm sure, but I personally am ok without. Not going to change my allocation regardless of previous results.
Re: XIRR question  beginner
XIRR (and, similarly, IRR, NPV, XNPV) is a cash flow function: cash inflows have a positive sign and cash outflows have a negative sign.
When I teach International Finance at UC, Irvine (and in my CFA courses), I tell my students that in cash flow problems they should pick a point of view  investor or account, lender or borrower, whatever  and always use that point of view; that way, they'll never get confused about whether a cash flow is positive or negative.
For this type of problem, you need to think of it as the investor depositing the initial value of the investment, and withdrawing the final value of the investment: it's a complete investment, inception to conclusion.
If you take the view of the investor, then the initial account balance is a negative number: the investor has a cash outflow when he puts the money into the account. If he makes further contributions, they're negative as well; if he makes withdrawals, they're positive. The final balance is positive.
If you take the view of the account, then the initial balance is positive (the account receives that money from the investor), deposits are positive, withdrawals are negative, and the final balance is negative (the account pays that back to the investor).
When I teach International Finance at UC, Irvine (and in my CFA courses), I tell my students that in cash flow problems they should pick a point of view  investor or account, lender or borrower, whatever  and always use that point of view; that way, they'll never get confused about whether a cash flow is positive or negative.
For this type of problem, you need to think of it as the investor depositing the initial value of the investment, and withdrawing the final value of the investment: it's a complete investment, inception to conclusion.
If you take the view of the investor, then the initial account balance is a negative number: the investor has a cash outflow when he puts the money into the account. If he makes further contributions, they're negative as well; if he makes withdrawals, they're positive. The final balance is positive.
If you take the view of the account, then the initial balance is positive (the account receives that money from the investor), deposits are positive, withdrawals are negative, and the final balance is negative (the account pays that back to the investor).
Nope: Excel knows that 10% is 0.1.sscritic wrote:I think 10% has to be 0.1lethean46 wrote:Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
This assumes you're looking at it from the account's point of view. That's one way to look at it, and it works. But it's not the only way to look at it.FinancialDave wrote:The starting balance would be positive and in the top most cell. Contributions are positive
Ending balance would be negative. Withdrawals are negative.
Not exactly. The first entry in the range has to be the beginning date and balance, but the rest of the entries can be in any order (and the dates must not be earlier than the beginning date, of course).FinancialDave wrote:your range does need a starting balance on the first line and ending balance (negative) on the last line of the range.
Simplify the complicated side; don't complify the simplicated side.

 Posts: 1624
 Joined: Thu May 26, 2011 9:36 pm
Re: XIRR question  beginner
magician wrote:XIRR (and, similarly, IRR, NPV, XNPV) is a cash flow function: cash inflows have a positive sign and cash outflows have a negative sign.
When I teach International Finance at UC, Irvine (and in my CFA courses), I tell my students that in cash flow problems they should pick a point of view  investor or account, lender or borrower, whatever  and always use that point of view; that way, they'll never get confused about whether a cash flow is positive or negative.
For this type of problem, you need to think of it as the investor depositing the initial value of the investment, and withdrawing the final value of the investment: it's a complete investment, inception to conclusion.
If you take the view of the investor, then the initial account balance is a negative number: the investor has a cash outflow when he puts the money into the account. If he makes further contributions, they're negative as well; if he makes withdrawals, they're positive. The final balance is positive.
If you take the view of the account, then the initial balance is positive (the account receives that money from the investor), deposits are positive, withdrawals are negative, and the final balance is negative (the account pays that back to the investor).
Nope: Excel knows that 10% is 0.1.sscritic wrote:I think 10% has to be 0.1lethean46 wrote:Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
This assumes you're looking at it from the account's point of view. That's one way to look at it, and it works. But it's not the only way to look at it.FinancialDave wrote:The starting balance would be positive and in the top most cell. Contributions are positive
Ending balance would be negative. Withdrawals are negative.
Not exactly. The first entry in the range has to be the beginning date and balance, but the rest of the entries can be in any order (and the dates must not be earlier than the beginning date, of course).FinancialDave wrote:your range does need a starting balance on the first line and ending balance (negative) on the last line of the range.
Magician,
Completely agree with what you say, especially that either way works  JUST in this case the question is looked at from the "accounts" point of view because the whole purpose is to download all the transactions from say your investing website and calculate the IRR  if you had to change EVERY sign in the download it is not the best way to think of it.
Just go with the flow and consider contributions to the account and the starting balance as positive  less work less chances for an error.
fd
I love simulated data. It turns the impossible into the possible!
Re: XIRR question  beginner
I agree . . . in this case. As I have in my signature, there's no reason to complify the simplicated side.FinancialDave wrote:, , , in this case the question is looked at from the "accounts" point of view because the whole purpose is to download all the transactions from say your investing website and calculate the IRR  if you had to change EVERY sign in the download it is not the best way to think of it.
Simplify the complicated side; don't complify the simplicated side.
Re: XIRR question  beginner
FYI, I was having problems with XIRR in OpenCalc. It was giving me Err:508 when I put something like XIRR(A2:A13,B2:B13). I found that I had to use a semicolon instead of a comma for some reason: XIRR(A2:A13;B2:B13). Then it worked fine.whomever wrote:XIRR(A2:A13,B2:B13,0.05)
Stupid spreadsheet newbie question: what if you have, say, quarterly subtotals in D5,G5, and J5, i.e. what's the syntax where instead of a contiguous range A2:A13 your values are in multiple nonadjacent cells?
I'm not guessing the right phrasing for google or the included help, apparently.
This is Openoffice Calc, FWIW, but an excel answer might well apply.
Re: XIRR question  beginner
The syntax should bewhomever wrote:I was just hoping there was some syntax where e.g. [A5,A6,A7] or somesuch was the same as A5:A7, so I could pass [A10,B10,E10] as a sequence/array/set/whatever the spreadsheet term is to XIRR().
=XIRR((A10,B10,E10),(A1,B1,E1))
where row 10 has the values and row 1 has the dates.
But is does not work with Excel's XIRR function. I don't know if it works with Calc.
The comma is the union operator. It combines multiple ranges into a single range with multiple areas. The parentheses mark which ranges are being unioned.
But Excel's XIRR function does not accept ranges with multiple areas. That is one reason I wrote my own version of XIRR posted here. http://www.bogleheads.org/forum/viewtop ... 130#p51130
Let us know if Calc's XIRR function works with this syntax.
Ron
Money is fungible 
Abbreviations and Acronyms
Re: XIRR question  beginner
You've got it backwards. Money going from you to your account should be negative. The final balance should be positivethat way you have something to retire on . Nevertheless, the function works either way.FinancialDave wrote:My OpenOffice spreadsheet has the same XIRR formula that appears to work the same way.
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
If you want to calculate quarterly XIRR then you need to still follow the above rules for each starting and ending value  otherwise the intermediate quarterly totals have no value for the yearly IRR.
fd
For the quarter you can input a withdrawal and a deposit on the date to make the XIRR.  Tet
Re: XIRR question  beginner
Replace 10% with 0. If that doesn't work try 1. Like this: <=XIRR(F374:F375,B374:B375,0)>  Tetlethean46 wrote:Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
Re: XIRR question  beginner
sscritic wrote:
As I discovered this morning as noted in another thread, XIRR will give you the wrong answer in a leap year if you use 12/31 to 12/31. XIRR only uses 365 days in a year (thanks to jonnyc for solving my confusion). You have to use 1/1/12 to 12/31/12 if you actually want to know your return for this year.
Thanks. That's probably why my yeartodate and oneyear returns differ slightly, as do my threeyear and fiveyear returns to a lesser extent. (My spreadsheet only goes back about seven years, so I don't have tenyear returns.)
Too bad, I'll forget all this in 2016.
Gordon
Re: XIRR question  beginner
Once again, XIRR does a cash flow calculation. It is not correct to say that "[m]oney going from you to your account should be negative" as if that's the only way to look at the situation. That's one viewpoint: that of the investor. Taking the account's viewpoint  in which money going from the investor to their account is a positive cash flow  is an equally valid way to view the problem.tetractys wrote:You've got it backwards. Money going from you to your account should be negative. The final balance should be positivethat way you have something to retire on . Nevertheless, the function works either way.FinancialDave wrote:My OpenOffice spreadsheet has the same XIRR formula that appears to work the same way.
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
If you want to calculate quarterly XIRR then you need to still follow the above rules for each starting and ending value  otherwise the intermediate quarterly totals have no value for the yearly IRR.
fd
For the quarter you can input a withdrawal and a deposit on the date to make the XIRR.  Tet
Simplify the complicated side; don't complify the simplicated side.
Re: XIRR question  beginner
There are ways to ensure that this doesn't happen. I cannot (in good conscience) recommend some of them.gkaplan wrote:Too bad, I'll forget all this in 2016.
Simplify the complicated side; don't complify the simplicated side.
Re: XIRR question  beginner
Try to read between the lines; there's no anality in my answer, only some frivolity that agrees with you.  Tetmagician wrote:Once again, XIRR does a cash flow calculation. It is not correct to say that "[m]oney going from you to your account should be negative" as if that's the only way to look at the situation. That's one viewpoint: that of the investor. Taking the account's viewpoint  in which money going from the investor to their account is a positive cash flow  is an equally valid way to view the problem.tetractys wrote:You've got it backwards. Money going from you to your account should be negative. The final balance should be positivethat way you have something to retire on . Nevertheless, the function works either way.FinancialDave wrote:My OpenOffice spreadsheet has the same XIRR formula that appears to work the same way.
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
If you want to calculate quarterly XIRR then you need to still follow the above rules for each starting and ending value  otherwise the intermediate quarterly totals have no value for the yearly IRR.
fd
For the quarter you can input a withdrawal and a deposit on the date to make the XIRR.  Tet
Re: XIRR question  beginner
@Oicuryy:
That was one of my wild guesses . I get an 'Err:502'. The cell contents display as '=XIRR((D32~E32),(12/31/2011~3/31/2012),0.05)'. I entered commas where the tildes are displayed, the
tildes are replaced after I hit enter.
Same results when I use e.g. date(2011,12,31).
That was one of my wild guesses . I get an 'Err:502'. The cell contents display as '=XIRR((D32~E32),(12/31/2011~3/31/2012),0.05)'. I entered commas where the tildes are displayed, the
tildes are replaced after I hit enter.
Same results when I use e.g. date(2011,12,31).
Re: XIRR question  beginner
OK. Looks like I lost my saved draft which explained my errors and subsequent corrections.
1. I finally figured out how to enter dates for XIRR. Click on a Date Cell. Then I used the open bar and entered: =date(2011,12,31). This then showed in the Date Cell as 12/31/2011. Repeated that process for all Date Cells.
2. I got a different error message which I forget now. It indicated that there were non ajacent Cells. So I deleted data in Cell A1 and Cell B1. Voila.
3. Then, the new error message was in the Cell which held the XIRR function: $ 0. Say what?
4. So I changed the Cell with XIRR function from $ to %. YES.
Thank you all SO much. Using XIRR is awesome. I still have lots to learn though.
I have a few more questions which I'll write in separate post on this same thread.
ML
1. I finally figured out how to enter dates for XIRR. Click on a Date Cell. Then I used the open bar and entered: =date(2011,12,31). This then showed in the Date Cell as 12/31/2011. Repeated that process for all Date Cells.
2. I got a different error message which I forget now. It indicated that there were non ajacent Cells. So I deleted data in Cell A1 and Cell B1. Voila.
3. Then, the new error message was in the Cell which held the XIRR function: $ 0. Say what?
4. So I changed the Cell with XIRR function from $ to %. YES.
Thank you all SO much. Using XIRR is awesome. I still have lots to learn though.
I have a few more questions which I'll write in separate post on this same thread.
ML
Re: XIRR question  beginner
Sorry. When I read "You've got it backwards" it sounded as though you meant . . . well . . . you know.tetractys wrote:Try to read between the lines; there's no anality in my answer, only some frivolity that agrees with you.  Tetmagician wrote:Once again, XIRR does a cash flow calculation. It is not correct to say that "[m]oney going from you to your account should be negative" as if that's the only way to look at the situation. That's one viewpoint: that of the investor. Taking the account's viewpoint  in which money going from the investor to their account is a positive cash flow  is an equally valid way to view the problem.tetractys wrote:You've got it backwards. Money going from you to your account should be negative. The final balance should be positivethat way you have something to retire on . Nevertheless, the function works either way.FinancialDave wrote:My OpenOffice spreadsheet has the same XIRR formula that appears to work the same way.
The XIRR will only calculate one rate of return, from a positive starting balance to a negative ending balance, over a specified date period.
If you want to calculate quarterly XIRR then you need to still follow the above rules for each starting and ending value  otherwise the intermediate quarterly totals have no value for the yearly IRR.
fd
For the quarter you can input a withdrawal and a deposit on the date to make the XIRR.  Tet
Simplify the complicated side; don't complify the simplicated side.
Re: XIRR question  beginner
I have 4 basic accounts. RO/IRA, tIRA, Roth IRA, Taxable.
I have 3 years of data entered now with XIRR results for all accounts for each of the 3 years, excepting the Roth IRA. Roth IRA has only 2012 data entered and an XIRR result for 2012. (I don't want to do 2011 and 2010 for the Roth right now.)
Anyway, this was my format. And I don't know how to proceed.
Accounts: RO/IRA ..... tIRA ...... Roth IRA ....... Taxable.
Data: ........2010 ....... 2010 ............................2010
Data: ........2011 ....... 2011 ............................2011
Data: ....... 2012 ........2012 ...... 2012 ..............2012
XIRR: ........2010 ........2010 ...........................2010
XIRR: ........2011 ........2011 ...........................2011
XIRR: ........2012 ........2012 ........2012 ............2012
I hope that makes sense. IOW, the XIRR functions are at the bottom of the page.
Looking at the last line, the 2012 XIRR line. I have an XIRR % for each account listed in the first line, above. The balances for each account are NOT similar, at all.
My question is. How would you suggest I treat the 4 different 2012 XIRR %s to get ONE return % that has meaning  for all 4 accounts together for 2012? Is there another operation that can be done? I DO like having XIRR results for each account, for each year. I'd like to see the ONE return % for 2012 in another column to the Right but still in the 2012 line.
Then, I'd also like ONE XIRR % for the RO/IRA for 2010, 2011, and 2012 together. I'd like to see that ONE XIRR % for the 3 years as a new LAST line in the RO/IRA column. Ditto for the tIRA column. Ditto for the Taxable column.
What do you suggest? Again, I'd like to maintain XIRR for each account, for each year. But how to proceed from here?
Thanks.
ML
I have 3 years of data entered now with XIRR results for all accounts for each of the 3 years, excepting the Roth IRA. Roth IRA has only 2012 data entered and an XIRR result for 2012. (I don't want to do 2011 and 2010 for the Roth right now.)
Anyway, this was my format. And I don't know how to proceed.
Accounts: RO/IRA ..... tIRA ...... Roth IRA ....... Taxable.
Data: ........2010 ....... 2010 ............................2010
Data: ........2011 ....... 2011 ............................2011
Data: ....... 2012 ........2012 ...... 2012 ..............2012
XIRR: ........2010 ........2010 ...........................2010
XIRR: ........2011 ........2011 ...........................2011
XIRR: ........2012 ........2012 ........2012 ............2012
I hope that makes sense. IOW, the XIRR functions are at the bottom of the page.
Looking at the last line, the 2012 XIRR line. I have an XIRR % for each account listed in the first line, above. The balances for each account are NOT similar, at all.
My question is. How would you suggest I treat the 4 different 2012 XIRR %s to get ONE return % that has meaning  for all 4 accounts together for 2012? Is there another operation that can be done? I DO like having XIRR results for each account, for each year. I'd like to see the ONE return % for 2012 in another column to the Right but still in the 2012 line.
Then, I'd also like ONE XIRR % for the RO/IRA for 2010, 2011, and 2012 together. I'd like to see that ONE XIRR % for the 3 years as a new LAST line in the RO/IRA column. Ditto for the tIRA column. Ditto for the Taxable column.
What do you suggest? Again, I'd like to maintain XIRR for each account, for each year. But how to proceed from here?
Thanks.
ML
Re: XIRR question  beginner
Make a new column totaling the the entries for the four columns, then use XIRR on that column.lethean46 wrote:I have 4 basic accounts. RO/IRA, tIRA, Roth IRA, Taxable.
I have 3 years of data entered now with XIRR results for all accounts for each of the 3 years, excepting the Roth IRA. Roth IRA has only 2012 data entered and an XIRR result for 2012. (I don't want to do 2011 and 2010 for the Roth right now.)
.
.
.
My question is. How would you suggest I treat the 4 different 2012 XIRR %s to get ONE return % that has meaning  for all 4 accounts together for 2012?
Just use the XIRR function on all of the data (2010, 2011, and 2012, together).lethean46 wrote:Then, I'd also like ONE XIRR % for the RO/IRA for 2010, 2011, and 2012 together. I'd like to see that ONE XIRR % for the 3 years as a new LAST line in the RO/IRA column. Ditto for the tIRA column. Ditto for the Taxable column.
My pleasure.lethean46 wrote:Thanks.
Simplify the complicated side; don't complify the simplicated side.
Re: XIRR question  beginner
[quote="magician"][quote="lethean46"]I have 4 basic accounts. RO/IRA, tIRA, Roth IRA, Taxable.
I have 3 years of data entered now with XIRR results for all accounts for each of the 3 years, excepting the Roth IRA. Roth IRA has only 2012 data entered and an XIRR result for 2012. (I don't want to do 2011 and 2010 for the Roth right now.)
.
.
.
My question is. How would you suggest I treat the 4 different 2012 XIRR %s to get ONE return % that has meaning  for all 4 accounts together for 2012?[/quote]
Make a new column totaling the the entries for the four columns, then use XIRR on that column.
[quote="lethean46"]Then, I'd also like ONE XIRR % for the RO/IRA for 2010, 2011, and 2012 together. I'd like to see that ONE XIRR % for the 3 years as a new LAST line in the RO/IRA column. Ditto for the tIRA column. Ditto for the Taxable column.[/quote]
Just use the XIRR function on all of the data (2010, 2011, and 2012, together).
[quote="lethean46"]Thanks.[/quote]
My pleasure.[/quote]
I don't understand. Will you explain it further please?
Ending Values for 2011 RO/IRA, tIRA, Roth IRA, and Taxable are at: A33, D33, G33, and J33.
Ending Dates for 2011 RO/IRA, tIRA, Roth IRA, and Taxable are at: B33, E33, H33, and K33.
(Various withdrawal Values and Dates are within this Range.)
Ending Values for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: A44, D44, G44, and J44.
Ending Dates for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: B44, E44, H44, and K44.
XIRR formulas for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: A49, D49, G49, and J49.
You said, "Make a new column totaling the entries for the four columns, then use XIRR on that column."
Exactly HOW do I DO that? I don't understand what to do.
Am I to total EACH line (33 through 44) into a new column? And am I also to have an additional Date column for each of those lines? That's 2 more columns for lines 33 through 44.
I have printed the worksheet as is. I think I can see that I should delete Columns C, F, and I which are empty for spacing. I'm afraid to try doing that right now because I'm afraid that I could screw up existing formulas. Any pointers on the correct way to delete empty Columns without messing up the existing formulas?
I can also see that I am going to have to scrunch the remaining columns a bit closer together AND reduce font size  in order to make room for the two additional columns. I'm already in landscape print. And I'd rather the columns not cross into two pages, if that's possible. We'll see.
Thanks a LOT.
ML
I have 3 years of data entered now with XIRR results for all accounts for each of the 3 years, excepting the Roth IRA. Roth IRA has only 2012 data entered and an XIRR result for 2012. (I don't want to do 2011 and 2010 for the Roth right now.)
.
.
.
My question is. How would you suggest I treat the 4 different 2012 XIRR %s to get ONE return % that has meaning  for all 4 accounts together for 2012?[/quote]
Make a new column totaling the the entries for the four columns, then use XIRR on that column.
[quote="lethean46"]Then, I'd also like ONE XIRR % for the RO/IRA for 2010, 2011, and 2012 together. I'd like to see that ONE XIRR % for the 3 years as a new LAST line in the RO/IRA column. Ditto for the tIRA column. Ditto for the Taxable column.[/quote]
Just use the XIRR function on all of the data (2010, 2011, and 2012, together).
[quote="lethean46"]Thanks.[/quote]
My pleasure.[/quote]
I don't understand. Will you explain it further please?
Ending Values for 2011 RO/IRA, tIRA, Roth IRA, and Taxable are at: A33, D33, G33, and J33.
Ending Dates for 2011 RO/IRA, tIRA, Roth IRA, and Taxable are at: B33, E33, H33, and K33.
(Various withdrawal Values and Dates are within this Range.)
Ending Values for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: A44, D44, G44, and J44.
Ending Dates for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: B44, E44, H44, and K44.
XIRR formulas for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: A49, D49, G49, and J49.
You said, "Make a new column totaling the entries for the four columns, then use XIRR on that column."
Exactly HOW do I DO that? I don't understand what to do.
Am I to total EACH line (33 through 44) into a new column? And am I also to have an additional Date column for each of those lines? That's 2 more columns for lines 33 through 44.
I have printed the worksheet as is. I think I can see that I should delete Columns C, F, and I which are empty for spacing. I'm afraid to try doing that right now because I'm afraid that I could screw up existing formulas. Any pointers on the correct way to delete empty Columns without messing up the existing formulas?
I can also see that I am going to have to scrunch the remaining columns a bit closer together AND reduce font size  in order to make room for the two additional columns. I'm already in landscape print. And I'd rather the columns not cross into two pages, if that's possible. We'll see.
Thanks a LOT.
ML
Re: XIRR question  beginner
I'll shoot you a PM.lethean46 wrote:I don't understand. Will you explain it further please?magician wrote:Make a new column totaling the the entries for the four columns, then use XIRR on that column.lethean46 wrote:I have 4 basic accounts. RO/IRA, tIRA, Roth IRA, Taxable.
I have 3 years of data entered now with XIRR results for all accounts for each of the 3 years, excepting the Roth IRA. Roth IRA has only 2012 data entered and an XIRR result for 2012. (I don't want to do 2011 and 2010 for the Roth right now.)
.
.
.
My question is. How would you suggest I treat the 4 different 2012 XIRR %s to get ONE return % that has meaning  for all 4 accounts together for 2012?
Just use the XIRR function on all of the data (2010, 2011, and 2012, together).lethean46 wrote:Then, I'd also like ONE XIRR % for the RO/IRA for 2010, 2011, and 2012 together. I'd like to see that ONE XIRR % for the 3 years as a new LAST line in the RO/IRA column. Ditto for the tIRA column. Ditto for the Taxable column.
My pleasure.lethean46 wrote:Thanks.
Ending Values for 2011 RO/IRA, tIRA, Roth IRA, and Taxable are at: A33, D33, G33, and J33.
Ending Dates for 2011 RO/IRA, tIRA, Roth IRA, and Taxable are at: B33, E33, H33, and K33.
(Various withdrawal Values and Dates are within this Range.)
Ending Values for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: A44, D44, G44, and J44.
Ending Dates for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: B44, E44, H44, and K44.
XIRR formulas for 2012 RO/IRA, tIRA, Roth IRA, and Taxable are at: A49, D49, G49, and J49.
You said, "Make a new column totaling the entries for the four columns, then use XIRR on that column."
Exactly HOW do I DO that? I don't understand what to do.
Am I to total EACH line (33 through 44) into a new column? And am I also to have an additional Date column for each of those lines? That's 2 more columns for lines 33 through 44.
I have printed the worksheet as is. I think I can see that I should delete Columns C, F, and I which are empty for spacing. I'm afraid to try doing that right now because I'm afraid that I could screw up existing formulas. Any pointers on the correct way to delete empty Columns without messing up the existing formulas?
I can also see that I am going to have to scrunch the remaining columns a bit closer together AND reduce font size  in order to make room for the two additional columns. I'm already in landscape print. And I'd rather the columns not cross into two pages, if that's possible. We'll see.
Thanks a LOT.
ML
Simplify the complicated side; don't complify the simplicated side.
Re: XIRR question  beginner
magician
THANKS. A PM might? be premature as I think I am getting it. I'm in process of entering each of those lines as totals with associated dates. So far, so good. I'm almost done with that part and I think I can also do the total 2012 XIRR for the four accounts. I was hoping to head off reply(s) as I know they are time consuming to write.
I did delete a column, just fine. It didn't mess up formulas. But then I decided that I liked the empty space and inserted the column back to the worksheet.
Thanks again. I'll look for the PM. I appreciate the help. I'm getting more comfortable. I need to get over this fear of messing up the whole worksheet.
ML
THANKS. A PM might? be premature as I think I am getting it. I'm in process of entering each of those lines as totals with associated dates. So far, so good. I'm almost done with that part and I think I can also do the total 2012 XIRR for the four accounts. I was hoping to head off reply(s) as I know they are time consuming to write.
I did delete a column, just fine. It didn't mess up formulas. But then I decided that I liked the empty space and inserted the column back to the worksheet.
Thanks again. I'll look for the PM. I appreciate the help. I'm getting more comfortable. I need to get over this fear of messing up the whole worksheet.
ML
Re: XIRR question  beginner
Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
Try it, set your first value to $0.
Try it, set your first value to $0.
Re: XIRR question  beginner
I don't.JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
What else would you have it do?
Simplify the complicated side; don't complify the simplicated side.
 Aptenodytes
 Posts: 3752
 Joined: Tue Feb 08, 2011 8:39 pm
Re: XIRR question  beginner
It doesn't make any sense to start with a balance of 0, because for the period you had a zero balance your return would be undefined (zero divided by zero).JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
Try it, set your first value to $0.
Just start with your first positive balance.
Re: XIRR question  beginner
I don't either, but one might prefer an error response such as num# rather than an actual number.magician wrote:I don't.JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
What else would you have it do?
Re: XIRR question  beginner
Either 0% or an error response should alert the user that something's amiss, and neither will tell him specifically that the problem is the initial balance. I'm not convinced that it's a sizable improvementdbr wrote:I don't either, but one might prefer an error response such as num# rather than an actual number.magician wrote:I don't.JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
What else would you have it do?
Simplify the complicated side; don't complify the simplicated side.