Google Answers Logo
View Question
 
Q: MIRR NEED ASAP ( Answered 5 out of 5 stars,   4 Comments )
Question  
Subject: MIRR NEED ASAP
Category: Business and Money > Finance
Asked by: pinkcitylady-ga
List Price: $75.00
Posted: 03 Sep 2006 18:56 PDT
Expires: 03 Oct 2006 18:56 PDT
Question ID: 761971
Create an Excel spreadsheet for a production plant that the company
will lease for 5 years at US$1,500,000 per year; it will cost the firm
US$4,000,000 in capital (straight-line depreciation, 5 year life) in
year 0; it will cost the firm an additional US$150,000 per year after
the new production plant is brought online for other expenses; and it
will generate an incremental revenue of US$3,500,000 per year. Use a
40% tax rate, a 10% cost of capital, and a 12% re-investment rate.
Assume the company will use cash flow to finance the project.
Discuss how the project would fair under hurdle rate scenarios of 10%,
15%, and 20% (based on MIRR).
Answer  
Subject: Re: MIRR NEED ASAP
Answered By: elmarto-ga on 04 Sep 2006 06:58 PDT
Rated:5 out of 5 stars
 
Hello!
In order to solve this, we have to calculate the net operating cash
flows in each of the years that the project will last. In this case,
we're told that the plant will be leased for 5 years.

The initial outlay is clearly $4,000,000.

The cash flow in each of the following years is found in this way. We
know that there will be savings of $3,500,000. Furthermore, there will
be a lease cost of $1,500,000 per year and additional expenses for
$150,000. Therefore, the net income before depreciation and taxes
would be:

NI before dep and taxes = 3500000 - 1500000 - 150000 = $1,850,000

The depreciation would be $800,000 per year (you have to depreciate a
value of $4,000,000 straight-line over 5 years, so the depreciation
per year would be 4000000/5 = 800000). Therefore, the net income
before taxes would be:

NI before taxes = 1850000 - 800000 = $1,050,000

Finally, taxes are 40% of the value we've just found, which amounts to
$420,000. Therefore, net income in years 1 through 5 would be:

NI = 1050000 - 420000 = $630,000

Finally, in order to find the operating cash flows we add the
depreciation to the net income, since the depreciation is not paid to
anyone, so it's not actually a cash outflow. We get:

Operating Cash Flow = 630000 + 800000 = $1,430,000

So that's the operating cash flow in years 1 through 5. We now simply
need to calculate the MIRR. This can be done in Excel. I've prepared
an Excel sheet, which you can find at

http://www.filefactory.com/file/dcdfd2/

As you can see, the MIRR of the project is 18%. In order for the
project to be accepted, its MIRR should be higher than the hurdle
rate. Therefore, we conclude that:

Hurdle Rate 10% : ACCEPT
Hurdle Rate 15% : ACCEPT
Hurdle Rate 20% : REJECT


Google search terms
mirr
://www.google.com/search?hl=es&q=mirr&lr=
"hurdle rate"
://www.google.com/search?hl=es&q=%22hurdle+rate%22&lr=
operating cash flow
://www.google.com/search?hl=es&q=operating+cash+flow&lr=


I hope this helps! If you have any doubt regarding my answer, please
don't hesitate to request clarification before rating it. Otherwise, I
await your rating and final comments.

Best wishes!
elmarto

Request for Answer Clarification by pinkcitylady-ga on 04 Sep 2006 08:43 PDT
How do you get the excel file I clicked on the link but the file
didn't appear to be downloadable.

Request for Answer Clarification by pinkcitylady-ga on 04 Sep 2006 08:50 PDT
Never mind patience:)  I had to scroll down and bypass some
advertisement stuff.  The last statement in te request is to discuss
how the project would fair under hurdle rate scenarios of 10%,
15%, and 20% (based on MIRR).  Do you have any specific comments? 
Also we were given a balance sheet, it does not appear to have
anything to do with this and I'm assuming it is to evaluate the
overall company for the project any thoughts?

Clarification of Answer by elmarto-ga on 04 Sep 2006 10:33 PDT
Hello again!
There's not much that can be said about how the project would fair
under different hurdle rates except for the acceptance/rejection of
the project. The hurdle rate is the required rate of return of a
project, based on the cost of capital of the firm and the risk
associated with the specific project. If the return of the project (in
this case, measured through the MIRR) doesn't exceed the hurdle rate,
then it's not worthwhile to undertake the project, either because its
return doesn't compensate for the cost of raising the funds needed to
accept, or it doesn't compensate for the risk associated with the
project, or both.

In your case, the return of the project is 18%. Therefore, when the
hurdle rate is 20%, it's not convenient to accept the project for the
reasons stated above. If the hurdle rate is lower than 18% and there
are no other attractive projects for the firm (as in this case, in
which no other projects are mentioned), then the return does
compensate for the cost of capital plus the risk premium, so the
project should be accepted.

Regarding the balance sheet, you're correct: since we're given all the
necessary information (cost of capital, re-investment rate, etc.), we
don't need the balance sheet in order to evaluate this leasing
project.

Best regards,
elmarto
pinkcitylady-ga rated this answer:5 out of 5 stars and gave an additional tip of: $10.00
Very responsive and got the answer out quickly
Thanks

Comments  
Subject: Re: MIRR NEED ASAP
From: elmarto-ga on 04 Sep 2006 15:38 PDT
 
I'm glad you liked my work. Thank you very much for the rating and tip!
Subject: Re: MIRR NEED ASAP
From: pinkcitylady-ga on 06 Sep 2006 22:09 PDT
 
Can you calculate the NPV for me I missed that in the origional problem
Subject: Re: MIRR NEED ASAP
From: elmarto-ga on 07 Sep 2006 06:13 PDT
 
Hello again!
The NPV can be easily calculated now that we've computed the cash
flows. The cash outflow at year 0 is 4,000,000. The cash inflow in
years 1-5 is 1,430,000. Finally, the cost of capital is 10%.
Therefore, the NPV will be:

NPV = -4,000,000 + 1,430,000/(1+0.10) + 1,430,000/(1+0.10)^2 + ... +
                    + 1,430,000/(1+0.10)^5 = $ 1,420,825

So the NPV of the project is $1,420,825

I hope this helps!
Subject: Re: MIRR NEED ASAP
From: pinkcitylady-ga on 07 Sep 2006 07:34 PDT
 
That is the answer I got, thank you I wasn't sure if I was doing it right

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy