Results 1 to 15 of 33

20110518, 01:45 #1
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
How to determine investment value with IRR?
I am interested in finding I believe the correct term is the Internal Rate of Return of an investment that I have added to over different intervals.
example I start in say Jan 1 2009 with 1000.00 and at different times I have added additional principal say 2000 march 1 2009, 1000 april 1 2010....
and now May 1 2011 after 10 additions of principal it is now worth 12,000. (the 1000 to 12000 is 11000 increase but minus all my additional principal deposits over the years)
What is the IRR of this 1000 investment that is now worth 12,000 but have added over the years a total of say 10,000 in principal payments
..
Is there a spreadsheet or a simple way of using the IRR to figure this out.
I am only interested in the return at todays date (current value with all the additions of principal) not at each step of the investment.
Thank you

20110518, 06:16 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Did you check out the HELP on the IRR function in XL?
In addition to the investments you have added to it (which are negative amounts) you need to know the returns (dividends/interest) that you receive (positive amounts).
Steve

20110518, 08:44 #3
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
Yes, could not really translate the help to my needs.
No dividends or interest received. Just a matter of increase value. basically a non dividend paying stock.
But aren't dates of additional added capital value important, if capital added late than irr should be higher as bigger result on smaller inital capital and yet I do not see any date parts.
Just confused
Jr

20110518, 10:43 #4
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hi Jr  You may want to take a look at the XIRR function....
XIRR function
HTH

The Following User Says Thank You to tfspry For This Useful Post:
jrklein (20110518)

20110518, 10:50 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I will preface this with the comment that I am not an accountant, nor do I try to be. I am a scientist with some knowledge of excel.
Perhaps I don't understand your situation, but If the only value to the stock is the money you put into it, I don't see where there is any "return" so the internal rate of return seems meaningless. Whether On Jan 1 you pay $12,000 or you pay $1000/month for a year, the value after a year is still $12,000 if you get no interest or payment (ie you get no return) from the stock. For an IRR calc you must have some negative (investment) and some return (positive) sinice the IRR is essentially the rate that you would have a NPV going from the negative value (investment) to 0
There is a loss of potential interest you could have received on the initial $12,000 if you had invested it elsewhere (even in a savings account) and made some interest on it. But in this case, I would think comparing the NPVs of the 2 scenarios would be what you wanted not calculating an IRR.
The IRR does have an array of incremental value. The initial value is generally your investment (negative) and the rest are the incremental returns for each period (monthly, yearly). The periods must be regular.
Perhaps I do not understand your scenario. Could you describe it in more detail?
Steve

20110518, 10:50 #6
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
Hi HTH
thank you this seems to make more sense, Just to clarify, even if I put in 10,000 into a bank account jan 1 2009,it is a negative number?, and then i put in 1000 into the same account March 1 2010 and 2000 April 1 2011, then these are both positive numbers.
The total amount invested would be 12000 in this example but 10,000 would be negative and 1000 would be positive and 1000 would be positive to get the XIRR? Just seems incosistent and I don't understand the math well enough
Also where do you put the current value of say 15000, (12000 of principal and now 3000 in increase in value) in the spreadsheet to make it work
JRLast edited by jrklein; 20110518 at 10:54.

20110518, 10:57 #7
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hi Jr  All amounts you put in (outlays) are negatives, amounts you receive are positives. In your example, you may want to place a value on all of the accumulated shares on a certain date and use that value as a positive number.
HTH means "Hope That Helps" !!!

The Following User Says Thank You to tfspry For This Useful Post:
jrklein (20110518)

20110518, 10:59 #8
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
Sorry I was not clear
lets take a bank account that i put in 10,000 jan 1 2009, and then i put in 1000 into the same account March 1 2010 and 2000 April 1 2011, The total amount invested would be 12000 in this example but now the value as of May 1 2011 is 15000, (12000 of principal and now 3000 in increase in value for whatever reason interest dividend, or assets held in account are just worth more if they are stock) how do I calculate a true rate of return. A simple rate of return does not allow for the extra principal put in at different times. For example if all the extra capital was put in at the end then the return would be much higher than if all the capital went in at the beginning. Just trying to get a better handle on a true rate of return where additional money / principal has been added at odd times between the original opening of the account and say today. JR

20110518, 11:14 #9
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
10,000
1Jan091,0001Mar091,00030Oct101,20015Feb1115,0001May110.06593
sorry this just does not seem right 10k is starting deposit, all others are additional deposits, and 15k is the final value, it looks like 15 k is another deposit, no way to differentitate between deposits and final value unless make 15k a positive value. then it shows a 6% return. does that seem roughly accurate??
if i make it all 0's for extra deposits just to stress the formula then it shows a 19% irr which i think makes sense. just verifying?
10,000
1Jan0901Mar09030Oct10015Feb1115,0001May110.19019

20110518, 12:05 #10
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
actually I think I have the hang of it and seems to work fine, Thank you
IS THERE A BETTER estimate or internal average of the amount of principal that is there each year?? Like an IRR on principal.
is it accurate to say you have average of 10k principal if the extra money is put in at the end . if you do an average of principal over 3 years not really accurate, is there more of a time weighted principal equation to see really what your "working principal" is each year to have a better understand of what happened now that one has a better understanding of the true return...
Thanks
JR

20110518, 12:20 #11
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hi JR  You may want to look at this video for some unusual results that can occur with XIRR.

The Following User Says Thank You to tfspry For This Useful Post:
jrklein (20110518)

20110518, 12:54 #12
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
very interesting, but he is saying the irr is not that reliable either and irr can only be used on a regular periodic basis ie additions each month.
I enjoyed watching it thanks
jr

20110518, 17:54 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
What that 6.5% IRR means in your example calculation is:
You initially invest $10,000 on Jan 1, 2009. On Mar 1, 2009, you have made no return, but invest another $1,000. On Oct 30, 2010, your investment still has made no returns, but you invest another $1,000. Feb 12, 2011 there is still no return but another investment of $1,200 is made. So at this time, you have spent $13,200 and have received nothing back. Then on May 1, 2011 the investment finally gives you a payout of $15,000.
Is that the situation?
Steve
PS. Thinking about this last night, a different way of looking at this, would be to assume a bank acct paying 6.59% ann interest and compounded annually. You have $10,000 in the account for 2.33 yrs (1/1/2009 to 5/1/2011) so its value with interest is $11,603. You have $1000 in it for 2.17 yrs (3/1/2009 to 5/1/2011) so its value with interest is $1,148. You have $1000 in it for 0.50 yrs (10/30/2010 to 5/1/2011) so its value with interest is $1,033. And you have $1200 in it for 0.21 yrs (2/15/2011 to 5/1/2011) so its value with interest is $1,216. And the total is $15,000. Thus the XIRR calc becomes a way to look at the average annual interest rate with annual compounding. Perhaps that is how you are trying to see the situation?
[Using Goal seek, the Ann Percentage rate can be calculated to be 6.40% if the bank would compound monthly (which I think is more typical).]Last edited by sdckapr; 20110519 at 06:38. Reason: Added a PS

The Following User Says Thank You to sdckapr For This Useful Post:
jrklein (20110519)

20110518, 18:49 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
IRR calculates based on regular intervals. The XIRR can be used with irregular intervals. What the video demonstrates is that the XIRR calc procedure is flawed. It seems to me that the IRR method is also flawed by his values, since it indicates a better return for the series that lost more money. Even from the standpoint that they are identical until the final payment. I would rank them Col B>C>D>E>F but based on the IRR row it gives: Col B>F>E>D>C. How would you rank the videos scenario?
Steve

20110519, 17:41 #15
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
sorry I have been flying all day, not certain what you are asking wrt to ranking/