make this excel spreadsheet:

Basic Information:

$$ Please include your last name in the submission file $($e$.$g$.,$ Bargeron$\_$PS$1)$

o If you are working with someone else, please include both last names in the submission

file name.

o Please also include names $($s$)$ in cell A$1$ of the tab.

o If you are working with someone, please only submit one copy of the excel file.

$$ This problem set is worth a total of $40$ points.

o The specific number of points for each question is next to the question in red.

$$ The problem set is due on Friday, September $20$th by $5$:$00$ pm$.$ It should be submitted online

through the Canvas system.

o The submission should clearly label each tab based on the question $($e$.$g$.,$ Question $1,$

Question $2$A&B$,$ Question $2$C&D$,$ etc.$).$

o For all questions, when relevant, please put your value drivers in the top left$-$hand corner

of the spreadsheet and make sure that all values are a function of a key driver $($i$.$e$.,$ don$$t

hard wire numbers$).$

o Clearly label your answers $($e$.$g$.,1$d$)$ and highlight all answers. If an answer requires

written justification, please include the written response below the answer.

$$ For example: Ans. $1$d: $$2,571\mathrm{.}00$

$1.$ You are considering attending an MBA Program. The cost of the MBA program is $$65,000$ In

addition, you will have to stop working for one year and you are making $$55,000$ a year $($thus

the total cost is $$120,000).$ Assume both costs occur at time $0.$ You expect the MBA program to

increase your current salary by $$34,000$ for each of the next five years $($and nothing after year

$5).$ However, the actual increase is uncertain, and you estimate a standard deviation of $$15,000.$

Assume a discount rate of $8\%.[$Total $12$ Points$]$

a$.$ Conduct a Monte Carlo simulation with $1000$ iterations. What is the average NPV and

the standard deviation of the NPV$?[7$ points$]$

b$.$ What percentage of the time does the project have a positive NPV$?[3$ points$]$

c$.$ What are the values at the $5$th and $95$th percentiles? $[2$ points$]$

$2.$ An investment firm has a new security for sale. You pay them $$200,000$ today and the company

will give you back $$20,000$ at the end of the first year, $$25,000$ at the end of year $2,$ $$30,000$ at

the end of year $3,...,$ $$55,000$ at the end of year $8,$ and $$60,000$ at the end of year $9.[$Total $14$

Points$]$

a$.$ Calculate the NPV of this investment if the discount rate is $10\%.[3$ points$]$

b$.$ Calculate the IRR of this investment. $[2$ points$]$

c$.$ Use the IRR computed in part B above as the discount rate and compute a loan

amortization table for the investment. $[5$ points$]$

d$.$ Assume the discount rate for the first five years is $8\%,$ but for the last four years it is

$12\%.$ What is the NPV of the investment? $[4$ points$]$

$3.$ Steve has a son who turned five years old today. Steve wants to give his son a gift on his $22$ nd

birthday $($seventeen years from today$)$ that will pay for rent on an apartment in London,

England for $5$ years. He expects the rental price to be $$50,000$ for the first year $($due at the end

of the year$)$ and grow at $3\%$ each year afterward. The discount rate is $5\%.[$Total $14$ Points$]$

a$.$ How much money does Steve need to give his son on his $22$ nd birthday? $[4$ points$]$

b$.$ If Steve wants to invest the same amount of money into an account at the beginning of

each year $($starting today$)$ for the next seventeen years, how much must he invest each

year? The account earns $5\%$ each year. $[4$ points$]$

c$.$ Steve is not sure when he will be able to start investing money. Create a vertical data

table listing the yearly investment amount needed for the number of investment years

between $12$ and $22$ in $1-$year increments. $[3$ points$]$

d$.$ Create a two$-$dimensional table giving the investment amount needed for different

number of investment years and growth rates for rental prices. The vertical dimension

should be investment years between $12$ and $22$ in $1-$year increments. The horizontal

dimension should be rental growth rates between $-2\%$ and $8\%$ in increments of $1\%.[3$