Q:

# How do you create an auto loan amortization spreadsheet in Excel?

A:

Use Excel's PMT function to calculate individual payments, determine the interest and principal components of each payment, and then set an amortization schedule, which deducts the principal payments from the outstanding balance, says TVM Calcs. Excel must re-calculate the interest and principal components of each subsequent payment after the first.

## Keep Learning

Excel amortization spreadsheets begin with entries for the value of the loan, the number of years the loan is for, the interest rate on the loan and the frequency of payments, says Vertex42 LLC. Those entries vary for each loan and must be entered into Excel's PMT function to calculate each payment.

The Excel payment function's syntax is "PMT(rate, nper, pv, [fv], [type])", where "rate" is the interest rate, "nper" is the number of periods in the loan and "pv" is the present value or principal of the loan, according to Microsoft. "[fv]" is an optional entry, and Excel assumes it is zero to represent a fully paid-off loan if it is not included. "[type]" is also optional, and the values of zero or one indicate payments due at the beginning or end of each period.

After calculating the initial payment and its interest and principal components based on the total loan value, use Excel to subtract the initial principal payment from that amount, adds TVM Calcs. The principal component for each subsequent payment increases and the interest component decreases with each successive payment.

Sources:

## Related Questions

• A: Free amortization software, online amortization schedule calculators or simple spreadsheet programs easily recalculate principal and interest to demonstrat... Full Answer >
Filed Under:
• A: Consumers make a home loan amortization schedule by subtracting monthly interest payments from starting balances to determine the principal paid and subtra... Full Answer >
Filed Under:
• A: An auto loam amortization chart includes the date of each scheduled payment; the amount of the payment; the amount that goes toward paying the principal an... Full Answer >
Filed Under:
• A: A personal loan agreement should cover in detail how much money is being loaned, whether any interest is being charged, and when the loan balance is due to... Full Answer >
Filed Under:
PEOPLE SEARCH FOR