Question

1. In cell F5, enter a formula using the PMT function. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount. Use defined names for the rate, nper, and pv arguments as follows:
   · rate argument: Divide the Rate by 12 to use the monthly interest rate.
   · nper argument: Multiply the Term by 12 to specify the number of months as the periods.
   · pv argument: Use the Loan_Amount as the present value of the loan.

2.In cell F6, enter a formula without using a function that multiplies 12 by the Term and the Monthly_Payment, and then subtracts the Loan_Amount to determine the total interest

3. in cell J5, enter another formula using the PV function. Use defined cell names for the rate, nper, and pmt arguments as follows:
· rate argument: Divide the Rate by 12 to use the monthly interest rate.
· nper argument: Subtract the year value in cell H5 from the Term, and then multiply the result by 12 to specify the number of months remaining to pay off the loan.
· pmt argument: Use the Monthly_Payment as a negative value to specify the payment amount per period.

Answer 1

Open MS-Office Excel

(a.)

Define name for rate, nper and pv cells as Rate, Term and Loan_Payment.

Select the cell then Menu > Formulas > Define Name.

for example : F1 cell is Rate, F2 cell is Term, F3 cell is Loan_Amount and F4 cell is Monthly_Payment.

.

Nextly, click on F5 cell and click on Fx above column headings. Make sure all is selected in category then select PMT function from the scoll down list.

In new window that pops up, Enter the Rate/12, Term*12 , Loan_Amount and press OK.

(b.)

Click on cell F6, and type " = 12*Term*Monthly_payment-Loan_Amount " and press Enter.

(c.)

Define name Year in cell H5. After that click on cell J5 and click Fx above column headings. Make sure all is selected in category then select PV function from the scoll down list.

In new window that pops up, Enter the Rate/12, (Year-Term)*12 , -Monthly_payment and press OK.

Done!