How to schedule installment payments in Excel

Author: Peter Berry
Date Of Creation: 14 February 2021
Update Date: 1 July 2024
Anonim
How to build an Amortization table in EXCEL (Fast and easy) Less than 5 minutes
Video: How to build an Amortization table in EXCEL (Fast and easy) Less than 5 minutes

Content

The amortization schedule (or repayment schedule) shows the interest charged on a fixed loan and the process of reducing principal through payments. The schedule provides a detailed overview of all payments so you will know the amount will be repaid to the principal and how much will go into interest. Moreover, the repayment schedule can be easily scheduled in Microsoft Excel. Start with step 1 below to create a schedule to pay close at home with no rental fees!

Steps

  1. Launch Microsoft Excel and open a new spreadsheet.

  2. Name cells A1 down A4 as follows: Loan Amount, Interest, Month and Payment.
  3. Enter the corresponding data in cells B1 through B3.

  4. Enter your loan interest rate as a percentage.
  5. Calculate the payment in cell B4 by entering "= ROUND (PMT ($ B $ 2/12, $ B $ 3, - $ B $ 1.0), 2)" in the formula bar (without the quotes) and pressing Enter.
    • The dollar symbols in this formula are an absolute reference to ensure that the formula will always pull data from specific cells even if they are copied anywhere in the spreadsheet.
    • Interest must be divided by 12 since this is the annual and monthly rate.
    • For example, if you borrow 3,450,000,000 VND at 6% interest paid for 30 years (360 months), your first payment will be 20,684,590 VND.

  6. Name the columns A6 through H7 as follows: Term, Opening balance, Payment, Principal, Interest, Principal accrued, Interest accrued and Closing balance.
  7. Fill in the Deadline column.
    • Enter the month and year of your first payment in cell A8. You may need a format to make the column display the correct date.
    • Select the cell, click and drag it down to fill cell A367. You need to make sure that the Auto Fill option is set to "Fill Months".
  8. Fill in the remaining information in boxes B8 through H8.
    • Enter your loan's opening balance in cell B8.
    • Type "= $ B $ 4" in cell C8 and press "Enter".
    • Create the formula to calculate the interest for the opening balance for that period in cell E8. The formula will look like this: "= ROUND ($ B8 * ($ B $ 2/12), 2)". The dollar sign also indicates a relative reference here. The formula will find the appropriate cell in column B.
    • In cell D8, subtract the total payment in cell C8 from the interest from cell E8. Use relative references so that the cell is copied exactly. The formula will look like this: "= $ C8- $ E8".
    • In cell H8, create a formula that subtracts the principal from the beginning balance for that period's period. The formula looks like this: "= $ B8- $ D8".
  9. Continue the schedule by filling in cells B9 through H9.
    • Cell B9 will include the relative reference of the previous period's closing balance. Enter "= $ H8" in this box and press Enter. Copy cells C8, D8, E8 and paste it into C9, D9, and E9. Copy cell H8 and paste it into cell H9. This is where relative references come into play.
    • In cell F9, you need to create a formula to calculate the cumulative principal paid. The formula will be: "= $ D9 + $ F8". Do the same for the cumulative interest rate cell in G9 with the same formula: "= $ E9 + $ G8".
  10. Complete the repayment schedule.
    • Highlight cells B9 through H9, move the mouse to the lower-right corner of the selection so the cursor changes to a cross, then click and drag the selection down row 367. Release the mouse.
    • You need to make sure the Auto Fill option is set to "Copy Cells" and that the final balance is 0 VND.
    advertisement

Advice

  • If the final balance is not VND0, double check to make sure you used absolute and relative references as instructed, and that the cells must be copied correctly.
  • You can now scroll down any stage in the loan repayment process to see how much went into the principal, how much was the interest, as well as how much principal and interest you paid. until that time.

Warning

  • This method is only applicable to home loans calculated on a monthly basis. For a car loan or a loan where interest is charged each day, the schedule can only provide a rough estimate of the interest rate.

What you need

  • Computer
  • Microsoft Excel
  • Loan details