investment return calculations
kjk
Does anybody know how to calculate (or where I might find a calculator
for) the overall annual percentage yield (APY) for a series of
investments with different starting times, different lengths, and
different amounts invested? For example:
1) $10K on January 1 which grew to 13K in 8 months
2) $16K on February 1 which grew to 18K in 9 months
3) $14K on March 1 which grew to 18K in two months
With the use of a simple financial calculator, it's easy to calculate
the APY on each of these investments, but how do I calculate the
overall APY . . . the average APY that I'm achieving by being an
investor. This would have to incorporate the different amounts, time
periods, and profit percentages into one annual percentage number. Is
this possible? Is there a specific name for this type of calculation?
Any advice would be much appreciated. Thanks.
Ken
Rich Carreiro
> investor. This would have to incorporate the different amounts, time
> periods, and profit percentages into one annual percentage number. Is
> this possible? Is there a specific name for this type of calculation?
Internal Rate of Return (IRR)
A financial calculator probably has it.
Excel certainly does (though you may need
to install the Analysis Toolpak).
--
Rich Carreiro rlc...@animato.arlington.ma.us
John
"kjk" <k...@usa.com> wrote in message
news:3d39cd8...@netnews.voicenet.com...
You forgot to mention the 40k you had invested in May which is worth 30k now
;-). I have always found the trick to answering these questions is asking
why/what you really want to know, money in the pocket is all that really
counts not APY.
kjk
<rlc...@animato.arlington.ma.us> wrote:
>Internal Rate of Return (IRR)
Rich,
Thanks for the quick response. You gave me exactly what to search
for, and I spent a little time searching. In case anybody is
interested, here are a few things I found that were helpful to me:
The Investment FAQ - How to Compute IRR
http://invest-faq.com/articles/analy-int-rate-return.html
The Investment FAQ - Investment Software
http://invest-faq.com/links/software.html
Online IRR Calculator
http://www.clarologic.com/weblogin/LoginForm.jsp
Ken
Surendar Jeyadev
Rich Carreiro <rlc...@animato.arlington.ma.us> wrote:
>k...@usa.com (kjk) writes:
>
>> investor. This would have to incorporate the different amounts, time
>> periods, and profit percentages into one annual percentage number. Is
>> this possible? Is there a specific name for this type of calculation?
>
>Internal Rate of Return (IRR)
I would think so, too, but the original poster did not specify a "Present
Value", which is critical in calculations of the IRR. In fact the times
of investment and the durations do not seem to add up! Wonder what he
really wants.
--
Surendar Jeyadev jey...@wrc.xerox.bounceback.com
Remove 'bounceback' for email address
Surendar Jeyadev
Surendar Jeyadev <jey...@wrc.xerox.bounceback.com> wrote:
>In article <m3ofd28...@animato.animato.arlington.ma.us>,
>Rich Carreiro <rlc...@animato.arlington.ma.us> wrote:
>>k...@usa.com (kjk) writes:
>>
>>> investor. This would have to incorporate the different amounts, time
>>> periods, and profit percentages into one annual percentage number. Is
>>> this possible? Is there a specific name for this type of calculation?
>>
>>Internal Rate of Return (IRR)
>
>I would think so, too, but the original poster did not specify a "Present
>Value", which is critical in calculations of the IRR. In fact the times
>of investment and the durations do not seem to add up! Wonder what he
>really wants.
Ooops! Forgot to add this:
>>> 1) $10K on January 1 which grew to 13K in 8 months
>>> 2) $16K on February 1 which grew to 18K in 9 months
>>> 3) $14K on March 1 which grew to 18K in two months
Now, is there a final "time", when it takes 8 months fron 1 Jan,
9 months from 1 Feb and 2 months from 1 Mar?
Jonathan Miller
Not necessarily. It could be that investment 3 was liquidated on May 1,
investment 2 on Nov 1, and investment 1 on Sep 1. Financial
consultants, investment analysts, and business managers seem to like to
ask the question "what if we had stayed with" (or "converted to").
In real life, to analyze this, you have to know what is happening with
the cash excess (deficiency). After all, you have to borrow or simply
not invest elsewhere the cash used to make this investment.
Jon Miller
kjk
<jonandma...@comcast.net> wrote:
>>> 1) $10K on January 1 which grew to 13K in 8 months
>>> 2) $16K on February 1 which grew to 18K in 9 months
>>> 3) $14K on March 1 which grew to 18K in two months
>>> incorporate the different amounts, time periods, and profit
>>> percentages into one annual percentage number. Is
>>> this possible?
>> Now, is there a final "time", when it takes 8 months fron 1 Jan,
>> 9 months from 1 Feb and 2 months from 1 Mar?
>Not necessarily. It could be that investment 3 was liquidated on May 1,
>investment 2 on Nov 1, and investment 1 on Sep 1.
That's right. The number of months that I gave in the illustration
for each investment gives the point at which it is closed. That's
exactly what happens in real life. You make a purchase at the time
you believe it's a good buy, and sell it when you think it's time to
take your profit. You have a different number of open positions at
different times, each one with a different purchase date, a different
liquidation date, and a different holding period. The question is how
do we take a look at all our past positions, which are now closed, and
derive a correct APY . . . one number which represents the result of
our entire past investment history.
> In real life, to analyze this, you have to know what is happening with
> the cash excess (deficiency). After all, you have to borrow or simply
> not invest elsewhere the cash used to make this investment.
Including the excess cash won't do what I was looking for. If we
include the excess cash, the APY that we come up with is really the
APY on all the funds available for investment, rather than just the
actual past investments.
Ken
Jonathan Miller
Cash and cash-equivalents are investments.
The IRR of anything is the rate that makes the value at time 0 equal to
the discounted present value of the cash flows (assuming liquidation at
the end). The IRR function in any spreadsheet and most financial
calculators do this.
Jon Miller
kjk
<jonandma...@comcast.net> wrote:
>>Including the excess cash won't do what I was looking for. If we
>>include the excess cash, the APY that we come up with is really the
>>APY on all the funds available for investment, rather than just the
>>actual past investments.
>>
>Cash and cash-equivalents are investments.
Only if the cash remains in the account. If we're approaching this
from the point of view of calculating the IRR of an account, and the
cash is immediately withdrawn from the account upon closing a
position, then for account purposes it doesn't exist. Is there a
mathematial way of including these immediate withdrawals in the
calculation, thereby getting the overall IRR of only the past stock
positions?
Ken
David Ziskind
<3d39cd8...@netnews.voicenet.com> as to the “Internal Rate of
Return” (IRR) calculation. The investment example posed was [partial
quote]:
>
> 1) $10K on January 1 which grew to 13K in 8 months
> 2) $16K on February 1 which grew to 18K in 9 months
> 3) $14K on March 1 which grew to 18K in two months
>
This reply post should be viewed with a mono-spaced font.
It is always possible to calculate the IRR provided there is some
definite rule that identifies what amounts enter or leave the
“investment account” (and when they do so). In the given example, 1, 2,
and 3 identify three "positive" contributions to the investment account
($10K added on Jan 1, $16K added on Feb 1, and $14K added on March 1).
Also, from the way the example is stated, we will assume that there were
additionally three "negative" contributions to the investment account
as follows:
4) $13K was withdrawn on Sept 1,
5) $18K was withdrawn on Nov 1, and
6) $18K was withdrawn on May 1.
In this case, therefore, there were six “contributions”. Positive-
valued contributions are cash additions to the investment account, and
negative-valued contributions are cash withdrawals from the investment
account.
(Comment: Additions of stock by purchase, deletions of stock by sale,
and similar transactions that stay inside the investment account, and
that are eventually flushed out once the account is valued and closed;
are ignored. Such transactions are invisible as far as the IRR
calculation is concerned. Stated another way, the IRR calculation only
schedules those transactions that are transfers between the investor's
"personal wealth account" and his "investment account". If when the
account is closed, there is literally stock still inside the investment
account, one theoretically disposes of it by an imaginary sale
transaction.)
The general formula for calculating the IRR, given that there are N
contributions is:
Sum{i=1 to N: C(i)*exp(r*(t_f - t_i))} = 0 (1)
where: C(i) = the amount of the i th contribution,
t_f = the time of the last contribution (usually a withdrawal)
-- this is the same as the time of closing the investment
account,
t_i = the time of the i th contribution, and
r = the Internal Rate of Return, ie, the IRR.
The theory of the above calculation is that the investment account
starts out at zero, then acquires a non-zero market value by reason of
the first contribution (which is usually a cash addition), then
experiences various cash additions and withdrawals, and finally returns
to a zero market value by reason of the last contribution (which is
usually a cash withdrawal).
In order to adapt formula (1) into a specific calculation for the given
data, the following rules are used:
a) we will assume that the six contributions involved occurred in year
2001;
b) we will start the calendar on January 1, 2000;
c) the unit of measure for money is $1,000.00;
d) the unit of measure for time is 1 year.
The effect of “d” is that a time period such as six months, becomes,
computationally, 0.5 -- ie, the unit of one year is dropped. Likewise,
a rate of return, such as 0.04 per year (ie, 4% per year), becomes,
computationally, 0.04 . Once the IRR is computed, the dimension of
“1/year” is restored -- ie, the actual value of IRR equals: computed
value multiplied by “1/year”. Note that a date such as March 1, 2001
becomes: [(2001 + ((3-1)/12)] - 2000 = 1.167 .
Formula (1) now becomes:
10*exp(r*0.833) + 16*exp(r*0.755) + 14*exp(r*0.666)
- 18*exp(r*0.500) - 13*exp(r*0.166) - 18*exp(r*0.000) = 0 (2)
Formula (2) can be solved by trial-and-error. Basically, all one needs
is a slide rule (or a calculator), paper and pencil, and some time.
Today, the practical approach is to use a spreadsheet -- the calculation
takes a fraction of a second. Setting up the spreadsheet takes a few
minutes the first time, and thereafter, is mostly just a matter of
entering the input data. Note that the spreadsheet, once instructed as
to the constraint involved, will solve the equation by applying the same
trial-and-error method as a human calculator.
The following table and comments illustrates the process for the Corel
suite spreadsheet. (Other spreadsheets will be similar.)
A B C D E F G
1 Internal Rate of Return Calculation — Returned in F7.
2 Enter in Rows 11 & below: Col A=Contrib. Amt., Col B=Contrib.Date.
3 QuickSum Col E (call it "Sum E"), use Tools.NumericTools.SolveFor,
4 to force Sum E ("Formula Cell)" to 0 ("Target Value")
5 by adjusting F7 ("Variable Cell").
6
7 1.833 <= Enter Final Date IRR = 0.402
8
9 Trx Amt Trx Date FDt-TDt GrwthFct Amt*GF
10
11 10 1.000 0.833 1.397697 13.9770
12 16 1.083 0.750 1.351836 21.6294
13 14 1.167 0.666 1.306955 18.2974
14 -18 1.333 0.500 1.222595 -22.0067
15 -13 1.667 0.166 1.069000 -13.8970
16 -18 1.833 0.000 1.000000 -18.0000
17 Sum E= 1.8e-07
The spreadsheet is set up as follows:
1. For subsequent reference, enter the text components of Lines 1 thru
5, 7, and 9. Preferably, enter the legend “Sum E=” in the last row of
Column D.
2. Enter the Final Date into cell A7. This is the date of the last
contribution, ie, the date on which the investment account is closed.
3. Enter the Contribution Amounts in Column A, Rows 11 and below.
4. Enter the Contribution Dates in Column B, Rows 11 and below.
5. In Cell C11, enter the formula: $A$7-B11. Quick Fill the remaining
cells of Column C, down to but excluding the last row. (The formula in
Row 12 will be $A$7-B12, etc.).
6. In Cell D11, enter the formula: (@EXP($F$7*C11)), and Quick Fill as
before.
7. In Cell Ell, enter the formula: (A11*D11), and Quick Fill as before.
8. Quick Sum Column E, from Row 11 down to but excluding the last row.
In the present example, this will insert in cell E17, the formula
@SUM(E11..E16) .
9. Navigate to Tools.NumericTools.SolveFor. Enter Formula Cell as Emm,
where mm is the Last Row Number (in this case, Formula Cell is E17).
Also: enter Target Value as 0, Variable Cell as F7, Max Iterations as
10, and Accuracy as 0.0005 .
10. Click on OK in the SolveFor box, and the spreadsheet will compute
the IRR and the value will appear in Variable Cell, ie, F7. In the
present example, the IRR is 0.402/year (40.2% per year) -- this
unusual result is a consequence of unusual input data.
The IRR can be misleading if large amounts are put into the investment
account for a short period of time, and then rapidly taken out after a
brief, very good (or very bad) investment performance. In that
situation, and lacking knowledge of the amounts under investment, a
false impression can arise when the investment account is relatively
long-lived due to minor amounts that are left in the account.
David Ziskind
zis...@ntplx.net
Bob Harris
> Does anybody know how to calculate (or where I might find a calculator for)
> the overall annual percentage yield (APY) for a series of investments with
> different starting times, different lengths, and different amounts invested?
> For example:
>
> 1) $10K on January 1 which grew to 13K in 8 months
> 2) $16K on February 1 which grew to 18K in 9 months
> 3) $14K on March 1 which grew to 18K in two months
APY is the percentage that the investment would grow in one year. I don't
see how the dates (jan/1, Feb/1, and Mar/1) are relevant, unless we're
supposed to take into account the number of days in each month.
1) $10K growing to $13K in 8 months. In other words, it multiplied itself
by 1.3 in 2/3 of a year. At the same rate over the course of a year, it
would multiply itself by 1.3^(3/2) = 1.48223 (i.e. it would grow to
$1,482.23). This is an APR of 48.223%.
2) In 3/4 of a year, the money has multiplied by 18/16. Over a year it
would multiply by (18/16)^(4/3) = 1.17005, for an APR of 17.005%.
3) (18/14)^6 = 4.51717; the APR is 351.717%. By the way, this was a much
better investment than the other two ;).
If you want to take into account the days in each month, then #1 grew from
$10K to $13K in (assuming not a leap year) 31+28+31+30+31+30+31+31 = 243
days. So the APR is 1.3^(365.25/243) - 1 = .483429 = 48.3429%. I don't
know if a bank would use 365.25 or 365 days for a year in such a
calculation.
The APY formula is fairly simple.
APY = (Af/Ai)^(1/y) - 1
where
Ai is the initial ammount
Af is the final amount
y is the number of years (which might not be a whole number)
The average APY of your three investments, if you had been able to reinvest
in each in turn, could be calculated like this:
Invest $10K, after 8 months it has grown to $13K.
Now invest that $13K, and after 9 more months it has grown to (18/16)*$13K =
$14,625.
Invest the $14,625, and after 2 more months you have $18,803.57.
Now, you started with $10,000, and after 19 months you have $18,803.57.
APY = ($18,803.57/$10,000)^(12/19) - 1 = 49.006%
Earlier this year, the local NFL team was sold for something like $500
million. The previous owner had purchased the time around 1968 for
something like $30 million. While this seems like an outlandish return on
investment, the APY is about 8.625%; not out of line with mortgage rates
during most of those 34 years.
Hope that helps.
Surendar Jeyadev
Then, are we agreeing that the IRR does not capture what you want?
I cannot find a suitable alternative. But, then, even the IRR can
give multiple answers if there were significant in and out flows.
kjk
Excellent!! Some said it couldn't be done, but I believe you've got
it. This looks like what I've been trying to accomplish. Thanks for
the time and effort that you put into your detailed illustration.
Ken
----------------------------------------------------------------
On Tue, 23 Jul 2002 18:10:19 GMT, "David Ziskind" <zis...@ntplx.net>
wrote:
kjk
<NspamI...@MINDnotSPRING.COM> wrote:
> I don't see how the dates (jan/1, Feb/1, and Mar/1) are relevant, unless
> we're supposed to take into account the number of days in each month.
Yes, we are.
>The average APY of your three investments, if you had been able to reinvest
>in each in turn, could be calculated like this:
>Now, you started with $10,000, and after 19 months you have $18,803.57.
>APY = ($18,803.57/$10,000)^(12/19) - 1 = 49.006%
But it didn't take 19 months, it only took 10 . . . the Jan 1
beginning of investment #1 to the Nov 1 exit from investment #2, and
we didn't compound the proceeds of each investment into the other, we
invested each one separately.
See David Ziskind's analysis in this thread. I believe he's got it.
Ken
kjk
(Surendar Jeyadev) wrote:
>Then, are we agreeing that the IRR does not capture what you want?
>I cannot find a suitable alternative.
See David Ziskind's analysis in this discussion in alt.math, I believe
John
> David,
>
> Excellent!! Some said it couldn't be done, but I believe you've got
> it. This looks like what I've been trying to accomplish. Thanks for
> the time and effort that you put into your detailed illustration.
>
> Ken
Note David is using continuously compounded interest rates in his calc's. To
convert to the more normally quoted annual compound rate APR. Use APR =
exp(r)-1 so 40% becomes 49%.
David Ziskind
the article ID <01c23271$6b26e6c0$LocalHost@default>.
First, there is an unbalanced and superfluous parenthesis in the date
conversion example. Thus:
[(2001 + ((3-1)/12)] - 2000 = 1.167
SHOULD BE [2001 + ((3-1)/12)] - 2000 = 1.167
Second, the reply post by John (7/24/02) drew attention to the
difference between Simple Interest Rate versus Annual Percentage Yield
-- a worthwhile distinction. Another variation in reporting occurs
based on whether the Simple Interest Rate is computed based on
continuous compounding, daily compounding, monthly, or other.
--David Ziskind