Tesla Inc. is launching a new production line to manufacture their new EV battery technology. Research and development costs for the past 6 years, leading up to production, have been $1.2 million per year. Today, production machinery totaling $4.55 million is being purchased to equip the new line. Operation and maintenance costs will total $85,000 per year, and materials and overhead costs will be $1.35 million annually to produce the batteries. The new production line will operate for 5 years, at which time the equipment will be sold at 20% of purchase price. Revenue from the new EV battery technology is expected to be $3.35 million the first year, increasing by $1.0 million per year for the next 3 years, then decreasing by $0.5 million per year in year 5.
To finance this new production line, a bank will loan Tesla Inc. the money at an interest rate of 9.99%, compounded monthly.
PART A:
What is the interest rate per month that Tesla Inc. is paying? What is the nominal annual interest rate? What is the effective annual interest rate?
Solve the problem in EXCEL using simple calculations and an EXCEL spreadsheet function.
PART B:
Create a cash flow diagram in EXCEL as a stacked column chart to represent the situation above. Make sure to label chart title, point-of-view, interest rate per period, time units, receipts and disbursements, axis labels with units, and data labels, as well as list any assumptions you might have (if needed).