Part 1: The formula to calculate the monthly payment, given loan L, term T and annual interest rate R is: LXR 12 R 1 1+_ 12XT ( 12) Excel also provides a PMT function to calculate the monthly payment for a loan. Its syntax is: PMT(rate, nper, pv) where Monthlypayment 2 Rate: The interest rate for the loan. For calculating the monthly payment, the annual interest rate is converted to monthly rate (dividing by 12). Nper: The total number of payments for the loan (Term *12). Pv: The principal. Prepare a monthly payment table for loans as shown below, rst using the formula and then use the PMT function. Your spreadsheet should allow user to change the term and the interest rate. It H Term 30 AnnualRate 6% Monthly Payment Loan Using formula Using PMT function Inblcn'ul'h 'w'M'II E g 8 Part 2: Use Excel to prepare an amortization table (see the example below) for a loan. There are three inputs to this program: Loan amount, Annual Interest Rate, and Terms. Use the payment function PMT to compute the monthly payment. The amount to interest column is calculated by multiplying the previous month's remaining balance by the monthly rate (annual rate! 12). Amount to principal is Payment Amount to Interest. Remaining Balance is previous month's remaining balance amount to principal. The table should be properly formatted. Test your worksheet with two sets of data: 1. Loan = 5000, rate = 12%, term = 1 year. 2. Loan = 10000, rate = 9%, term = 1.5 years (18 payments) Amortization Table Loan $5,000.00 Rate 12% Term{Year) 1 Payment Monthly Amount to Amount to Remaining Number Payment Interest Principal Balance 0 $5,000.00 1 $444.24 $50.00 $394.24 $4,505.75 2 $444.24 $45.05 $393.19 $4,207.57 3 $444.24 $42.03 $402.17 $3,305.40 4 $444.24 $33.05 $405.19 $3,399.21 5 $444.24 $33.99 $410.25 $2,933.95 5 5444.24 $29.39 $414.35 $2,574.51 7 $444.24 $25.75 $413.50 $2,155.11 3 $444.24 $21.55 $422.53 $1,733.42 9 $444.24 $17.33 $425.91 $1,305.51 10 $444.24 $13.07 $431.13 $375.34 P H 3.: h $3.75 $435.49 $439.35 $4.40. $439.35 $0.00 .4 M is: h