Question

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. (3 points)
2. Write the needed formulas in the Loan Amortization Table given to calculate the interest payment and the principal payment for each period payment. (3 points for Interest Payment column, 3 points for Principle Payment column, 2 points for Loan Balance column)

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).

please post formulas, do not need charts

Answer 1

Loan Amount = $ 100000

Annual Interest rate = 7.5%

Loan term/period = 9 years

Loan annual installment = Loan Amount/PVIFA(7.5%,9Years)

Annual Installment = 100000/6.378887

= 15676.72

or you can use PMT Function in Ms excel =PMT(7.5%,9,-100000) which comes to $15676.72.

Preparation of Loan Amortization table -

Excel worksheet is attached.

please check with your answer and let me know.