The Maxwell School
Syracuse University
Syracuse University
Due Thursday 4/25
Fuel cells are an alternative to batteries for powering electric vehicles. They are very efficient and produce little pollution, but at the moment they are very expensive to produce. Making them less expensive will take a lot of research and may not pay off for a decade or more. This exercise asks you to use present value calculations to evaluate a potential investment in fuel cell research.
Imagine you are the manager of a company in the energy industry. Suppose you know that you could develop a marketable fuel cell technology if you set up a research lab and allow a team of scientists and engineers to spend a decade doing research. To keep things simple, assume that building and operating the lab until the product was developed could be paid for with a single $100 million payment today. After that, the lab would have no costs or revenue until the product was developed. Once developed, the technology could be licensed to auto companies for $10 million per year forever. Finally, to raise the $100 million to pay for the lab, you would have to issue a bond with a $100 million face value, a 10 year maturity date, and a $7 million annual coupon payment.
To eliminate ambiguities about the timing of payments, setting up the lab would cost $100 million in year 0 (today) and would produce $10 million of income per year with the first payment in year 11. The bond would raise $100 million in year 0, would cost $7 million in years 1-9, and would cost $107 million in year 10 (7+100).
Please use the template in Teams to build a nicely formatted spreadsheet to determine the NPV of the overall project (bond and lab combined) for three interest rate cases: 4%, 5%, and 6%.
In setting up the worksheet, please: (1) name the cells in the data section and use the names in any formulas in the subsequent sections,(2) enter all values in millions of dollars; (3) set the format for all cells containing dollar values to show 2 decimal places, and (4) use the fundamental present value formulas from class rather than Excel's built-in present value functions like PV (to help demystify how the calculations are done).
Finally, note that the spreadsheet will be easiest to set up if you are careful about using absolute row and column cell references (e.g., A$5 to fix the row to 5 or $B7 to fix the column to B).