HOMEWORK

Fin 420, Spring 2021 (65 points)
Instructor: Shrikant Jategaonkar
Excel Assignment #2
This assignment is for credit and therefore you cannot work or discuss it with other groups. Any such
incident will be considered an academic misconduct (plagiarism). The only person you can work with
is your group member (if you have one). If you have any questions, please email me.
The assignment is based on Problem #18 at the end of chapter 7 in the book. Work through that
problem first and that will help you understand the assignment better.
NPV Profile
A gold miner is considering investing in a new mine in South Africa. Gold mining is an industry with many
unknowns. As such, the miner has developed ranges for several variables. Gold in South Africa is buried very
deep, so the mine will require an initial investment of between $200 and $300 million (increment by $10
million). Once this investment is made, the mine is expected to produce revenues of between $20 and $40
million (increment by $1 million) per year for between 10 to 30 years (increment by 1 year). It will cost between
$5 to $15 million (increment by $1 million) per year to operate the mine. After the useful life of the mine, the
gold will be depleted. The mine must then be stabilized on an ongoing basis, which will cost between $5 and $15
million (increment by $1 million) per year in perpetuity. You are required to plot the NPV as a function of the
two discount rate spin buttons. In other words, calculate NPV for all discount rates between the minimum and
maximum and plot those values. That’s called the NPV profile. The spin button for the Minimum Discount Rate
should go from 1% to 5% (increment by 1) and the Maximum Discount Rate should go
from 20% to 25% (increment by 1). Create an Excel sheet that shows two tables, one graph, and includes all the
features shown in the picture below:
2
INSTRUCTIONS AND USEFUL HINTS:

  1. Assume the annual revenue and cost occur at the end of the year.
  2. The attached picture shows calculations when the mine depletes at 10 years. The number of columns
    will change with the change in the time to depletion. For example, while a 10 year depletion should
    show 10 columns for years, a 30 year depletion should show 30 columns.
  3. Imagine you are constructing this table for a client and so presentation is an important aspect. The table
    size has to vary with the input parameters. (Hint: The IF statements are helpful in adjusting the table
    size). Points will be deducted for work that is not presented professionally.
  4. The inputs are listed in the picture: initial cost, annual revenue, annual cost, number of years until mine
    is depleted, perpetual annual expense after mine is depleted, maximum discount rate, and minimum
    discount rate. The user should be able to change any of these parameters and the numbers in the tables
    and the graph should change accordingly. In other words, both the table and the graphs should be
    dynamic.
  5. Use the skills you acquired while working on the 1st assignment to create the graph and tables. Points
    will be deducted if the table or the graph is not dynamic.
  6. Notice the buttons next to the Input parameters. They are called the “Spin Buttons”. You are required to
    figure out how to construct these buttons. The user should be able to use these to change the input
    parameters.
  7. You are dealing with a perpetuity with this assignment. You will use both the NPV and PV formulas in
    Excel in finding the NPV for each discount rate.