Activity 3 36 Points

Activity 3
36 points
This assignment corresponds to developing loan amortization tables. There are two computer
exercises listed below. During the Discussion Section, you learn how to do Exercise 1.
Work on Exercise 2 and upload your finished work (i.e., one Excel file) on:
Blackboard => Assignments and Assessments => Activity 3-Take Home
Use the template Activity 3-Workbook in this exercise. There are two spreadsheets in the EXCEL
workbook, one for each exercise. Only spreadsheet Exercise 2 will be graded. If you have any
questions, use TAs’ office hours.
Please save your Excel file as your last name(s), Activity 3 (i.e., Yektansani Activity 3) to
Blackboard by the due date.
Exercise 1:
The example exercise is to work through a loan amortization example using Excel. Open Activity 3-
Workbook. Go to the Exercise 1 worksheet.
The example loan conditions are (enter these values under Loan Terms):
Loan amount borrowed (principal or pv) $100,000
Loan interest (rate) is 7.5%
Loan term (number of payments or nper) is 9 years
Annual payments of principal and interest
1) Calculate the annual loan payment in cell C7 using the PMT function in Excel. The PMT
function is in the formulas under the Financial menu option. In the PMT Menu box, the Rate is
the interest rate, Nper is the number of payments or term, and PV is the principal amount
borrowed (enter this as a negative value). FV and Type should be blank or you can enter 0. Use
your mouse and use the cell reference to enter the required entries.
2) Write the needed formulas in the Loan Amortization Table given to calculate the interest payment
and the principal payment for each period payment.
1st, Interest Payment: Calculate the interest payment as follows: Interest payment = period interest
rate * the outstanding loan balance. Start from Pmt Num 1 and use the loan balance of the
previous period. You need to use absolute and relative cell addresses to accomplish this task!
2nd, Principle Payment: When you make payments on a loan, part of your payment goes for
interest on the loan and part goes to pay back the loan (principle). Subtract the Interest Payment
from the Annual Loan payment (i.e., principal and interest that you calculated using PMT) to
calculate the amount paid on principal.
3rd, Loan Balance: Subtract the principal payment from the previous period outstanding balance.
In each period, the loan balance is whatever loan balance was left from the previous payment
minus principle payment. (Note: Loan Balance in period 0 is the amount borrowed).
1
4th, copy and paste the formulas for the remaining 8 payments.
5th, enter formulas to sum the totals of Interest Payments and Principle Payments in your table.
a) Calculate the total amount paid (Principal + Interest) using values in Term (cell C6) and Loan
Payment (cell C7).
b) Use the Excel IPMT formula to calculate the interest payment for payment 3 in D19. Again,
enter PV as a negative value.
c) Use the Excl PPMT formula to calculate the principal payment for payment 4 in D17.
d) Check to see if the results of a, b and c are the same as calculated by your Loan Amortization
Table.
2
Exercise 2: Home Mortgage (36 points)
Consider you just graduated college and want to purchase a house. Your dream home is a 4
bedroom, 2 bath, with 2,000 square feet house and is listed at $315,000.
Assume you have enough money to put a 5% down payment ($315,000 * 5% = $15,750). You will
qualify for a 30-year fixed mortgage of 5.05% APR. You will make monthly payments.
Fill in the Loan Terms table and make a loan amortization table for financing the house in the
Exercise 2 worksheet.
When calculating the monthly payment, the periodic interest rate and the repayment term
become:
Periodic interest rate = APR / 12
(i.e., Monthly Interest Rate is Annual Percentage Rate / 12)
Number of payments = Number of years * 12
Grading for Loan Terms table (6 points): 2 points for Borrowed, 2 points for Monthly Interest Rate, 2
points for Term in months.
for the Loan Amortization Table (9 points): 3 points for Interest Payment Column, 3 points for
Principle Payment column, 3 points for Loan Balance column
a) What is the monthly payment? (3 points)
b) How much total interest will you pay when paid in full according to the loan terms? (3 points)
c) How much principal will you pay when paid in full? (3 points)
d) How much will the house cost in total (principal + interest) when paid in full. (3 points)
e) What is the interest payment for period 12 using the excel IPMT function? (3 points)
f) What is the principal payment for period 12 using the excel PPMT function? (3 points)
g) How much money would you save in interest over the life of the loan if the interest rate was
3.25%, instead of 5.05%? (3 points)
Change cell c6 according to the new interest rate, excel automatically re-does all the
calculations. Now write down the number associated with part (b), let’s call this number X,
undo this change (so now everything goes back to the initial value), subtract X from what you
initially calculated for part (b).

Activity 3 36 Points

Uploaded by

Copyright:

Available Formats

Activity 3 36 Points

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Activity 3 36 Points

Uploaded by

Copyright:

Available Formats

Activity 3

You might also like