# 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:

- Assume the annual revenue and cost occur at the end of the year.
- 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. - 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. - 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. - 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. - 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. - 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.