problems on finance engineering
FIN 5203, Trine University, Fall 2019, Dr. Kolar
Page 1
Excel Assignment 2: due Sunday, September 15th at 11:55pm
Note: Please upload your Excel file (in the .xlsx format – do not use the GetFormula function I used in my examples - include your name in the file name) on Moodle using the corresponding link under Week 4. Start with a new Excel file and answer each question in a separate sheet within your file. Please make sure you use cell references wherever appropriate, show your inputs at the top like I did in my examples, show and do all of your work directly in Excel, and answer all questions (you may have to type written answers to some questions if appropriate). After you upload the file, double-check the file you uploaded and make sure it contains all of your answers; let me know immediately in case there is an issue with the file.
Question 1 (based on Chapter 5) Consider a project with the cash flows described below. Assume 11% cost of capital.
Year Cash Flow
0 -150,000
1 20,000
2 30,000
3 40,000
4 40,000
5 30,000
6 20,000
a) What is the present worth (NPV) of the project? Using the present worth (NPV) rule, should the project be accepted? Why?
b) What is the internal rate of return (IRR) of the project? Using the IRR rule, should the project be accepted? Why?
Question 2 (based on Chapter 5) Use the EXTERNAL RATE OF RETURN method to answer this question. Assume 15% cost of capital. Consider a project with the cash flows described below, and assume the REINVESTMENT rate is equal to 10% (project’s cash flows can be re- invested at 10%). Should the project be accepted? Why?
Year Cash Flow
0 -300,000
1 140,000
2 100,000
3 70,000
4 50,000
5 40,000
6 40,000
FIN 5203, Trine University, Fall 2019, Dr. Kolar
Page 2
Question 3 (based on Chapter 6) Consider the two mutually exclusive projects described below.
a) Assuming the cost of capital is 9%, should either of the two projects be accepted? Why? b) Assuming the cost of capital is 16%, should either of the two projects be accepted? Why? c) For all positive values of the cost of capital, divide the cost of capital in ranges with different decisions, describe and
discuss what decision would be made in each range and why. Include an NPV profile chart to illustrate your answer.
Year Cash Flow
Project A
Cash Flow
Project B
0 -450,000 -700,000
1 200,000 200,000
2 150,000 200,000
3 100,000 200,000
4 100,000 200,000
5 75,000 200,000
Question 4 (based on Chapter 6) A chocolate company is deciding between two chocolate machines, with unequal lives – machine A lasts 4 years, while machine B only lasts 3 years. The choice has no impact on company’s revenues. Assume repeatability, and 15% cost of capital. The annual costs for each machine are described below.
Year Cash Flow
Machine A
Cash Flow
Machine B
0 -180,000 -150,000
1 -25,000 -20,000
2 -30,000 -25,000
3 -35,000 -30,000
4 -40,000 N/A
Determine which machine should be picked, and explain why, using
a) The equivalent annuity approach. b) The common denominator approach.
Question 5 (based on Chapter 7) XYZ Steel Corporation is deciding whether to expand operations. The expansion would require purchasing a new machine for $500,000, with additional $30,000 shipping and installation fees. The machine will be depreciated using a 7-year recovery period (use the percentages given in table 7-3 in the textbook). This project is expected to last 6 years, and the machine is expected to be sold for $200,000 at the end of year 6. In each of the six years of the project (years 1-6) there will be additional revenues of $125,000, and additional expenses of $45,000. Assume 35% tax rate, and 12% cost of capital.
a) Calculate after-tax annual cash flows from the project for years 0-6. b) Calculate the NPV and the IRR of the project, determine whether it should be accepted, and explain why.