To create a loan amortization sheet using home software, such as Microsoft Excel, set up a loan data table in a new spreadsheet, calculate monthly payment mortgage, enter columns labels for the amortization table, and calculate the loan data amounts. OpenOffice Calc software can also work in this process.Continue Reading
The first column, A, has four cells with labels that include the loan amount, terms in months, interest rate and payment, according to SF Gate. The second column, B, details information corresponding to the labels, with the loan amount and payment set up in currency, and the interest rates put up in percentages. To calculate the monthly mortgage payment for cell B4, enter a PMT function such as "=PMT(B2/12,B3,-B1)" in cell B4, where loan amount, rate and term run from cell B1 through B3.
In the sixth row of the spread sheet, name the cells A through E as follows: payment number, principal, interest, total interest paid and loan balance, explains SF Gate. Use the cell autofill feature to insert payment numbers in column A, starting with cell A7. Key in "1" in cell A7 and use the mouse to auto fill the rest of the cells in this column, which is the payment number column.
To calculate the principal and interest amounts, enter "=PPMT($B$2/12,A7;$B$3,-$B$1)" and "=IPMT($B$2/12,A7,$B$3,-$B$1)" in cells B7 and C7, respectively, according to SF Gate. Enter "=B1-C7" in column D7 to calculate the loan balance. Auto fill the formula from cell E8 into the other cells in the total interest paid column.Learn more about Financial Calculations