Homework 1 - Finance Fundamentals This homework is a group assignment. Each group should have one person upload their group submission. The homework assignment will be graded on a [1 + (100%-almost perfect), (90% - well done but with a few mistakes), ~- (80%- an honest effort), 0% - no effort or not submitted] system. YOU MUST SUBMIT YOUR WRITEUP ONLINE VIA CANVAS BY THE DUE DATE. You may write your answers to the non-excel problems in a text box within excel, or you may embed your excel problem into a word document that contains your solutions to the other problems. If you need help figuring out how to do these, please use google! 1. Excel Problem: You have the opportunity to invest in Gopher Gardens, a residential high-rise real estate property in downtown Minneapolis. You expect Gopher Gardens to generate $4.5M in rent one year from now and for rent to increase at 8% per year for the following four years. Five years from now, you believe you will be able to sell the property for $30 million. Assume the interest rate is 6% per year. a. How much should you be willing to pay today for Gopher Gardens? b. If you can buy the property for $43 million, what is the NPV of this opportunity? C. Acknowledging that the dollar values above are in the future and therefore are necessarily estimates) explore what the NPV would be if your assumptions about growth are wrong, Specifically calculate the NPV for values of g between 0% and 10% (NPV @g=0%, NPV @g=1% etc.) You should use the Data Table utility, found in Data tab>What-if analysis>Data Table to do this. Plot your results in a connected (the dots are connected) scatter plot. d. At what value of g is the NPV = 0? (hint: use Goal Seek in the same place as Data Table) e. Repeat your analysis from the last two questions, but now change the discount/interest rate. Evaluate the NPV for all values of r between 0% and 10%. Plot the results in a connected scatter plot. This is called an NPV profile. f. At what value of r is the NPV = 0? This is called the Internal Rate of Return (IRR). g. Now use a Data Table to simultaneously evaluate NPV at all values of g between 0% and 10% AND all values of r between 0% and 10%. (you should have a 10x10 matrix). Highlight cells where NPV is negative (hint: try using conditional formatting on the Home tab). Homework 1 - Finance Fundamentals This homework is a group assignment. Each group should have one person upload their group submission. The homework assignment will be graded on a [1 + (100%-almost perfect), (90% - well done but with a few mistakes), ~- (80%- an honest effort), 0% - no effort or not submitted] system. YOU MUST SUBMIT YOUR WRITEUP ONLINE VIA CANVAS BY THE DUE DATE. You may write your answers to the non-excel problems in a text box within excel, or you may embed your excel problem into a word document that contains your solutions to the other problems. If you need help figuring out how to do these, please use google! 1. Excel Problem: You have the opportunity to invest in Gopher Gardens, a residential high-rise real estate property in downtown Minneapolis. You expect Gopher Gardens to generate $4.5M in rent one year from now and for rent to increase at 8% per year for the following four years. Five years from now, you believe you will be able to sell the property for $30 million. Assume the interest rate is 6% per year. a. How much should you be willing to pay today for Gopher Gardens? b. If you can buy the property for $43 million, what is the NPV of this opportunity? C. Acknowledging that the dollar values above are in the future and therefore are necessarily estimates) explore what the NPV would be if your assumptions about growth are wrong, Specifically calculate the NPV for values of g between 0% and 10% (NPV @g=0%, NPV @g=1% etc.) You should use the Data Table utility, found in Data tab>What-if analysis>Data Table to do this. Plot your results in a connected (the dots are connected) scatter plot. d. At what value of g is the NPV = 0? (hint: use Goal Seek in the same place as Data Table) e. Repeat your analysis from the last two questions, but now change the discount/interest rate. Evaluate the NPV for all values of r between 0% and 10%. Plot the results in a connected scatter plot. This is called an NPV profile. f. At what value of r is the NPV = 0? This is called the Internal Rate of Return (IRR). g. Now use a Data Table to simultaneously evaluate NPV at all values of g between 0% and 10% AND all values of r between 0% and 10%. (you should have a 10x10 matrix). Highlight cells where NPV is negative (hint: try using conditional formatting on the Home tab)