Exercise 1:

The example exercise is to work through a loan amortizationexample using Excel. Open Activity3-Workbook. Go to Exercise 1 worksheet.

The example loan conditions are (enter these values under LoanTerms):

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 PMTfunction in Excel. The PMT function is in the formulas underthe Financial menu option. In the PMT Menu box, theRate is the interest rate, nperis the number of payments or term, and PV is theprincipal amount borrowed (enter this as a negative value). FV and Type should be blank or you can enter0. Use your mouse and use the cell reference to enter therequired entries.
2. Write the needed formulas in the Loan Amortization Table givento calculate the interest payment and the principal payment foreach period payment.

1^st, Interest Payment:Calculate the interest payment as follows: Interest payment =period interest rate * the outstanding loan balance. Start from PmtNum 1 and use the loan balance of the previousperiod. You need to use absolute and relative cell addresses toaccomplish this task!

2^nd, Principle Payment:When you make payments on a loan, part of your payment goes forinterest on the loan and part goes to pay back the loan(principle). Subtract the Interest Payment from the Annual Loanpayment (i.e., principal and interest that you calculated usingPMT) to calculate the amount paid on principal.

3^rd, Loan Balance: Subtractthe principal payment from the previous period outstandingbalance.
In each period, the loan balance is whatever loan balance was leftfrom the previous payment minus principle payment. (Note: LoanBalance in period 0 is the amount borrowed).

4^th, copy and paste theformulas for the remaining 8 payments.

5^th, enter formulas to sumthe totals of Interest Payments and Principle Payments in yourtable.

1. Calculate the total amount paid (Principal + Interest) usingvalues in Term (cell C6) and Loan Payment (cell C7).
2. Use the Excel IPMT formula to calculate the interest paymentfor payment 3 in D19. Again, enter PV as a negative value.
3. Use the Excl PPMT formula to calculate the principal paymentfor payment 4 in D17.
4. Check to see if the results of a, b and c are the same ascalculated by your Loan Amortization Table.