View Question
 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).```
 Subject: Re: MIRR NEED ASAP Answered By: elmarto-ga on 04 Sep 2006 06:58 PDT Rated:
 ```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: and gave an additional tip of: \$10.00 ```Very responsive and got the answer out quickly Thanks```

 `I'm glad you liked my work. Thank you very much for the rating and tip!`
 `Can you calculate the NPV for me I missed that in the origional problem`
 ```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!```
 `That is the answer I got, thank you I wasn't sure if I was doing it right`