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.
Continue ReadingExcel 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.
Learn more about Software