Question

on a loan worksheet in cell c9 

enter a PMT function to calculate the monthly payment for the Altamonte Springs 2018 facilities loan. Ensure that the function returns a positive value and set the references to cells B5 and B6 as absolute references.

Answer 1

The value of the cell C9 can be filled using PMT function as follows:

The PMT function can be used to return a periodic payment for a loan. Its syntax is wriiten as follows:

=PMT(rate, nper, pv, [fv], [type])

The arguments of this function can be explained as follows:

Rate: The interest rate applied on a loan.

Nper: It is the total number of payments for a loan.

Pv: It is present value of the loan payments.

Fv: It is an optional argument used for future value after the last payment. It is 0 by default.

Type: It is also optional. Its value will be 0 at the end of period, 1 at the beginning of period and 0 by default.

The monthly payment in the cell C9 for Altamonte Springs can be computed by the formula listed as follows:

C9 =PMT($B$5/12, $B$6, -B9)

Explanation:

• The annual interest rate on loan in the cell B5 will be divided by 12 to get monthly interest rate.
• Second argument is the number of payments in the cell B6.
• The third argument is current facilities cost for Altamonte Springs in cell B9.
• The cell B5 and B6 are absolutely referenced as when dragging the formula from cell C10 to C15, the row and column positions for B5 and B6 are fixed.

The required steps to show complete formation of excel sheet by filling the values in column C (monthly payment) are shown as follows:

Step 1: Create an excel sheet as same as given in the question as follows:

Step 2: Select the column C and right click it, select formatting cells. Select currency under the Number tab in the pop up window open. Make sure that the field decimal places is set to 2. Click ok button and the required excel sheets in this step are shown as follow:

Step 3: Select cell C9 and enter the required PMT function. Click enter and the required excel sheet will look like as follows:

Step 4: Drag the value in cell C9 to the range C10:C15 and the required excel sheet will look like as follows: