Google Answers Logo
View Question
 
Q: Finance-Calculate project cash flows, NPV, and IRR excel spreadsheet ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: Finance-Calculate project cash flows, NPV, and IRR excel spreadsheet
Category: Business and Money
Asked by: needasapnow-ga
List Price: $50.00
Posted: 10 Sep 2003 12:38 PDT
Expires: 10 Oct 2003 12:38 PDT
Question ID: 254308
I could e-mail the excel spreadsheet as attachments to make it much
easier!
Need this by Sept. 10, 2003 10pm eastern US time

PLease Complete 7.21 with formulas

	Revenues generated by a new fad product in each of the next 5 years
are forecasted as follows:
								
	Year	Revenues						
	1	$40,000 						
	2	30,000   						
	3	20,000   						
	4	10,000   						
	Thereafter	0   						
								
	Expenses are expected to be 40 percent of revenues, and working
capital required in each year is expected to be 20 percent of revenues
in the following year. The product requires an immediate investment of
$50,000 in plant and equipment.
								
	a. What is the initial investment in the product? Remember working
capital.
								
	b. If the plant and equipment are depreciated over 4 years to a
salvage value of zero using straight-line depreciation, and the firm's
tax rate is 40 percent, what are the project cash flows in each year?
								
	c. If the opportunity cost of capital is 10 percent, what is the
project NPV?
								
	d. What is the project IRR?							
								
								
	Solution							
								
	Problem 7-21 							
	Instructions							
								
	Use formulas and the MS Excel NPV and IRR functions to solve this
problem.
								
	a. What is the initial investment in the product? Remember working
capital.
								
	Initial outlay	       $50,000 						
	Working capital	       FORMULA						
	Initial investment	$50,000 						
								
	b. If the plant and equipment are depreciated over 4 years to a
salvage value of zero using straight-line depreciation, and the firm's
tax rate is 40 percent, what are the project cash flows in each year?
								
				        Working		        Cash		
	Year	Sales	       Expenses	Capital	Depreciation	Flow		
	0					             ($50,000)		
	1	$40,000 	FORMULA	FORMULA	FORMULA	      $24,000 		
	2	$30,000 	FORMULA	FORMULA		      $18,000 		
	3	$20,000 	FORMULA	FORMULA		      $12,000 		
	4	$10,000 	FORMULA	FORMULA		      $6,000 		
								
	c. If the opportunity cost of capital is 10 percent, what is the
project NPV?
								
	NPV	($191.93)						
								
	d. What is the project IRR?							
								
	IRR	FORMULA


Problem 6.20

Here are the cash flows for two mutually exclusive projects:						
						
Project	   C0	   C1	     C2	      C3		
A	   ($20,000) $8,000  $8,000    $8,000 		
B	   ($20,000)	0        0     $25,000 		
						
a. At what interest rates would you prefer project A to B?						
						
b. What is the IRR of each project?						
						
						
Solution						
						
Problem 6-20 						
Instructions: Need Formulas						
						
Use the MS Excel NPV function to calculate the NPVs for both projects
in the profile below. In part B use
the IRR function.						
						
a. At what interest rates would you prefer project A to B?						
						
NPV Profile						
Discount						
Rate	NPVA	NPVB				
0%	FORMULA	FORMULA				
2%	FORMULA	FORMULA				
4%	FORMULA	FORMULA				
6%	FORMULA	FORMULA				
8%	FORMULA	FORMULA				
10%	FORMULA	FORMULA				
12%	FORMULA	FORMULA				
14%	FORMULA	FORMULA				
16%	FORMULA	FORMULA				
18%	FORMULA	FORMULA				
20%	FORMULA	FORMULA				
						
						
						
						
b. What is the IRR of each project?						
						
Project A IRR	FORMULA					
Project B IRR	FORMULA	

Problem #3				

Find the WACC of William Tell Computers. The total book value of the
firm's equity is $10 million; book value per share is $20. The stock
sells for a price of $30 per share, and the cost of equity is 15
percent. The firm's bonds have a par value of $5 million and sell at a
price of 110 percent of par. The yield to maturity on the bonds is 9
percent, and the firm's tax rate is 40 percent.
						
						
Solution						
						
Problem 11-10 						
Instructions						
						
	Find the WACC of William Tell Computers. The total book value of the
firm's equity is $10 million; book value per share is $20. The stock
sells for a price of $30 per share, and the cost of equity is 15
percent. The firm's
bonds have a par value of $5 million and sell at a price of 110
percent of par. The yield to maturity on the bonds is 9 percent, and
the firm's tax rate is 40 percent.
								
								
	Solution							
								
	Problem 11-10 							
	Instructions							
								
	Enter formulas to calculate the unknowns.							
								
          Market		                      After tax			
          Value	    Proportions	 Cost	    cost	    WACC		
Debt	    FORMULA    #VALUE!	       9%        FORMULA	FORMULA		
Equity    FORMULA    #VALUE!	       15%	         15%	FORMULA		
Total	    $0 	    #VALUE!			                  0.0%
Answer  
Subject: Re: Finance-Calculate project cash flows, NPV, and IRR excel spreadsheet
Answered By: wonko-ga on 10 Sep 2003 16:20 PDT
Rated:5 out of 5 stars
 
Problem 7-21

a. The initial investment would be equal to the plant and equipment
expenditure of $50,000 plus the working capital for the first-year. 
The working capital for the first-year is defined as 20% of the
revenues of year 1, so it is equal to $8,000.  Therefore, the total
initial investment is $58,000.  The working capital requirement for
years 2 and 3 is $6000, $4000, respectively.  Because the business
ends at the end of Year 4, there is a return of $20,000 in working
capital then.

b.  Straight-line depreciation over four years means that the plant
and equipment will be depreciated in equal amounts over four years.  A
salvage value of zero means that the plant and equipment will be
completely depreciated after four years.  Therefore, the annual
depreciation is equal to $50,000 divided by four or $12,500 per year.
 
The tax payment per period is equal to the (revenue minus the expenses
minus the depreciation) multiplied by 40%.  Working capital is not an
expense, so it is not used in this calculation.  In the third and
fourth years, the depreciation plus the expenses exceeds the revenue,
so there is no tax payment due.  The taxes for year one are $4600, for
year two are $2200, and for years three and four are zero dollars,
respectively.
 
The cash flow in each year is determined by taking the revenue minus
the expenses minus the working capital used minus the tax payment. 
Depreciation is a noncash expense, so it does not affect cash flow. 
The cash flow for year zero is -$58,000, for year one is $13,400, for
year two is $11,800, for year three is $10,000, and for year four is
$26,000.

c.  The net present value is calculated by subtracting the initial
investment amount from the sum of the cash flow for each period
divided by (1 + i)^n where i is the cost of capital and n is the
period.  The net present value for the project is -$10,794.62. 
Generally, one wishes to avoid projects with negative net present
values.
 
d.  The internal rate of return is the value of i for which the net
present value equation equals zero.  An iterative approach is required
to solve this one.  My Hewlett-Packard calculator calculates an
internal rate of return of approximately 1.95%.  If the cost of
capital were less than 1.95%, then the project would have a positive
net present value.
 
Here is a table of computed values: 
 
Year  0 1  2  3  4 
Plant    -$50,000 
Revenue   $40,000  $30,000  $20,000  $10,000 
Expenses  -$16,000 -$12,000 -$8,000  -$4000 
Working Capital  -$8,000  -$6,000  -$4000  -$2000  $20,000 
Depreciation  -$12,500 -$12,500 -$12,500 -$12,500 
Tax Payment  -$4600  -$2200  $0  $0 
Cash Flow  -$58,000 $13,400  $11,800  $10,000  $26,000 
 
Present Value -$10,794.62
Internal Rate of Return 1.95%


Problem 6-20

a. The net present value of a series of cash flows is calculated by
the formula NPV = -I + C(n)/(1 + r)^n where C(1) is the cash flow in
period n, I is the initial investment, and r is the interest-rate
 
Rate NPV A         NPV B  
0 4000         5000  
2 3071.066 3558.058  
4 2200.728 2224.909  
6 1384.096 990.4821  
8 616.7759 -154.194  
10 -105.184 -1217.13  
12 -785.35  -2205.49  
14 -1426.94 -3125.71  
16 -2032.88 -3983.56  
18 -2605.82 -4784.23  
20 -3148.15 -5532.41  

As you can see, the net present value of Project B is higher than that
of Project A for interest rates of 4% or less.  Project A is preferred
for interest rates of 6% or more, although Project A becomes
potentially unattractive for interest rates of 10% or higher.

One project is preferred over another if it's net present value is
greater than that of the other Project (it has a higher POSITIVE net
present value or has a lesser NEGATIVE net present value).  Since the
net present value of Project B is greater than the net present value
of Project A, it is preferred for discount rates of 0% to 4%.  For
discount rates of 6% to 20%, Project A is preferred because it has a
greater net present value (it is less negative in those cases where
both are negative) then does Project B. However, in general, one tries
to avoid projects with zero or negative net present values.
 
b.  The Internal Rate of Return is the value of r for which the Net
Present Value is zero.  In this case, it can be solved using simple
algebra.  The Internal Rate of Return for Project A is 9.7%, and the
Internal Rate of Return for Project B is 7.72%.  This makes sense
based on what we saw above where Project B had a slightly negative net
present value at 8% and Project A had a slightly negative net present
value at 10%.

Problem 11-10

The Weighted Average Cost of Capital is calculated using the formula
r* = rD (1-TC) D/V + rE (E/V) where r* is the weighted average cost of
capital, rD is the firm's current borrowing rate, TC is the firm's
marginal income tax rate, rE is the expected rate of return on the
firm's stock, D is the market value of the firm's debt, E is the
market value of the firm's equity, and V is the total market value of
the firm (D + E).
 
From the problem, we know that rE is 15% and TC is 40%. 
 
D is determined by multiplying the par value of $5 million by the 110%
premium currently prevailing in the market, which equals $5,500,000.
 
rD is determined by dividing the yield to maturity on the bonds of 9%
by the 110% premium currently prevailing in the market, which equals
8.18%.
 
E is determined by dividing the firm's book value of the equity by the
book value per share and then multiplying the result by the price per
share.  $10 million book value/$20 book value per share = 500,000
shares outstanding.  500,000 shares*$30 per share = $15 million.
 
V is determined by adding D and E, which equals $20,500,000.

Plugging the above values into the Weighted Average Cost of Capital
formula given above results in an r* of 12.29%.

Sincerely,
Wonko
needasapnow-ga rated this answer:5 out of 5 stars

Comments  
There are no comments at this time.

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