MAT 119 Finite Mathematics FINANCE PROJECT

Subject: Business / Finance
You are required to work in a team of 1 to 3 students. Please submit one report for each team.
Project will be evaluated on (a) content and completeness (b) clarity of report and neatness.
Please answer all the questions and show your work. If you are not sure what to do with any
question, do not hesitate to ask. Feel free to add anything that could earn you extra credit.
TASK 1: EXCEL SPREADSHEET TUTORIAL
Excel spreadsheet is a convenient tool for entering data on a sheet and performing calculations
and drawings graphs from the data. The following is how to get started with Excel.
1. Long on to the computer and open Excel by double clicking on the Excel icon
2. The Excel spreadsheet consists of columns A, B, C, D,…, and rows 1, 2, 3, 4,… You can
also name columns or rows using the first row or column. You will find tool bars on the
top of the screen that can be used to assist in working with the spreadsheet. The “Help”
command is quite useful when learning Excel.
3. The boxes that are at the intersection of each row and column are called CELLS. When
you move the mouse around, different cells are highlighted. When a cell is highlighted, it
is made active and data can be entered into the cell. Another way to make cells active is
by moving the cursor keys.
4. The following table gives the cost of five cars from a car dealer. Use Excel spreadsheet
to calculate the total cost of each car including the sales tax and the license fee.
A
1
2
3
4
5
6 Cost
Tax@8.5%
License B
C
D
E
Nissan
Ford
Chevy
Toyota
13225.00 14554.00 10580.00 12205.00
115.00 130.00 80.00 105.00 F
Jeep
14587.00
130.00 Total 5. Start by entering Nissan in cell B1, Ford in cell C1,…To calculate the sales tax for Nissan
type =B2*0.085 in cell B3 and then press enter. Type = SUM(B2:B4) in cell B6 to add
the content of column B from cells B2 to B4. Press enter to get the total cost of Nissan in
cell B6. To write your answer to two digits after the decimal point select the answer , then
click Format, then Cells, then Number and make your selection.
6. Do the same to find the taxes of the other cars in cells C3, D3, E3 and F3. You can use a
shortcut to do this as follows: Move your active cell to B3. Move your cursor to the lower
right hand corner of this cell to a small box in the corner. When directly over the small
box the mouse pointer changes shape to a +. Now click the left mouse button and drag
the small box across cells C3, D3, E3 and F3. Release the button when at F3. Excel has
automatically copied the formulas from cell B3 to cells C3, D3, E3 and F3! 7. Apply the same technique to find the totals in C6, D6, E6 and F6. To get the borders
select the required region, then click Format, then Cells, then Border and make your
selection. TASK 2: Finding Future Value of Annuity
Suppose Jason plans to put $500 into a savings at the end of each year for 5 years at 8%
compounded annually.
(a)The following table shows how each $500 amount earns interest compounded annually. In cell
D5 enter =500*(1+0.08)^4 and in D6 enter =500*(1+0.08)^3. Complete the * parts of the table:
A B
C
Amount
Number of
1 Payment
of
years
2
At 8%
3 Number Payment
interest
4
5
1
500.00
4
6
2
500.00
3
7
3
500.00
2
8
4
500.00
1
9
5
500.00
0
10 TOTAL D
Accumulated
value using
Compound
Interest Formula
680.24
629.86
*
*
*
* (b) What is your total accumulated value in D10?
(c) Use the Savings Formula by hand to find the accumulated value in Jason’s account after 5
years. Show your work. Compare your answer with that in part (b). The two answers should be
the same. TASK 3: Creating Amortization Schedule Using Excel Spreadsheet
Suppose Joe Nash borrows $5000 at 12% compounded monthly, to begin a new business.
Construct an amortization schedule for this loan if he agrees to pay back in three months as
follows: (a) Use the amortization formula to calculate Joe’s monthly payment and round the payment
up.
(b) Create an amortization schedule table for the loan. At the beginning of the table, enter
the following information: title, name of client, loan amount, length of loan, annual
interest rate, interest rate per month and monthly payment. The table should have the
column headers: Payment Number, Beginning Balance, Payment, Interest Rate Per
Month, Interest Payment, Principal Payment and Ending Balance. The results are:
Using the amortization formula, the monthly payment is found to be $1700.110557. We round
this up to $1700.12. In the table, in E4 enter =B4*D4. In F4 enter =C4-E4. Enter =B4-F4 in G4,
etc. We have:
Amortization Schedule
Client:
Joe Nash
Loan Amount: $5,000
Length of Loan: three months
Annual Percentage Rate: 0.12
Interest Rate Per Month: 0.01 1
2
3
4
5
6 A B Payment
Number
0
1
2
3 Beginning
Balance
0.00
5000.00
3349.88
1683.26 C
Amount
of
Payment
0.00
1700.12
1700.12
1700.12 D E F G Interest Rate
/ Month
0.01
0.01
0.01
0.01 Interest
Payment
0.00
50.00
33.50
16.83 Principal
Payment
0.00
1650.12
1666.62
1683..29 Ending
Balance
5000.00
3349.88
1683.26
-0.03 TASK 4: Creating Amortization Schedule Using Excel Spreadsheet
Problem: You and your friends (group members) have decided to take your friendship to a new
level by buying a house together.
(a) Each member should find a job in their field of interest and use relevant statistics
pertaining to their line of work to determine a yearly income.
(b) Determine a set of per individual constraints on each member’s monthly contribution
towards a new house payment plan. Please take into account medical expenses, food,
hobbies, etc. The total dollar amount allotted towards a monthly payment will determine
the price range of the house (i.e. Determine the maximum loan amount).
(c) Select a house (using the internet) to purchase. Include all relevant data on the desired
house in the report (including a picture).
(d) Determine the monthly payment on the loan for the house assuming a 10% per annum
interest rate and a loan period of 25 years. You are required to create an amortization schedule for the above loan. (c) Create an amortization schedule for the loan. At the beginning of the table, enter the
following information: title, name of client, loan amount, length of loan, annual interest
rate, interest rate per month and monthly payment. The table should have the column
headers: Payment Number, Beginning Balance, Payment, Interest Rate Per Month,
Interest Payment, Principal Payment and Ending Balance.
Questions: Use the amortization schedule to answer the following questions:
(1) What is the balance of the loan after 3 years of payments?
(2) Of the total amount paid for the house, how much was interest?