(15 pts.)

1. Phil Farmer just sold his farm to his nephew. The terms of the sale are that the nephew will pay Phil $800/acre each year for the next 35 years.

What is the equivalent sales price that Phil is receiving for his land, given in today's dollars, if his discount rate is 4%?

Explain your answer, including any TVM formulas you used.

Use the 'Q1' worksheet in the picture provided below

(20 pts.)

2. You are considering buying a new car from a local dealer (Dealer 1) for $40,000. Dealer 1 will finance the entire purchase price at 6% interest over 6 years. Interest is compounded monthly and you must make monthly payments.

What is the most you should be willing to offer another dealer (Dealer 2) for the same car who is offering a financing plan with a 4% interest rate over 6 years?

Hint: If theloan payments are the same you can assume you would be indifferent between buying from the two dealers.

Explain your answer, including any TVM formulas you used.

Use the 'Q2' worksheet in the picture provided below

(70 pts.)

3. As owner of the Illini Tap you are considering an investment in upgrading the bar's entertainment systems.

If you decide to invest in these upgrades you have estimated it will require an initial investment of $48,000 which will be used to buy some TVs, a digital jukebox, and a new stereo system for the bar.

You estimate that this will boost revenues by $15,000 during the first year because of the additional customers this will attract to the bar. These additional revenues are expected to increase by 3% each year. However, the upgrade will also create additional costs of $6,000 in the first year due to additional satellite service and music and sports package fees. These additional costs are also expected to increase over time, but only by 2% each year. Your accountant says you can depreciate the initial investment using the straight-line method over 6 years. You estimate that the new sound and video equipment will have zero salvage value so the full $48,000 will be depreciated. Finally, you assume a marginal tax rate of 25%.

Using the information above, answer the following questions assuming a 10-year planning horizon.

Provide complete answers in your homework file, including all values in suggested tables to show how you did the calculations.

(20 pts.)

1. Calculate the annual net after tax cash flows for this investment during the planning horizon.

Hint: The table below would be a good way to lay out the individual cash flows and do the necessary calculations to get the NATCF values in Excel. You could then copy and paste the table and values into a word document.

Complete the NATCF table in the 'Q3a-c' in the picture provided below

(15 pts.)

1. Calculate the NPV for this investment using 5%, 10%, 15%, and 20% discount rates. Provide the present values of the NATCF's and the NPV for each discount rate. Use the table layout below as a guide.

Answer the NPV table in the 'Q3a-c' in the picture provided below

(10 pts.)

1. Based on your calculations for part (b), what can you say about the Internal Rate of Return for this investment? Calculate the IRR for this investment to confirm (your calculator may have an IRR function; you can also use trial-and-error or the Solver in Excel, or Excel's IRR function).

Enter your answers in the boxes provided in the 'Q3a-c' in the picture provided below

(15 pts.)

1. Calculate the series of NATCFs and the NPV for this project at a 10% discount rate assuming that you finance the investment using a 8-year loan with a fixed interest rate of 4% (annual compounding and end-of-year payments) and a 50% down payment. Complete the final two columns of Table 1 below.

Hint: This will require you to adjust the NATCF calculations that you made for part a. You will need to account for the loan when calculating the NATCF series. You can assume the bar is able to deduct the interest portion of the loan payments as an expense for income tax purposes. This should be done using a similar table as you created for part a, adding columns for the loan payment information. Clearly label your tables to help with grading. An example layout for the NATCF table with the loan is provided on page

Complete the NATCF and NPV table in the 'Q3d-e' in the picture provided below

(10 pts.)

1. Finally, using NPV as your decision criteria and a 10% discount rate, should you upgrade the sound system in the bar and, if so, should you pay cash or use a loan to finance the investment? Very briefly justify/explain your answer.

Enter your answer in the box provided in the 'Q3d-e' in the picture provided below

AutoSave 9.CE ... HW 6 template 2023 Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Comments Share Calibri (Body) 11 A A 207 Wrap Text v General Insert v Delete Paste B MVA Merge & Center v $ ~ % 9 08 598 Conditional Format Cell Sort & Find & Analyze Sensitivity ormatting as Table Styles Format