FINA 314: Business Finance    Excel Assignment Spring 2017    50 points 1 Work individually.  Answer the following questions using Excel to calculate the answers.  Submit your Excel file on Blackboard by 11:59pm on Thursday, April 27, 2017.   50 points total.  Late submissions up to 24 hours past the deadline receive a 50% discount, 100% thereafter. Tasks to complete: 1.  Download and save the Excel file, “FINA314-ExcelAssignment-Spring2017”.  The file is located in the Excel Assignment folder. 2.  Save the file to your own drive and rename the file with your last name and first name.  For example, FINA314-ExcelAssignment-Spring2017-smith-john. (-3 points if you do not rename your file as required) 3.  The Excel file contains worksheet  tabs, one for each problem.  Complete  each problem by entering your answer in the indicated space.   Use Excel formulae,  functions, and cell referencing (when possible) to complete each problem.  DO NOT solve the problem using a calculator and then type the answer in Excel, ts will result in “0” credit for the problem. 4.  Submit your answer file on Blackboard by the deadline. Fair warning:  file sharing is an act of academic dishonesty.  See policy in the syllabus. Question 1 (15 points) You want to buy a $300,000 house.  You plan to make a down payment of 20% of the purchase price and finance the rest with a 30-year fixed rate mortgage loan.  The loan is fully amortizing, and requires monthly payments at the end of each month.  The nominal loan rate is 5%, compounded monthly.  1) How much of the purchase price will you finance with the mortgage loan?  2) What is your anticipated monthly mortgage payment? Question 2 (10 points) Suppose that you deposit $200 at the end of each month into an account paying an expected annual rate of return of 3%, compounded monthly.  How much money will you have in the account in 10 years? Question 3 (10 points) An investment offers to pay you $300 per quarter for 10 years.  If the annual rate is 11% with quarterly compounding, then what is the present value of these cash flows? Question 4 (10 points) You currently have $4,000 in a bank account that pays a nominal rate of 1%, compounded monthly. You plan to make additional monthly deposits of $200, starting at the end of ts month.  How many payments will you have made when your account balance reaches $50,000? Question 5 (5 points) A basketball player is offered the following contract today, Jan. 1, 2012:  $2 million immediately, $2.40 million in 2012, $2.90 million in 2013, $3.60 million in 2014, and $3.80 million in 2015. Assume all payments other than the first $2 million are paid at the end of the year.    If the appropriate discount rate is 10 percent per year, what is the present value of the deal? FINA 314: Business Finance    Excel Assignment Spring 2017    50 points 2 Excel nts  Example Interest rates (rates of return, etc.) are entered in decimal form Enter 0.01 for 1% A formula or function always begins with =  =3/4 or =4*7 Use the ^ to raise a number to an exponent  (1.03)^5 or 71^365 To find the number of periods, use the function =nper(  =nper( To round up a number, use the function =roundup( . You may include another function inside of the roundup function.  Ts is particularly useful for expressing the number of compounding (or discounting) periods. =roundup(=nper( To find the periodic rate, use the function =rate(  =rate( To find the present value, use the function =pv(  =pv( To find the periodic payment, use the function =pmt(  =pmt( To find the future value, use the function =fv(  =fv( To sum a column or row, type =sum( and select information to be summed.  The autosum feature is an alternative. =sum( Use cell references whenever possible  If the number in cell B2 is 5, reference cell B2 by typing = and selecting cell B2, then t enter To copy the contents of one cell, hover the cursor over the box in the lower right corner until the black crosshair appears.  Left mouse click, hold, and drag. Control-C followed by Control-V also copies content.