Q:

How can a person create an amortization table in Excel?

A:

Quick Answer

An amortization table in Excel can be created using the PMT, PPMT and IPMT functions. The table reflects the amount of each payment, the portions that go toward principal or interest and the outstanding balance.

Continue Reading

Full Answer

An amortization table is used to outline the details of a long-term loan, such as a mortgage or auto loan. It displays each payment made toward paying off the loan.

Step 1: Create the necessary rows

The rows should be labeled "annual interest rate," "years," "payments per year," "amount of loan" and "payment amount." The next column over should have all the applicable figures next to these cells.

Step 2: Calculate the monthly payments

Use the PMT Excel function to calculate the payments. This function takes into account the interest rate, the total number of payments, the present value and the future value of the loan.

Step 3: Calculate how the payment will be applied

Use the PPMT function and the IPMT function to figure out how much of each payment will go toward principal, and how much toward the interest. After subtracting both from the outstanding amount, the balance should be updated.

Step 4: Outline the rest of the payments

Select the cells reflecting the first payment amounts and balance and drag it down one row. Update the formula to reflect the data for the second payment. Repeat for the remaining number of payments, as necessary, until the balance reaches zero.

Learn more about Software

Related Questions

  • Q:

    How do you round numbers in Excel?

    A:

    Though there are several functions in Excel for rounding numbers, the ROUND function is the most basic and the most commonly used. Keep in mind that when using any rounding function, its result is always treated as rounded by any formulas that reference it and can potentially alter calculations.

    Full Answer >
    Filed Under:
  • Q:

    What are some tips for passing an Excel test?

    A:

    Tips for passing an Excel test include reviewing concepts and functions through the program's support page on Office.com, taking practice tests to learn about the different topics that may appear and using online courses to focus on specific areas of the program. It may also help to purchase a book or study guide to focus on a specific version of the software.

    Full Answer >
    Filed Under:
  • Q:

    What are some common functions in Microsoft Excel?

    A:

    Some common Microsoft Excel functions include SUM, IF, LOOKUP, VLOOKUP and MATCH, as of the Excel 2013 version. SUM adds the values in selected cells, IF returns a value based on true-or-false conditions and LOOKUP finds a value from a row or column based on a selected row or column.

    Full Answer >
    Filed Under:
  • Q:

    Does the "Excel for Dummies" book provide spreadsheet examples?

    A:

    The "Excel 2013 for Dummies" book includes examples of spreadsheets for users to familiarize themselves with worksheet creation and editing functions. The book does not contain an exhaustive list of examples, but it provides the reader with skills to interpret data and create their own spreadsheets and databases.

    Full Answer >
    Filed Under:

Explore