IRR error
in [General]
|
Prev: Open a file without using Office Live?
Next: How do I transpose a large amount of data from rows to columns
From: Brian on 10 Mar 2010 01:14 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.
From: Joe User on 10 Mar 2010 02:36 "Brian" wrote: > 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 ----- "Brian" wrote: > 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.
From: Dana DeLouis on 10 Mar 2010 02:41
On 3/10/2010 1:14 AM, Brian wrote: > 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 |