Rate of Return

  • Thread starter Thread starter Guest
  • Start date Start date
Hi Liz!

If they are evenly spaced (e.g. monthly) you can use the IRR function.

Description: Returns the internal rate of return for a series of cash
flows
Syntax: =IRR(values,guess)

If they are at unequal intervals you need the XIRR function:

Description: Returns the annual effective interest rate for a schedule
of cash flows received at specified dates
Syntax: =XIRR(values,dates,guess)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Liz said:
I need to get a rate of return for a series of uneven payments. How
Click to expand...
do I do t his?
 
Hi Liz!

I ought to add that if payments are evenly spaced and you use IRR.

If you use intervals of a month the rate of return will be the monthly
effective rate. But note that this will not be completely accurate
because months have different lengths.

To convert monthly effective to Annual Nominal compounded monthly you
multiply by 12
To convert monthly effective to Annual Effective use
=(1+MonthlyEffective)^12-1

For accuracy, you really need the XIRR function.

If you need to convert the Annual Effective from the XIRR function to
Annual Nominal Compounded Monthly use:
=((1+AnnEff)^(1/12)-1)*12


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Liz said:
I need to get a rate of return for a series of uneven payments. How
Click to expand...
do I do t his?
 
The payments are evenly spaced, they are the same for 10 years then stop, how would you figure the IRR after the payments stop, like in year 30?
 
Hi Liz!

I need a bit more explanation of what you are doing. You can always
send a workbook direct to me and I'll play with it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Liz said:
The payments are evenly spaced, they are the same for 10 years then
Click to expand...
stop, how would you figure the IRR after the payments stop, like in
year 30?
 
Norman, thanks for your help, but I'm still having trouble because the payments stop and I need to find the IRR for each year after they stop and i can't seem to get it to work.
How frustrating!
 
Hi Liz!

The sort of problem that I'm having is to know whether you are looking
historically at your rate of return at future dates or prospectively
at those dates. But there are a lot more potential problems such as
inflation and capitalization of future rights.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Liz said:
Norman, thanks for your help, but I'm still having trouble because
Click to expand...
the payments stop and I need to find the IRR for each year after they
stop and i can't seem to get it to work.
 
Anonymous said:
*Norman, thanks for your help, but I'm still having trouble becaus
the payments stop and I need to find the IRR for each year after the
stop and i can't seem to get it to work.
How frustrating! *
Click to expand...

If there are no future cash flows the IRR from that point is zero
 
Hi All!

Or if you're calculating a retrospective IRR, the IRR is the same as
that measured to the last cash flow. This is by definition in that we
are finding the rate that equates the sum the PVs of all cash flows to
zero. The PV of zero cash flows is zero. But if you want an intuitive
proof:

=IRR({-100000,50000,50000,50000},0)
Returns: 23.3751928528259%
=IRR({-100000,50000,50000,50000,0,0,0},0)
Returns: 23.3751928528259%

Taking a traditional cash flow of a single negative followed by all
positives. If you want to calculate IRR's at future dates you need a
calculation of the investment's value at that future date. Otherwise
you have an infinite return; all flows are positive.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
millsy > said:
If there are no future cash flows the IRR from that point is zero.
Click to expand...

If there are no future cashflows, then the IRR of future, nonexistent
cashflows is indeterminate. No cashflows is the same as all cashflows 0, and
all cashflows 0 means discounting at any interest rate gives a zero NPV. 5%
of nothing is the same as 200% of nothing.

The IRR of a set of cashflows is always the same at any point in time. If
NPV = Sum(Cashflow / (1 + IRR)^i, i = 0 to N) = 0, then NPV * (1 + other
R)^T = 0 no matter what other R and T happen to be.

If you mean you cease having cashflows from some project after year N but
continue to earn investment income on the retained earnings from the
project, then those subsequent investment returns *are* additional cashflows
that should have been included in the IRR calculation.
 
"The IRR of a set of cashflows is always the same at any point in time. I
NPV = Sum(Cashflow / (1 + IRR)^i, i = 0 to N) = 0, then NPV * (1 + othe
R)^T = 0 no matter what other R and T happen to be.

Harlan, thanks for this definition. Now I have another question concerning IRR a string of cashflows, say 100. Suppose, one has a sequence of 100 cashflows. Can anything be inferred by comparing IRR of flows 1-100 against flow 1-50
 
purpleaces said:
Harlan, thanks for this definition. Now I have another question
concerning IRR a string of cashflows, say 100. Suppose, one has a
sequence of 100 cashflows. Can anything be inferred by comparing
IRR of flows 1-100 against flow 1-50?
Click to expand...

You raise the more interesting question whether IRR ever implies anything
meaningful. When fed bond-like cashflows (initial cash outflow - negative -
followed by all cash inflows - positive), the IRR is analagous to bond
yield. In that situation, IRR is as meaningful as bond yields.

If cashflow 0 is negative and all other cashflows (1..100) positive, then
the IRR of cashflows 0 through 100 will be higher then the IRR of cashflows
0 through 50. That's hardly surprising: more inflows are better than no
more, no matter how long you must wait for them.

Once there are multiple sign changes in the cashflows, all bets are off.
Multiple sign changes give rise to multiple IRRs. While some people may
claim that one of multiple IRRs may be more 'meaningful', there's no
avoiding the fact that since IRRs are nothing more than mathematical results
with no theoretical underpinning, any IRR is as meaninful as any other. All
give a zero NPV for the cashflows in question.

So that's what may be inferred: confirmation of common sense when there are
no sign changes from cashflow 50 on, or nothing necessarily useful if there
are sign changes.
 
Back
Top