Excel formula

[MAK]

What is the following formula trying to do? What do the functions mean, and
the ^ symbol?

=IF(ISNUMBER((1+IRR($F$47:BA47,0.01))^12-1),(1+IRR($F$47:BA47,0.01))^12-1,"n/a")

 

[Joe User]

What is the following formula trying to do?
What do the functions mean, and the ^ symbol?

The "^" (caret) operator is exponentiation. x^y is x raised to the power y;
that is x multiplied by itself y times.

The expression (1+IRR(...))^12-1 is attempting to annualize a monthly IRR.

The problem is: Excel IRR() does not always return a valid number.
ISNUMBER(...) is attempting to detect that.

So the IF() expression says: if IRR() returns a valid number, return the
annualized IRR; otherwise, return the string "n/a".

FYI, the IF() expression is unnecessarily complicated. At a minimum, it
should be rewritten:

=IF(ISNUMBER(IRR($F$47:BA47,0.01)), (1+IRR($F$47:BA47,0.01))^12-1, "n/a")


----- original message -----

 

[David Biddulph]

The ^ symbol is the exponentiation operator, or raising a number to a power.
If IRR is the rate of return for 1 month, (1+IRR)^12-1 [or
POWER(1+IRR,12)-1] will be the rate of return for 1 year if the return is
compounded monthly.
IRR, ISNUMBER, and IF are all standard Excel functions, details of which are
included in Excel help.