# Fina 314: business finance excel assignment spring 2017 50

Fina 314: business finance excel assignment spring 2017 50.

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, this 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 this 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 Hints 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. This 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 hit 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.