IRR error

[Brian]

Here is the question below I am working on in a Corporate Finance class.

(Default risk) You buy a very risky bond that promises a 9.5% coupon and
return of the $1,000 principal in 10 years. You pay only $500 for the bond.

a. You receive the coupon payments for three years and the bond defaults.
After liquidating the firm, the bondholders receive a distribution of $150
per bond at the end of 3.5 years. What is the realized return on your
investment?

Years Cashflow
0 500
0.5 0
1 95
1.5 0
2 95
2.5 0
3 95
3.5 150
When I do the IRR on the cash flow, I get the "NUM" error.. can somone help.

 

[Joe User]

When I do the IRR on the cash flow, I get the "NUM" error

Learn to use Help. Click on Excel Help and type "irr function", then click
on "IRR worksheet function".

As the Help page explains (emphasis added):

"The internal rate of return is the interest rate received for an investment
consisting of payments (__negative__ values) and income (__positive__ values)
that occur at regular periods."

That is, cash flows must be signed. So the 500 at time 0 should be -500.

However, keep in mind that the Excel IRR function returns the __periodic__
IRR -- in your case, the semiannual IRR. Since investment returns are
usually expressed as an annual rate, you will need to annualize the periodic
IRR.

That is accomplished one of two ways:

1. =2*IRR(B1:B8)

2. =(1+IRR(B1:B8))^2 - 1

Personally, I prefer #2. It is also consistent with the Excel XIRR
function, which you do not need in this case.

However, you will find that both methods are used equally. You should ask
your instructor which way he/she wants it done, if your text does not offer a
method.

Finally, there are other instances when the Excel IRR function returns the
#NUM error (and sometimes the #DIV/0 error). If you have structured the
problem correctly (namely, correctly signed cash flows), the problem be that
you need to give the Excel IRR function some help. Read about the "guess"
parameter in the Help page.

But that problem does not arise in this case.


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

 

[Dana DeLouis]

Here is the question below I am working on in a Corporate Finance class.

(Default risk) You buy a very risky bond that promises a 9.5% coupon and
return of the $1,000 principal in 10 years. You pay only $500 for the bond.

a. You receive the coupon payments for three years and the bond defaults.
After liquidating the firm, the bondholders receive a distribution of $150
per bond at the end of 3.5 years. What is the realized return on your
investment?

Years Cashflow
0 500
0.5 0
1 95
1.5 0
2 95
2.5 0
3 95
3.5 150
When I do the IRR on the cash flow, I get the "NUM" error.. can somone help.

Hi. If I am not mistaken, all your cash flows are of the same sign, and
thus causing the error.

You pay only $500 for the bond.

You pay out (-500) and take in (+95)

Years Cashflow
0 -500
0.5 0
1 95
....etc

Since you don't get back what you payed out, the return should be
negative. I get - 2.7%
This is for half a year, so I believe the yearly rate is twice this, or
about -5.4%

Hope I got this correct. :>)

= = = = = = =
HTH :>)
Dana DeLouis