Question

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

To calculate the monthly payment, total interest, and present value, one would use the PMT and PV functions with appropriate formulas, incorporating the loan's interest rate, term, and amount into the respective calculations.

Step-by-step explanation:

To answer the first question, you would enter the following formula in cell F5 to calculate the monthly payment using the PMT function:

=-PMT(Rate/12, Term*12, Loan_Amount)

For the loan specified, assuming Rate is 6%, Term is 30 years, and Loan_Amount is $300,000, the PMT function will calculate the payment per month.

To answer the second question, in cell F6, the formula to calculate the total interest paid over the loan period without using a function would be:

=12*Term*Monthly_Payment - Loan_Amount

Given the Term is 30 years, Monthly_Payment is the result from F5, and Loan_Amount is $300,000, this formula gives you the total interest paid over the life of the loan.

Finally, to answer the third question, in cell J5 you would enter the following formula using the PV function:

=PV(Rate/12, (Term - Year_H5)*12, -Monthly_Payment)

Here, Rate is still 6%, Term is 30 years, Monthly_Payment would be the same as before but entered as a negative because it's a payment, and Year_H5 refers to the value in cell H5 which indicates the number of years already paid on the loan.

Answer 2

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