Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Approximate match with multiple criteria in Excel
  • How to get address of named range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel FORMULATEXT function
  • Last row number in range

Data Analysis Examples

  • How to add Trendline to a chart in Excel
  • Add Outline to Data in Excel
  • How to count table rows in Excel
  • How To Compare Two Lists in Excel
  • Understanding Anova in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation must not contain
  • Data validation must not exist in list
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only

How To Create Loan Amortization Schedule in Excel

by

This example teaches you how to create a loan amortization schedule in Excel.

1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. We have named the input cells.

2. Use the PPMT function to calculate the principal part of the payment. The second argument specifies the payment number.

3. Use the IPMT function to calculate the interest part of the payment. The second argument specifies the payment number.

4. Update the balance.

5. Select the range A7:E7 (first payment) and drag it down one row. Change the balance formula.

6. Select the range A8:E8 (second payment) and drag it down to row 30.

It takes 24 months to pay off this loan. See how the principal part increases and the interest part decreases with each payment.

Post navigation

Previous Post:

Find Percentage Change Between Numbers

Next Post:

Lock Cells in a Worksheet Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check cell contains which things in Excel
  • How to count number of characters of text in a cell in Excel
  • How to extract nth word from text string in excel
  • How to check if cell contains number in Excel
  • How to count total characters in a range in Excel
  • Excel Date & Time Functions Example
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Convert text timestamp into time in Excel
  • Two ways to sum time over 30 minutes in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • TBILLEQ function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles