ME 470 Systems Design In-class/Homework Corporate Finance 1. For Example 18.3 of Dieter and Schmidt we showed in lecture how the Present Value (PV) and Net Present Value (NPV) functions in Excel could be used to calculate the Present Value of the costs of Machine A. Create an Excel spreadsheet that shows the annual costs and calculates the Present Value of the costs of Machine B in example 18.3. Do two separate calculations, the first which uses the PV function, and the second which uses the NPV function. Make sure you get the same results. The functions work differently. 2. a. Use Excel to find the NPV for a Cardiac Stent project: • Years 1-5 development: $300M/year • Years 5-9 FDA testing, IP costs, manufacturing ramp up: $ 500M/year • Year 10 until expiration of patent – Volume: 600,000 units / year – Revenue: $2500 / unit – Costs: $1000 / unit • Patent issues at start of year 9 and is enforceable for 17 years • Cost of money is 6% b. Find the NPV if the FDA testing takes twice as long as planned (still at $500M/year) c. For the original case, determine the NPV if there is a 5% probability that there is no FDA approval, a 10% probability of 1.5B intellectual property settlement in year 14, and a 85% probability of business as predicted. 3. What price would you pay today for a device that reduces Aerodynamic Drag of a semi-tractor trailer by 3% such that your break even economically? Assume: - 50% of energy for hauling a trailer goes to aerodynamic drag - 30% of energy for hauling a trailer goes to rolling resistance - The nominal mileage for the semi is 6 miles/gallon - Trailer life is about 1,000,000 miles - A trailer is pulled about 500 miles per day - The fuel price is assumed to be $3.00 per gallon - The interest rate is assumed to be 5%Personal Finance 4. Publishers Clearing house: According to the Official Rules of the Publishers Clearinghouse - “Win $10,000,000.00 from Giveaway No. 1400 and you will receive $225,000.00 a year for 29 years and a final payment of $3,475,000.00 in the 30th year” (http://prism.pch.com/Rules/Rules.aspx?Cid=MQA1AA== ). Determine the present value of the prize for two different interest rates, 3% and 7%. 5. Interest Rate and Loan Period Sensitivity You are planning to purchase a new car for $25,000 dollars and are wondering how hard to shop for a car loan. Examine the sensitivity of the future value of the costs with respect to interest rate. Assume a down payment of $2500 and annual interest rates of 5%, 6%, 7%, 8% and 9%. a) Use the PMT function in Excel to determine the monthly payment you would need to make at each interest rate (assume a 48 month loan and don’t forget to divide the interest rate by 12 to get the monthly interest rate). b) Determine the amount of interest you paid over the course of the loan (down payment + payments – cost of the car) for each interest rate. c) Repeat parts a) and b) for a loan period of 60 months. 6. Break Even Consider a Honda Civic Hybrid w/Nav (MSRP= $25,800, Combined fuel economy=42 mpg) vs. a Honda Civic EX w/Nav (MSRP =$22,255, Combined fuel economy = 29 mpg) (Source:Edmonds.com). Determine the break even point (number of miles you would have to drive for equivalent cost) if fuel prices are $2.00/gal, $2.50/gal, $3.00/gal, $4.00/gal, or $5.00/gal and report the values in a table. Grading: You must show your work. A reader must be able to easily see the method, the equations, and what spreadsheet commands were used. The use of cash flow diagrams and clear annotation of spreadsheets will go a long way to helping with clarification. There should be sufficient text that another student in the class could understand and explain what was