GUIDELINES$-$ Work on each question in a separate worksheet of the same Excel file. Name the sheets Q$1,$ Q$2,$ etc. Clearly indicate where you have the answer to each part $($A$,$ B$,$ C$,$ etc.$).$

Unless otherwise stated, assume that cash flow occurs at the end of a period.

If a part of a question specifies a method to be used, only that method should be used to solve the problem. If any other method is used, it will result in no points for that part of the question. Any method can be used for parts that do not specify a method.

If a part of a question is asking you to use the timeline method, the value of the present value factor or the future value factor for each cash flow should be calculated first to answer that part. These factors should be calculated in Excel using the interest rate and time. Also, these factors should be used to find the answer to the question. Excel built$-$in functions $($functions related to time$-$value of money, such as PV$/$FV$/$NPV$)$ cannot be used for questions asking you to solve using the timeline method.

Detailed calculations for every question should be shown. If only the answer is written, no points will be awarded.

The best way to use Excel is to only input the values provided in the question. Every other calculation should be done in Excel. Please do not use a calculator to solve the questions and then just write the answers in Excel.

The assignment can be submitted multiple times before the deadline. If the assignment is submitted multiple times, please submit the entire assignment every time it is submitted. Only the last submission will be graded.

QUESTIONS$-$

Question $1$: A firm spends $$20,000$ annually on electricity. An outside company has offered to install an auto$-$controlled system that will reduce electric bills by $$1,400$ in each of the next $10$ years. Assume that the savings are realized at the end of the year. The system will cost $$8,500$ to purchase now and another $$2,000$ at the end of five years from now for major overhauling. The system will not have any value at the end of its life. Assume the cost savings are known with certainty and the interest rate is $7\mathrm{.}5\%.$

Calculate the NPV of installing the new system. Use the timeline method to find the answer. $(10)$

Should the firm install the new system? Why or why not? $(4)$

If the annual reduction in electricity bill is instead $$1,500,$ what is the NPV of installing the new system? Use the timeline method to find the answer. $(10)$

Calculate the IRR $($or IRRs$)$ of the project when annual reduction in electricity bill is $$1,500.(3)$

Calculate the NPV of the project when annual savings is $$1,500$ for all interest rates between $0\%$ and $15\%$ with $1\%$ increment $($i$.$e$.,$ for $0\%,1\%,2\%,3\%,4\%,5\%,.....,15\%).(12)$

Draw a scatter chart of NPV $($Y$-$axis$)$ vs interest rates $($X$-$axis$)$ using values found in part E$.$ The points should be joined by a line. Label the axis and add a chart title. $(6)$

What does the point where the scatter plot intersects the X$-$axis signify? $(3)$

Question $2$: Jenny is $23$ years old today $($this is time $0).$ She is hoping to retire at age $62($exactly on her $62$nd birthday$)$ and actuarial tables suggest that she is likely to live until the age of $86.$ She wants to move to an area with a warmer climate upon retirement on her $62$nd birthday. The move is estimated to cost $$25,000.$ Starting on the $62$nd birthday and ending on the $85$th birthday $($all withdrawals are at the beginning of the year$),$ a withdrawal of $$30,000$ per year will be made to meet annual living expenses. Assume the interest rate to be $6\mathrm{.}75\%$ for all calculations.

What is the total amount required on the $62$nd birthday so that this amount can be used to meet all the expenditures in retirement years? Use the timeline method to find the answer. $(15)$

What is the present value $($i$.$e$.,$ value at year $23)$ of the required savings calculated in part a$?(4)$

To attain the retirement goal, Jenny is planning to save an equal amount each year starting from today. The last savings will be made on her $61$st birthday. How much needs to be saved each year? $(4)$

Question $3$: Suppose Mary would like to purchase a new car today for $$50,000.$ She will pay $$6,500$ as the down payment towards this purchase and finance the balance over $7$ years at an annual interest rate of $10\mathrm{.}25\%.$ The first payment will be made in exactly one month from the purchase date. Payments are made at the end of every month over the next $7$ years.

What is the loan amount? $(2)$

What is the monthly interest rate? $(2)$

How many monthly payments will be made? $(2)$

Calculate the monthly payment required to fully pay off the loan in $7$ years using both the formula and the function method. $(8)$

Show that the present value of all the monthly payments is equal to the loan amount. Use the timeline method for this. $(15)$