How to Prepare Amortization Schedule in Excel (with Pictures) (2024)

  • Categories
  • Computers and Electronics
  • Software
  • Office
  • Spreadsheets
  • Microsoft Excel

Download Article

Written byAlex Kwan|Edited byNicole Levine, MFA

Last Updated: August 11, 2022Tested

Download Article

  • Creating an Amortization Schedule Manually
  • |
  • Using an Excel Template
  • |
  • Video
  • |
  • |
  • Tips
  • |
  • Warnings
  • |
  • Things You'll Need

An amortization schedule shows the interest applied to a fixed interest loan and how the principal is reduced by payments. It also shows the detailed schedule of all payments so you can see how much is going toward principal and how much is being paid toward interest charges. This wikiHow teaches you how to create your own amortization schedule in Microsoft Excel.

Method 1

Method 1 of 2:

Creating an Amortization Schedule Manually

Download Article

  1. 1

    Open a new spreadsheet in Microsoft Excel.

  2. 2

    Create labels in column A. Create labels for your data in the first column to keep things organized. Here's what you should put in each cell: .

    • A1: Loan Amount
    • A2: Interest Rate
    • A3: Months
    • A4: Payments

    Advertisem*nt

  3. 3

    Enter the information pertaining to your loan in column B. Fill out cells B1-B3 with information about your loan. Leave B4 (the cell next to the Payments label) blank.

    • The "Months" value should be the total number of months in the loan term. For example, if you have a 2-year loan, enter 24.
    • The "Interest Rate" value should be a percentage (e.g., 8.2%).
  4. 4

    Calculate your payment in cell B4. To do this, click cell B4, and then type the following formula into the formula (fx) bar at the top of the sheet and then press Enter or Return: =ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2).

    • The dollar signs in the formula are absolute references to make sure the formula will always look to those specific cells, even if it is copied elsewhere into the worksheet.
    • The loan interest rate must be divided by 12, since it is an annual rate that is calculated monthly.
    • For example, if your loan is for $150,000 at 6 percent interest for 30 years (360 months), your loan payment will calculate out to $899.33.
  5. 5

    Create column headers in row 7. You'll be adding some additional data to the sheet, which requires a second chart area. Enter the following labels into the cells:

    • A7: Period
    • B7: Beginning Balance
    • C7: Payment
    • D7: Principal
    • E7: Interest
    • F7: Cumulative Principal
    • G7: Cumulative Interest
    • H7: Ending Balance.
  6. 6

    Populate the Period column. This column will contain your payment dates. Here's what to do:

    • Type the month and year of the first loan payment in cell A8. You may need to format the column to show the month and year correctly.
    • Click the cell once to select it.
    • Drag down from the center of the selected cell downward to cover all cells through A367. If this doesn't make all of the cells reflect the correct monthly payment dates, click the small icon with a lightning bolt on it at the bottom-right corner of the bottommost cell and make sure the Last Month option is selected.
  7. 7

    Fill out the other entries in cells B8 through H8.

    • The beginning balance of your loan into cell B8.
    • In cell C8, type =$B$4 and press Enter or Return.
    • In cell E8, create a formula to calculate the loan interest amount on the beginning balance for that period. The formula will look like =ROUND($B8*($B$2/12), 2). The single dollar sign creates a relative reference. The formula will look for the appropriate cell in the B column.
    • In cell D8, subtract the loan interest amount in cell E8 from the total payment in C8. Use relative references so this cell will copy correctly. The formula will look like =$C8-$E8.
    • In cell H8, create a formula to subtract the principal portion of the payment from the beginning balance for that period. The formula will look like =$B8-$D8.
  8. 8

    Continue the schedule by creating the entries in B9 through H9.

    • Cell B9 should include a relative reference to the ending balance of the prior period. Type =$H8 into B9 and press Enter or Return.
    • Copy cells C8, D8 and E8 and paste them into C9, D9 and E9 (respectively)
    • Copy H8 and paste it into H9. This is where the relative reference becomes helpful.
    • In cell F9, create a formula to tabulate cumulative principal paid. The formula will look like this: =$D9+$F8.
    • Enter the cumulative interest formula into G9 like this: =$E9+$G8.
  9. 9

    Highlight cells B9 through H9. When you rest the mouse cursor over the bottom-right part of the highlighted area, the cursor will turn to a crosshair.

  10. 10

    Drag the crosshair all the way down to row 367. This populates all the cells through row 367 with the amortization schedule.

    • If this looks funny, click the small spreadsheet-looking icon at the bottom-right corner of the final cell and select Copy Cells.
  11. Advertisem*nt

Method 2

Method 2 of 2:

Using an Excel Template

Download Article

  1. 1

    Go to https://templates.office.com/en-us/loan-amortization-schedule-tm03986974. This is a free, downloadable amortization schedule template that makes it easy to calculate the total interest and total payments. It even includes the option to add extra payments.[1]

  2. 2

    Click Download. This saves the template to your computer in the Excel template format (XLTX).

  3. 3

    Double-click the downloaded file. It's called tf03986974.xltx, and you'll usually find it in your Downloads folder. This opens the template in Microsoft Excel.

    • The data in the template is there as an example—you'll be able to add your own data.
    • If prompted, click Enable Editing so you can make changes to the workbook.
  4. 4

    Type the loan amount into the "Loan Amount" cell. It's in the "ENTER VALUES" section near the top-left corner of the sheet. To type it, just click the existing value ($5000) and type your own amount.

    • When you press Return or Enter (or click another cell), the amounts in the rest of the sheet will recalculate. This will happen each time you change a value in this section.
  5. 5

    Enter your annual interest rate. This goes into the "Annual interest rate" cell.

  6. 6

    Enter the duration of your loan (in years). This goes into the "Loan period in years" cell.

  7. 7

    Enter the number of payments you make per year. For example, if you make payments once per month, type 12 into the "Number of payments per year" cell.

  8. 8

    Enter the loan start date. This goes into the "Start date of loan" cell.

  9. 9

    Enter a value for "Optional extra payments." If you pay over the minimum amount due on your loan each pay period, enter that extra amount into this cell. If not, change the default value to 0 (zero).

  10. 10

    Enter the name of the loan issuer. The default value of the "LENDER NAME" blank is "Woodgrove Bank." Change this to your bank's name for your own reference.

  11. 11

    Save the worksheet as a new Excel file. Here's how:

    • Click the File menu at the top-left and select Save As.
    • Select a location on your computer or in the cloud where you'd like to store your schedule.
    • Enter a name for the file. If the file type is not already set to "Excel Workbook (*.xlsx)," select that option from the drop-down menu (below the file name) now.
    • Click Save.
  12. Advertisem*nt

Community Q&A

Search

Add New Question

  • Question

    How can I change the currency?

    How to Prepare Amortization Schedule in Excel (with Pictures) (26)

    Community Answer

    The currency is dependent on the format of the cell with dollar amounts in them. Right click on the cell you want to change and click on "Format." Then choose "Currency" and put in the appropriate codes for the currency you choose.

    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support wikiHow

    YesNo

    Not Helpful 3Helpful 14

  • Question

    How do I do a weekly payment loan?

    How to Prepare Amortization Schedule in Excel (with Pictures) (27)

    Community Answer

    You have to change the periods. Try this: Change "Months" in cell A3 to "Periods." Change the "Payments" formula in cell B4, changing the "12" (which represents months) to "52" which represents the number of weeks in a year. The new formula will look like this: "=ROUND(PMT($B$2/52,$B$3,-$B$8,0), 2)." You'll also need to change your periods starting in cell A9 to add 7 days instead of 1 month. I would use this formula in cell A9: "=DATE(YEAR(A8),MONTH(A8),DAY(A8)+7."Lastly, copy your new formula down to the rest of the schedule so all the periods will be correct.

    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support wikiHow

    YesNo

  • Question

    How do I change the formula for quarterly payments rather than monthly payments?

    How to Prepare Amortization Schedule in Excel (with Pictures) (28)

    Community Answer

    Wherever there is a field being populated with the =ROUND(...) formula, replace the "12" entry with a "4." Example: In cell E8, create a formula to calculate the loan interest amount on the beginning balance for that period. The formula will look like "=ROUND($B8*($B$2/4), 2)"

    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support wikiHow

    YesNo

    Not Helpful 3Helpful 12

See more answers

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

      Advertisem*nt

      Video

      Tips

      • If you do not receive a final ending balance of $0.00, make sure you have used the absolute and relative references as instructed and the cells have been copied correctly.

        Thanks

        Helpful7Not Helpful2

      • You can now scroll to any period during the loan payment to see how much of the payment is applied to the principal, how much is charged as loan interest and how much principal and interest you have paid to date.

        Thanks

        Helpful8Not Helpful3

      Submit a Tip

      All tip submissions are carefully reviewed before being published

      Submit

      Thanks for submitting a tip for review!

      Advertisem*nt

      Warnings

      • This will only work for home loans that calculate monthly. If the loan is a car loan or a daily compounded loan, this will only give rough estimates for interest paid.

        Thanks

        Helpful0Not Helpful0

      Advertisem*nt

      Things You'll Need

      • Computer
      • Microsoft Excel
      • Loan details

      You Might Also Like

      How to Make a Line Graph From Data in Microsoft Excel4 Easy Ways to Add Links in Microsoft Excel
      How toRecover a Corrupt Excel FileHow to Merge Cells in Microsoft Excel: A Quick GuideHow to Use If‐Else in Microsoft Excel: Step-by-Step TutorialHow toUnprotect an Excel SheetHow to Fix Ethernet Doesn't Have a Valid IP Configuration on WindowsHow toLink Sheets in ExcelHow toMake a Spreadsheet in ExcelHow toCreate a Mortgage Calculator With Microsoft ExcelHow toInsert Pictures in Excel That Automatically Size to Fit CellsHow to Create a Graph in ExcelHow to Password Protect a Microsoft Excel Spreadsheet

      Advertisem*nt

      About This Article

      How to Prepare Amortization Schedule in Excel (with Pictures) (44)

      Written by:

      Alex Kwan

      Certified Public Accountant

      This article was written by Alex Kwan and by wikiHow staff writer, Nicole Levine, MFA. Alex Kwan is a Certified Public Accountant (CPA) and the CEO of Flex Tax and Consulting Group in the San Francisco Bay Area. He has also served as a Vice President for one of the top five Private Equity Firms. With over a decade of experience practicing public accounting, he specializes in client-centered accounting and consulting, R&D tax services, and the small business sector. This article has been viewed 1,190,549 times.

      How helpful is this?

      Co-authors: 11

      Updated: August 11, 2022

      Views:1,190,549

      Categories: Microsoft Excel

      Article SummaryX

      1.Download the amortization schedule from Microsoft.
      2.Open the template in Excel.
      3.Fill out your loan data in the "ENTER VALUES" section.
      4.Type the bank name as the "LENDER NAME."
      5.Save the worksheet as an Excel file.

      Did this summary help you?

      In other languages

      Spanish

      Portuguese

      Russian

      Indonesian

      French

      Dutch

      Chinese

      • Print
      • Send fan mail to authors

      Thanks to all authors for creating a page that has been read 1,190,549 times.

      Reader Success Stories

      • How to Prepare Amortization Schedule in Excel (with Pictures) (45)

        John Lowndes

        Oct 27, 2016

        "I work at a CPA firm, and had to do a quick amortization schedule for a client out of Excel. I've done one a..." more

      More reader storiesHide reader stories

      Is this article up to date?

      Advertisem*nt

      How to Prepare Amortization Schedule in Excel (with Pictures) (2024)
      Top Articles
      Latest Posts
      Article information

      Author: Patricia Veum II

      Last Updated:

      Views: 6018

      Rating: 4.3 / 5 (44 voted)

      Reviews: 83% of readers found this page helpful

      Author information

      Name: Patricia Veum II

      Birthday: 1994-12-16

      Address: 2064 Little Summit, Goldieton, MS 97651-0862

      Phone: +6873952696715

      Job: Principal Officer

      Hobby: Rafting, Cabaret, Candle making, Jigsaw puzzles, Inline skating, Magic, Graffiti

      Introduction: My name is Patricia Veum II, I am a vast, combative, smiling, famous, inexpensive, zealous, sparkling person who loves writing and wants to share my knowledge and understanding with you.