Installment Loan Formula

An installment loan formula needs four things---how much borrowed, how much interest, how many payments a year and how long to pay back the loan. The formula is cumbersome but easy to understand in a spreadsheet. In print it reads: Payment = (Loan amount x Interest) ÷ (Payments per Year x (1 -- (1 + (Interest) ÷ Payments per Year)) raised to the power of negative Payments per Year x Length of Loan))).

Spreadsheet Setup

Create a simple spreadsheet. Label the columns in the first row as follows: A1 = "Payment"; B1 = "Loan Amount"; C1 = "Interest"; D1 = "Payments/Year" and E1 = "Length of Loan". Do not include the quotation marks.

Payment Calculation

Enter in Column A, Row 3, "= (B3*C3)/D3*(1-(1+(C3/D3))^(D3*E3)))". Do not include the quotation marks.

Values Required

Enter values in cells B1 through E1. For a loan amount of $1,000.00 with interest of 5 percent to be repaid monthly in 3 years put: B1 = 1000, C1 = 0.05, D1 = 12, and E1 = 3.

Result

In cell A3, $29.97 should appear as the monthly payment. Format this number as currency as well as the loan amount and the interest as a percent to improve the display.

Added Formulas

The total amount to be repaid is calculated by: Payments x Payments per Year x Length of Loan. The interest paid is this value minus the loan amount. Formulas and results are shown in the image example.