How to determine investment value with IRR?

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Hi Jr – You may want to take a look at the XIRR function….
XIRR function

HTH

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
JR

Reply | Quote

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

Reply | Quote

-10,000

1-Jan-09-1,0001-Mar-09-1,00030-Oct-10-1,20015-Feb-1115,0001-May-11 0.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

1-Jan-0901-Mar-09030-Oct-10015-Feb-1115,0001-May-11 0.19019

Reply | Quote

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

Reply | Quote

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

Reply | Quote

sorry I have been flying all day. Yes your example and analysis is dead on. I was looking for a way for a way to get some handle on the return and your apporach is correct. Not certain what you mean by “using goal see” is this another formula to plug into? JR

Reply | Quote

yes you have it exactly. Not certain how to use goal seek to make the xirr compound monthly
Thank you
JR

Reply | Quote

See my example file (attached).

Dates are in Col A and amounts in Col B. Col C has the value of each monthly with monthly compounding based on the Ann interest rate in C1 each from their start date to the final date (in A10). [In C4 you have:
=$B4*(1+C$1/12)^(($A$10-$A4)/365*12)

C10 has the sum of all the values + interest. Use Goal Seek:
Set Cell: C10
to Value: 15000
by changing cell:C1

Excel will vary C1 until 15000 is in C10. This gives an Annual Percentage rate of about 6.40% compounded “monthly” (every 365/12 days).

For annual compounding you can use in C4:
=$B4*(1+C$1)^(($A$10-$A4)/365)

and goal seek will give you 6.59% (which is the value from XIRR) which uses a similar approach, though the values in B are negative and final date and +15000 is part of the calcs

Steve
PS you could set it up to calculate based on set periods of time which is more of what a bank would do, not just 30+ days. You can also calculate based on the time forward in case you don’t have the end time yet. But it this were the case, I would think the bank would be providing you with the actual monthly interest earned instead of a return at the end…

Reply | Quote

Hi JR – You may want to look at this video for some unusual results that can occur with XIRR.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

sorry I have been flying all day, not certain what you are asking wrt to ranking/

Reply | Quote

If you watch the YouTube video that tfspry linked above, there are several columns with the same scenarios but different values in the last period and different IRR calc’d because of that change. Based on the IRRs values (largest to smallest) they would be ranked: Col B>F>E>D>C, but based on the total sum and based on the getting the largest payout overall, I would rank them:Col B>C>D>E>F. Just curious how you would rank them and what that says about your trust in the IRR values as a meaningful measurement…

Steve

Reply | Quote

my apologies, I am travelling overseas I do not have regular access to the computer. Thank you for the file, I have never used goal seek before and this is wonderful. I have to rewatch the utube for the columns as I did it right before the plane and found it qualitatively wonderful but did not analyze it in detail as you had, I still trust IRR unless you push it to unusual scenarious where it breaks down and I as not aware of thsi fault. My thanks again and when I am back will give all this the time it deserves. Again my thanks
JR

Reply | Quote

I still trust IRR unless you push it to unusual scenarious where it breaks down and I as not aware of thsi fault.

It is not so much the “fault” of the XIRR or the IRR function really, but the nature of the example data itself. Typically one expects the NPV curve to decrease as the interest rate goes up from 0%. But in the data set, for many of the columns, the NPV starts out negative at 0% increases and goes to zero as the NPV increases (sometimes at 10, 30%) reaches a maximum NPV and then decreases to reach 0 again at about 400%. The XIRR and IRR sometimes each find a different “crossover” point. [FYI the curves actually have a 3rd point where the NPV is 0 that is around -99%. At near -100% the NPV is very large(division by a very small number) and then drops to below zero, so it can rise as the Interest increases as described earlier, and sometimes this zero is found instead. It seems to me that when the NPV curve is “flatter” and the iterations are not close together enough, the findings jump too far and go from increasing to decreasing slopes and so no answer is found…].

The example data indicates (based on the NPV vs int data, that in some cases is the int rate in a bank is below 10% (or greater than 400%) that it would be better to put the money into a savings acct rather than this investment. In some of the scenarios listed if the interest rate you can get on a bank is <30% it is better to put it into a savings account. That is the problem with not knowing which zero point is hit. One presumes that an IRR of 30% is better than an IRR of 10%, but if this is a rising and not a falling slope to the curve, the exact opposite is true…

From the little I know, using an IRR methodology is flawed more than the IRR (or XIRR) value determination due to the possible nature of the curves. Any time an investment can yield both net positive and negative "returns" in periods, is when the NPV vs interest curves can get "interesting".

Steve
PS: There us a tutorial on the "Perils of the Internal Rate of Return" at http://hspm.sph.sc.edu/courses/econ/invest/invest.html which has some of the various NPV vs interest charts and their interpretation. The last section has the charts similar to those that arise from the youtube examples.

Reply | Quote

Steve
ps Is there a way of determining the average daily balance in a time weighted fashion between the first deposit and the last deposit versus just doing a simple average which does not propertly reflect the different investment period of the different deposits??

Reply | Quote

Is there a way of determining the average daily balance in a time weighted fashion between the first deposit and the last deposit versus just doing a simple average which does not propertly reflect the different investment period of the different deposits??

I can think of ways. Could you describe an example situation? In the example you listed previously, you did not indicate any intermediate returns so the daily average is easy:

You initially invest $10,000 on Jan 1, 2009. On Mar 1, 2009, you have made no return, but invest another $1,000. Thus from Jan to March your average is -$10,000. From March 1, 2009 to Oct 30, 2010, your investment still has made no returns and your average is -$11,000, On Oct 20, 2010 after you you invest another $1,000 until Feb 12, 2011 (when you make another investment) your average is -$12,000 From Feb 12, 2011 (after you invest anothor $1,200 through May 1,2011 your average is -$13,200 On May Then on May 1, 2011 the investment finally gives you a payout of $15,000 and it is now worth +$15,000

If you had put it into a bank account, it would be more complicated as you would have intermediate returns on some day each month in addition to the times you added money. To calculate the daily average would require us knowing the interest rates each period (is it constant or changing each period) and at what days the interest is being added. The 6.59% (or 6.40%) values are only overall estimates since it presumes “fractional compounding” which is not done in practice. The method used in those estimates averages the interest, so does not worry about the average daily investment

Steve

Reply | Quote

Steve
I am impressed, your understanding of this math is so far superior to mine.
What I am interested in is getting a handle on the amount of principal that you are getting interest on.
Lets say it is a non dividend paying stock or a mutual fund just to make it clearer to get my thought across. We have a certain amount at the beginning invested say 10 K over the months years you put in at different non equal intervals say 1K one time 6 k another time. And at the end say 3 years later you have a value for the investment of say 20K
But if all the contributions were put in right at the end of 3 years you might be feel you only were getting the increase on just the beginning working capital of 10 k as most of the principal was put in 2 days before the end. On the other hand all the contributions of principal might group at the beginning and then you would be getting increase in value on all of the principal

I am interested to see what the “time weighted” capital would be. Are you getting 6 % on most or part of the capital, how much capital was getting the benefit from the IRR most of the time was it just 10 k or was it 18 k…… It would not be fair to add up all the principal payments and divide by the number and say this is what the average working capital was over the term of the investment as the curve could be skewed to the front or end…..
Just looking for a better understanding / indication of what is the “average” working capital just like the XIRR gives a better indication of the return over the term of the investment
thanks
jr

Reply | Quote

What I am interested in is getting a handle on the amount of principal that you are getting interest on.
Lets say it is a non dividend paying stock or a mutual fund just to make it clearer to get my thought across.

In this case you are not getting interest, though you could compare this mutual fund scenario with an interest bearing account. If it is non-dividend I presume it non interest as well. To look at this you need the price of the stock over time, what you can buy it for an what you can sell it for. Instead of this new more nebulous example, lets look at the specific one you mentioned earlier:
You initially invest $10,000 on Jan 1, 2009. On Mar 1, 2009 you invest another $1,000. On Oct 30, 2010, your investment another $1,000. On Feb 12, 2011 another investment of $1,200 is made. So at this time, you have spent $13,200. Then on May 1, 2011 you sell the fund for $15,000.

On those dates how much was the stock? If for example you always bought it for $10/share, you could have sold it for $11.36 to get the $15,000. [In this case with 20/20 hindsight, you should have put the money into a some other account, earned interest, and then before the stock rose in price, took the money from savings bought it for $10 and sold it for $11.36 and you have made money from the interest as well]. But that is not the only scenario (there are an infinite number of them). You could have bought $10,000 worth for $8.47 and all the rest at $10 and sold them all in may for $10 and still be at $15,000: only making money on the $10,000 investment. Or you could have bought the stock for $10/share for all dates, but bought on the $1000 dates at $3.57/share and sold it for $10/share and ended up with $15000, or paid $10 / share for all but the $1,200 was bought at $4/share and then sold it for $10/share to be at $15,000. So in each of those cases a different purchase accounted for all the profit.

But as mentioned earlier, you could have invested it all in a bank account making 6.59% (the IRR) and been in the same situation.

It doesn’t have to even be a mutual fund or interest-bearing account. The same example works in another example. Assume it is late 2008 and you are commissioned to build a machine for someone. They will pay you $15,000 on May 1, 2011 for delivery of the machine. On Jan 1, 2009 you spend $10,000 on parts and raw materials. You hire a company and will spend $2000 for them to fabricate and put it together. On Mar 1, 2009 you get the parts and material pay half the amount ($1,000) for them to start. On Oct 30, 2010 they finish and you pay them the remaining $1,000. You do your final testing the next few months and on Feb 12, 2011 spend $1,200 for final evaluations, painting, crating, shipping, storage, etc. You deliver it on May 1, 2011 and get paid your $15,000.

I am interested to see what the “time weighted” capital would be. Are you getting 6 % on most or part of the capital, how much capital was getting the benefit from the IRR most of the time was it just 10 k or was it 18 k…… It would not be fair to add up all the principal payments and divide by the number and say this is what the average working capital was over the term of the investment as the curve could be skewed to the front or end…..

That all depends on the details of the investment. Is it in a bank earning some set interest, are you building a machine on commission, are you starting a business with regular expenses and income, are these stocks with some variable dividends and variable value over time. It sounds like you are interested in the last one, and for that you need the daily stock price. Each day you can examine the amount you paid vs the amount the stock is worth. From this difference and time you could even calculate some IRR of what average interest you would have made or you could even compare it to what the equivalent cash would have done in an actual interest bearing account.

Just looking for a better understanding / indication of what is the “average” working capital just like the XIRR gives a better indication of the return over the term of the investment

I am not sure what you mean by ” ‘average’ working capital” though it seems to me that you may be interested in the “Net Present Value” (NPV) since I would interpret “cash flow” as an indication of “working capital” [But as I mentioned before, I am a chemist with excel skills (and a mathematical background), I am not an accountant…]

Steve

Reply | Quote

Hello again JR – A sample file is attached.
It seems the goal is to determine what is the IRR of investments made over time in a non dividend paying stock Vs. investing in a bank savings account. The investment is sometimes called “dollar cost averaging” and focuses on final results rather than results of each individual investment in that stock/mutual fund.

The attached shows in Cell E7 what the expected value would be of the investment stream (Col B) using the XIRR rate calculated in Cell B8. Cols G and H calculate what the return would be from a bank account at that IRR rate (Cell B8). Change the bank rate in Cell F2 to show other possible results.

If the above looks reasonable, “What-If” tables could prove useful for further analyses.

HTH

Reply | Quote

HTH Many thanks, really appreciate this. JR

Reply | Quote

Is there a way of determining the average daily balance in a time weighted fashion between the first deposit and the last deposit

I had a thought for this if you are interested in the average balance of the investment or principal (not the wt’d average capital). If you have the workbook setup like my Avg Interest Goal Seek.xls (attached earlier) with dates in A4:A7, amounts in B4:B7 and a date in A13, you can use the formula:
=SUMPRODUCT(($B$4:$B$7)*(A13-$A$4:$A$7)*(A13>$A$4:$A$7)/(A13-$A$4))

to get the wt average investment at any date. To get the working capital would require additional information on the value of the investment over time and that depends on the investment. If it earns interest/dividends that becomes part of the capital, it is stock with a changing selling price, that is a big part that determines the value over time, if you are investing in some item (building a machine, buying a restaurant, etc) there is the salvage value to consider at any point in time.

Steve

Reply | Quote

Steve Many thanks,
1.I pasted in the formula into your sheet at cell f10 and it yields 0 I am not certain I understand this.

2.I was actually thinking more of a hedge fund where you put in deposits over the years and was trying to get what I thought would be a simple handle on how much “original” money was basically working for you for that time period.
Extreme example to show my point: If you put in 10k at the begining then 10k is working for you of your own money and if you put in 5k additional of your own money say 1 month right before you take a valuation some 2 years later in reality you only had 10k working for you of your original money for all the time of this investment not 15 k or an average of 7.5 k, as an extreme example.

With a hedge fund any possible dividends, interest or increase in value during the investment period is irrelevanat as this is all a ghost till the money comes out. What is only real from most people point of view is how much of your own original money has been put in and is at risk and working for you. Rest of any increase is immaterial as do not know how much you have till you cash out.

What I think has more interest is how much of your own real money has been working for you over these years, in this extreme example it is not 15k but really 10k that you have been earling the XIRR on.

Obviously if you made 20 additions at varying times the actual amount of your own real money workinig is less obvious over the life span as investment could be skewed to the front end (all the deposits at the beginning or to the rear end or equally distributed over the period from beginning till when you cash out) . .

Was hoping to get at a formula or way of seeing how much of one’s capital has really been getting the xirr for the period of the investment retrospectively was it really 10k as most put in at the very end or was it the full amount of whatI put in as all at the beginning. or was it just some time weighted average that only a computer can properly calculate that the xirr was working on . This would be determined by the first date cell and the last date cell when one decides to liquadate all monies.

I hope this is clear, Thanks
JR

Reply | Quote

1.I pasted in the formula into your sheet at cell f10 and it yields 0 I am not certain I understand this.

Did you put a date of interest in A13?

As to rest, when I get a chance I will respond. I must go to work now…

Steve

Reply | Quote

Perhaps this is something you had in mind…

It has the Principal over time, the average principal, and it also has the Princ+Cmpd Int and avg P+I. The interest used was the XIRR value calculated.

I am still not sure what you interested in with the fund, since it does not have any value until the end. When and what contributes to the value, depends on (as mentioned earlier) how the value at a particular time is calculated. This essentially is how much can you get at a particular time if you were to liquidate it. With stocks whose values change over time (again as mentioned earlier) you may find that all the profits may come from just one contribution and others may be a loss or break even. To calculate this you would need to provide us more information.

Steve

Reply | Quote

Steve Perfect THanks, Just looking for what the time weighted average is for a given date that I give to the spreadsheet. This works great. Gives me a feeling of how much of my own principal has been working for me at the xirr with a time weighted average.
JR

Reply | Quote

Glad I could help

Steve

Reply | Quote