XIRR question - beginner

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

XIRR question - beginner

Post by lethean46 » Tue Jan 01, 2013 2:28 pm

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

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 2:30 pm

lethean46 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
I think 10% has to be 0.1

P.S. I just used it for the first time today as well.

FinancialDave
Posts: 1547
Joined: Thu May 26, 2011 9:36 pm

Re: XIRR question - beginner

Post by FinancialDave » Tue Jan 01, 2013 2:32 pm

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
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!

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 2:33 pm

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
A2 is positive; A13 is negative. A1 is negative, but is not part of the range of XIRR.

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.

FinancialDave
Posts: 1547
Joined: Thu May 26, 2011 9:36 pm

Re: XIRR question - beginner

Post by FinancialDave » Tue Jan 01, 2013 2:35 pm

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!

FinancialDave
Posts: 1547
Joined: Thu May 26, 2011 9:36 pm

Re: XIRR question - beginner

Post by FinancialDave » Tue Jan 01, 2013 2:37 pm

"Gummy" has a great tutorial on the use of the XIRR function:

http://www.financialwebring.org/gummy-stuff/xirr.htm

fd
I love simulated data. It turns the impossible into the possible!

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 2:38 pm

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%)

stan1
Posts: 5498
Joined: Mon Oct 08, 2007 4:35 pm

Re: XIRR question - beginner

Post by stan1 » Tue Jan 01, 2013 2:40 pm

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.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 2:43 pm

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 jon-nyc 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.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 2:54 pm

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%

lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

Re: XIRR question - beginner

Post by lethean46 » Tue Jan 01, 2013 2:58 pm

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

User avatar
Doc
Posts: 8134
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: XIRR question - beginner

Post by Doc » Tue Jan 01, 2013 2:59 pm

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 365-day 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.
Excell 2007 help.

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.

rr2
Posts: 1023
Joined: Wed Nov 19, 2008 10:04 pm

Re: XIRR question - beginner

Post by rr2 » Tue Jan 01, 2013 2:59 pm

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
For dates I just enter 12/31/2011.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 3:06 pm

lethean46 wrote:Wow. Thanks a lot.

I changed 10% to .10 and still get a #VALUE! result.
Start simple. Can you do my example with only two dates and two values (1000 and 1100)?

User avatar
Aptenodytes
Posts: 3751
Joined: Tue Feb 08, 2011 8:39 pm

Re: XIRR question - beginner

Post by Aptenodytes » Tue Jan 01, 2013 3:22 pm

Don't use commas in a date field. Dashes or slashes.

whomever
Posts: 749
Joined: Sat Apr 21, 2012 5:21 pm

Re: XIRR question - beginner

Post by whomever » Tue Jan 01, 2013 3:55 pm

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 non-adjacent 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.

FinancialDave
Posts: 1547
Joined: Thu May 26, 2011 9:36 pm

Re: XIRR question - beginner

Post by FinancialDave » Tue Jan 01, 2013 4:20 pm

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
I love simulated data. It turns the impossible into the possible!

2retire
Posts: 367
Joined: Wed Jun 13, 2012 9:00 am

Re: XIRR question - beginner

Post by 2retire » Tue Jan 01, 2013 4:34 pm

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 non-adjacent cells?
I don't think you can do that. Can you give an example of what your spreadsheet looks like?

lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

Re: XIRR question - beginner

Post by lethean46 » Tue Jan 01, 2013 4:40 pm

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

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 4:40 pm

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.
Just to double check, I moved to another computer where I have openoffice.

Code: Select all

01/01/12	-1000
12/31/12	 1100
	0.10
Negative starting values work in openoffice too.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 4:50 pm

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.

whomever
Posts: 749
Joined: Sat Apr 21, 2012 5:21 pm

Re: XIRR question - beginner

Post by whomever » Tue Jan 01, 2013 5:02 pm

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().

FinancialDave
Posts: 1547
Joined: Thu May 26, 2011 9:36 pm

Re: XIRR question - beginner

Post by FinancialDave » Tue Jan 01, 2013 6:15 pm

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!

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: XIRR question - beginner

Post by sscritic » Tue Jan 01, 2013 6:42 pm

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.
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 non-interest 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.

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.

rr2
Posts: 1023
Joined: Wed Nov 19, 2008 10:04 pm

Re: XIRR question - beginner

Post by rr2 » Tue Jan 01, 2013 6:53 pm

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
Great. What made it work?

User avatar
runner9
Posts: 2014
Joined: Tue Aug 02, 2011 8:49 pm
Location: Ohio

Re: XIRR question - beginner

Post by runner9 » Tue Jan 01, 2013 6:57 pm

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.

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Tue Jan 01, 2013 7:02 pm

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).
sscritic wrote:
lethean46 wrote:Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
I think 10% has to be 0.1
Nope: Excel knows that 10% is 0.1.
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.
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:your range does need a starting balance on the first line and ending balance (negative) on the last line of the range.
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).
Simplify the complicated side; don't complify the simplicated side.

FinancialDave
Posts: 1547
Joined: Thu May 26, 2011 9:36 pm

Re: XIRR question - beginner

Post by FinancialDave » Tue Jan 01, 2013 7:35 pm

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).
sscritic wrote:
lethean46 wrote:Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
I think 10% has to be 0.1
Nope: Excel knows that 10% is 0.1.
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.
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:your range does need a starting balance on the first line and ending balance (negative) on the last line of the range.
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).

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!

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Tue Jan 01, 2013 7:57 pm

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.
I agree . . . in this case. As I have in my signature, there's no reason to complify the simplicated side.
Simplify the complicated side; don't complify the simplicated side.

Bungo
Posts: 877
Joined: Wed Sep 28, 2011 11:28 am

Re: XIRR question - beginner

Post by Bungo » Tue Jan 01, 2013 8:06 pm

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 non-adjacent 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.
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.

User avatar
Oicuryy
Posts: 1209
Joined: Thu Feb 22, 2007 10:29 pm

Re: XIRR question - beginner

Post by Oicuryy » Tue Jan 01, 2013 8:27 pm

whomever 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().
The syntax should be
=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

User avatar
tetractys
Posts: 4596
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Re: XIRR question - beginner

Post by tetractys » Tue Jan 01, 2013 8:38 pm

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
You've got it backwards. Money going from you to your account should be negative. The final balance should be positive--that way you have something to retire on :D . Nevertheless, the function works either way.

For the quarter you can input a withdrawal and a deposit on the date to make the XIRR. -- Tet

User avatar
tetractys
Posts: 4596
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Re: XIRR question - beginner

Post by tetractys » Tue Jan 01, 2013 8:43 pm

lethean46 wrote:Lastly, I clicked on Cell A15 and manually entered: =XIRR(A2:A13,B2:B13,10%). Then hit Enter. The Cell reads: #VALUE.
Replace 10% with 0. If that doesn't work try 1. Like this: <=XIRR(F374:F375,B374:B375,0)> -- Tet

gkaplan
Posts: 7034
Joined: Sat Mar 03, 2007 8:34 pm
Location: Portland, Oregon

Re: XIRR question - beginner

Post by gkaplan » Tue Jan 01, 2013 8:49 pm

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 jon-nyc 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 year-to-date and one-year returns differ slightly, as do my three-year and five-year returns to a lesser extent. (My spreadsheet only goes back about seven years, so I don't have ten-year returns.)

Too bad, I'll forget all this in 2016.
Gordon

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Tue Jan 01, 2013 8:55 pm

tetractys wrote:
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
You've got it backwards. Money going from you to your account should be negative. The final balance should be positive--that way you have something to retire on :D . Nevertheless, the function works either way.

For the quarter you can input a withdrawal and a deposit on the date to make the XIRR. -- Tet
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.
Simplify the complicated side; don't complify the simplicated side.

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Tue Jan 01, 2013 8:56 pm

gkaplan wrote:Too bad, I'll forget all this in 2016.
There are ways to ensure that this doesn't happen. I cannot (in good conscience) recommend some of them.

;)
Simplify the complicated side; don't complify the simplicated side.

User avatar
tetractys
Posts: 4596
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Re: XIRR question - beginner

Post by tetractys » Tue Jan 01, 2013 8:57 pm

magician wrote:
tetractys wrote:
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
You've got it backwards. Money going from you to your account should be negative. The final balance should be positive--that way you have something to retire on :D . Nevertheless, the function works either way.

For the quarter you can input a withdrawal and a deposit on the date to make the XIRR. -- Tet
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.
Try to read between the lines; there's no anality in my answer, only some frivolity that agrees with you. -- Tet

whomever
Posts: 749
Joined: Sat Apr 21, 2012 5:21 pm

Re: XIRR question - beginner

Post by whomever » Tue Jan 01, 2013 9:32 pm

@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).

lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

Re: XIRR question - beginner

Post by lethean46 » Tue Jan 01, 2013 10:21 pm

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

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Tue Jan 01, 2013 10:44 pm

tetractys wrote:
magician wrote:
tetractys wrote:
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
You've got it backwards. Money going from you to your account should be negative. The final balance should be positive--that way you have something to retire on :D . Nevertheless, the function works either way.

For the quarter you can input a withdrawal and a deposit on the date to make the XIRR. -- Tet
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.
Try to read between the lines; there's no anality in my answer, only some frivolity that agrees with you. -- Tet
Sorry. When I read "You've got it backwards" it sounded as though you meant . . . well . . . you know.
Simplify the complicated side; don't complify the simplicated side.

lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

Re: XIRR question - beginner

Post by lethean46 » Tue Jan 01, 2013 11:00 pm

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

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Tue Jan 01, 2013 11:08 pm

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?
Make a new column totaling the the entries for the four columns, then use XIRR on that column.
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.
Just use the XIRR function on all of the data (2010, 2011, and 2012, together).
lethean46 wrote:Thanks.
My pleasure.
Simplify the complicated side; don't complify the simplicated side.

lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

Re: XIRR question - beginner

Post by lethean46 » Wed Jan 02, 2013 1:49 am

[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

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Wed Jan 02, 2013 2:01 am

lethean46 wrote:
magician wrote:
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?
Make a new column totaling the the entries for the four columns, then use XIRR on that column.
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.
Just use the XIRR function on all of the data (2010, 2011, and 2012, together).
lethean46 wrote:Thanks.
My pleasure.
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'll shoot you a PM.
Simplify the complicated side; don't complify the simplicated side.

lethean46
Posts: 282
Joined: Sat Nov 17, 2007 10:52 pm
Location: OH

Re: XIRR question - beginner

Post by lethean46 » Wed Jan 02, 2013 2:29 am

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

JustinR
Posts: 566
Joined: Tue Apr 27, 2010 11:43 pm

Re: XIRR question - beginner

Post by JustinR » Wed Jan 02, 2013 5:49 am

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.

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Wed Jan 02, 2013 11:39 am

JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
I don't.

What else would you have it do?
Simplify the complicated side; don't complify the simplicated side.

User avatar
Aptenodytes
Posts: 3751
Joined: Tue Feb 08, 2011 8:39 pm

Re: XIRR question - beginner

Post by Aptenodytes » Wed Jan 02, 2013 11:58 am

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

Just start with your first positive balance.

dbr
Posts: 26579
Joined: Sun Mar 04, 2007 9:50 am

Re: XIRR question - beginner

Post by dbr » Wed Jan 02, 2013 12:02 pm

magician wrote:
JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
I don't.

What else would you have it do?
I don't either, but one might prefer an error response such as num# rather than an actual number.

User avatar
magician
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: XIRR question - beginner

Post by magician » Wed Jan 02, 2013 12:11 pm

dbr wrote:
magician wrote:
JustinR wrote:Does anyone know why, if you start with a balance of 0, XIRR gives you 0% no matter what you do?
I don't.

What else would you have it do?
I don't either, but one might prefer an error response such as num# rather than an actual number.
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 improvement
Simplify the complicated side; don't complify the simplicated side.

Post Reply