Question

The following spreadsheet can be used to compute monthly payments given the apr, principal, and term of the loan.

A B C D E
1 Principal in dollars Interest Rate as a Decimal Time in Years Time in Months Monthly Payment
2 11,000 0.03 4 c f
3 900 0.027 0.5 d g
4 2,500 0.034 2 e h

a. write the spreadsheet formula to compute cell d2.

Answer 1

The formula for cell D2 is =PMT(B2/12,C2*12,-A2).

In the provided spreadsheet, cell D2 is intended to calculate the monthly payment for a loan based on the principal amount, interest rate, and loan term. The formula used for cell D2 is =PMT(B2/12, C2*12, -A2).

Here's a breakdown of the formula:

B2/12: This part of the formula divides the annual interest rate in cell B2 by 12 to convert it into a monthly interest rate.

C2*12: The loan term in years (cell C2) is multiplied by 12 to convert it into the total number of monthly payments.

-A2: The principal amount (cell A2) is negated (multiplied by -1) because payments are typically considered as outgoing cash flows.

PMT(B2/12, C2*12, -A2): This function, PMT, calculates the monthly payment for a loan based on the provided parameters: the monthly interest rate, the total number of payments, and the principal amount.