Question

Exercise 1:

The example exercise is to work through a loan amortization example using Excel. Open Activity 3-Workbook. Go to 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.

1^st, 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!

2^nd, 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.

3^rd, 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).

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

5^th, enter formulas to sum the totals of Interest Payments and Principle Payments in your table.

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

Answer 1

This is what we get using the PMT function in Excel

Loan / PV	1,00,000
Term / Periods	9
Rate (of interest)	7.50%
Annual repayment	15,677	PMT(Rate, Term, PV)

This is the table showing year wise interest and payment amortization using detailed calculations:

Time	Loan Amount	Annual Repayment	Interest Portion	Principal Portion	Balance Loan
0	1,00,000				1,00,000
1		15,677	7,500	8,177	91,823
2		15,677	6,887	8,790	83,033
3		15,677	6,227	9,449	73,584
4		15,677	5,519	10,158	63,426
5		15,677	4,757	10,920	52,506
6		15,677	3,938	11,739	40,768
7		15,677	3,058	12,619	28,149
8		15,677	2,111	13,566	14,583
9		15,677	1,094	14,583	-
Totals	1,00,000	1,41,090	41,090	1,00,000

b. IPMT for payment 3 gives the value: 6,227, which is exactly equal to what we see in the interest portion field against year 3 in the table above

c. PPMT for payment 4 gives the value 10,158, which is exactly equal to the value in the principal portion field against year 4 in the table above

d. Yes, all the values are matching