IRR Syntax Help

Question

Level 1

4 points

IRR Syntax Help

Hi all,

I have a spreadsheet with a 1 column that represents the hypothetical cashflows of an investment, and another column that represents the hypothetical "cashout" value of the same investment on a year over year basis. In reality the spreadsheet to calculate the IRR if a person were to cashout their investment in any given year. essentially it will be an IRR of the stream of hypothetical CFs, and in the final year to factor in the "cashout" amount into the final payment. The issue I am having is with the syntax, because the cashout amount is found in a separate column, and does not fit into the array form the IrR function seems to need.

For example. I want to know the IRR after 5 years.

What I would to is IRR(A1:A5;A6+B6)

Where A6+B6 = cashflow + cashout amount in that year.

Anyone know of a way to format the IRR function to allow something like this?

Thanks for reading.

Posted on Jul 26, 2013 7:59 AM

Reply

Answer 1

Barry

Level 9

60,035 points

Jul 26, 2013 9:25 AM in response to seanocon

Hi Sean,

Your exmple does not fit the syntax of IRR(flows-range;estimate) as described in the iWork Formulas and Functions User Guide:

The IRR function returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals.

IRR(flows-range, estimate)

Â flows-range: A collection that contains the cash flow values. flows-range is a collection containing number values. Income (a cash inflow) is specified as a positive number, and an expenditure (a cash outflow) is specified as a negative number. There must be at least one positive and one negative value included within the collection. Cash flows must be specified in chronological order and equally spaced in time (for example, each month). If a period does not have a cash flow, use 0 for that period.
Â estimate: An optional argument specifying the initial estimate for the rate of return. estimate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). If omitted, 10% is assumed. If the default value does not result in a solution, initially try a larger positive value. If this does not result in an outcome, try a small negative value. The minimum value allowed is –1.

Note that the second argument, estimate, is an "initial estimate of the rate of return" and is a percentage, not a cash value.

The iWork Formulas and Functions User Guide is avaliable for download via the Help menu in Numbers.

Regards,

Barry

Reply

Answer 2

Jul 27, 2013 8:48 AM in response to seanocon

Sean,

Here's a guess at what you want...

In this example, I have hidden Column C since this first period will return an error, having only one transaction.

Jerry

Reply

Answer 3

Aug 7, 2013 8:59 AM in response to seanocon

If Excel and iWork Numbers are compatible then the following should work

=IRR( (A1:A6 , B6) )

The comma operator in Excel creates a union of two cell ranges and notice the parenthesis aroung the union of two cell ranges which if omitted would make B6 a guess rate instead

EDIT

This would merely append B6 to the list of cash flows and I understand you required the last amount to be the sum of A6 and B6 so not sure if the following would work

=IRR( (A1:A5 , A6+B6) )

Message was edited by: FinancialEngineer

Reply

Answer 4

Aug 7, 2013 11:56 AM in response to FinancialEngineer

In Numbers, the comma does not append. In Numbers, the comma separates the arguments of a Function. The second argument in IRR needs to be a rate and it is optional.

Jerry

Reply

Answer 5

Barry

Level 9

60,035 points

Aug 7, 2013 12:26 PM in response to Jerrold Green1

Jerrold Green1 wrote:

In Numbers, the comma does not append. In Numbers, the comma separates the arguments of a Function. The second argument in IRR needs to be a rate and it is optional.

Jerry

Excels' description of the IRR function indicates syntax and arguments are much the same as those for IRR in numbers (see above).

But the extra pair of parentheses in FE's formula gather the range and cell into a list. The (optional) second argument is omitted in both.

The comma in this case would serve the same function as commas in the SUM function:

=SUM(B2:B7,C7)

Unfortunately, it doesn't work in IRR, which returns the error message "The formula contains a list where a single argument is expected."

Here's something that appears to do the job.

Initial investment (negative) and periodic returns in column B

Final payout in C7

Rows summed (B+C) in column D

IRR formula in D1: =IRR(D2:D7)

Note that the second argument has been omitted. You may need to add this estimate of the IRR. If so, the formula will return a 'could not calculate, please change your estimate' (or similar) message.

Regards,

Barry

Reply

Answer 6

Aug 7, 2013 1:21 PM in response to Barry

Barry,

Thanks, I missed the nested parentheses. Same bottom line.

I believe that my post of July 27 does the job too.

Jerry

Reply