Comments on: How to make a loan amortization schedule in Excel (with extra payments)

The tutorial shows how to make an amortization schedule in Excel to detail periodic payments on an amortizing loan or mortgage, including an amortization schedule with extra payments and a variable number of periods. Continue reading →

Comments page 3. Total comments: 88

  1. Hi Team,

    I need your assistance to calculate 20% fortnightly and spread over a number of repayments.

    Say, K1000 is loan out to customers with an interest rate of 20% including a processing fee of 2% on the loan amount. How much can be repaid over 5 fortnights or up to 16 fortnight (8 months).

    No. of repayments in fortnights
    Amount (Kina) 5 6 7 8 9 10 11 12 13 14 15 16 17
    500
    600
    700
    1000
    1500
    2000
    Amount will increase

    Kindly work on my request and advise me

    Thank you,
    Inara 7086 1814

  2. A loan was made with the following factors $17000, 2.0%, payment of $200 Monthly. Approx time 8 years. Unfortunately, the payments are sometimes over and sometimes Zero. When I use the summary for extra payment that you provided, it calculates the over payments, but with a $0.00 payment, it just leaves it as the previous months balance. Should there not be added interest for the missed payment?
    Thank you.

  3. Thank you so much! Your tutorial was easy to follow for me.

  4. Thank you for the great write-up
    How do i calculate interest on a loan with moratorium period for first few months? How would the amortization for such a loan be?

  5. THE TABLE IS WONDERFUL
    Can you add the following:
    start date of loan
    Due Date of payment
    Also ion the extra payment can you add the facility where in the person repays the loan with varied payments, like in your example your schedules payment is 2238.63, now if the persons pays 5000 in any month then the tenure should reduce however instead of the 2238.63 he pays nothing or less then there should be a additional charge of the interest lost by extending the tenure. Is that possible

  6. Hello, what if the bank charges a yearly service fee of $500. How do I incorporate this into the spreadsheet?

  7. Hi Svetlana,
    Thank you soooooo much! I've spent many hours looking for a way to calculate balance owed with varying extra payments and there is nothing else out there! Very Nice! Very Professional! You are the best!

  8. Please i need a loan amortization excel sheet that has biweekly repayment. and loan terms. 3 months, 6 months, 10 months,
    appreciate your effort

  9. How do I insert a differnet %rate without changing the existing data? We went from a 15yr fixed 3.125% last November to a 15yr fixed 2.5%. How do I inset that new 2.5% into the table for my next payment? Thanks

    1. I would say simply just add your new interest rate to a cell on top of your spreadsheet and reference it in the next qualifying payment period (formula calculation) instead of the original "Annual interest rate". Call it NewInterestRate and substitute this cell for "AnnualInterestRate" cell.

  10. I believe there is an error in this tutorial (The last payment,)
    https://www.ablebits.com/office-addins-blog/create-loan-amortization-schedule-excel/
    4. Build formulas for amortization schedule with additional payments
    the last total payment cell (D32) is the same as balance in the previous period cell (G31). Interest for that period ($12.24) was not added to (D32). I believe cell (D32) the total payment should be $2110.49 and cell (E32) should be $2098.25.
    On the plus side , your tutorial was excellent, much better than other sites.
    Thank You.

    1. Hello William,

      Thank you for your feedback! From all appearances, you are right. The problem is that I created the amortization schedule with extra payments based on Excel's build-in Loan Amortization Schedule template. In fact, our schedule is a simplified version of Microsoft's one (the goal was to make it more understandable and easier to replicate), and both produce exactly the same results (to make sure of that, just download the loan amortization schedule from your Excel and use it on the same data).

      Anyway, we've tried another formula for calculating the total payment and got a different result for the last period ($2110.49 like you mentioned). Just need to do some more testing to make sure in works correctly in other scenarios and work out a plausible explanation why our result is different from Microsoft's :) If all goes well, I will update the formula in the tutorial.

      Thank you again for your very thoughtful comment!

  11. How would i create a schedule where i can manipulate both the payment frequency, and the interest capitalization frequency. All examples I've seen work on the assumption that interest is capitalized monthly (Put differently, interest is capitalized at the same frequency that payments are made). Need to calculate where these two variable are different

  12. Very helpful. How about if I want to include 6months moratorium

  13. Apart from regular extra payments, how can the adhoc extra payments be considered (e.g. if I had a windfall gain in year 2 and then year 3 and 5)
    Or these adhoc payments could be every quarter, semi-annual, annually, etc. how will these kind of payments affect the principal and overall duration of the loan?

  14. We need a formula for when the FV is not zero...When a balloon payment is due at the end of the term.

  15. Dear Svetlana,

    Excellent post. I loved your extra payment tutorial. However, most of the banks in Malaysia provide reducing balance mortgage with daily rest (daily interest calculation). It would be helpful if you can also show us how to devise a daily rest amortization with extra payment (this extra payment directly reduces the principal, hence reduced interest charges).

    Thank you.

  16. Dear team
    I need excel please guide

  17. I downloaded your payment schedule/ledger with additional payment. The formula is set up to find the monthly interest based on a loan that compounds interest monthly. How do I get this table to show monthly interest based on a loan that compounds the interest annually?

    1. Hi,
      Just change the number of payments per year to 1 instead of 12. That should calculate the payment annually (I could be wrong).

  18. Excellent write-up!
    Suggestions/observations:
    - Assign names to APR and other fixed values; the formulas will then be clearer.
    - As I understand the formulas, there's an assumption that all periods are equal. That is, the principal and interest portions of the payments disregard the number of days between payments (for monthly or yearly payments). Perhaps add an option for the IPMT and PPMT functions when one has to consider the days between payments. Some mortgages calculate interest based on number of days in each payment period.

  19. Do you have a ready excel formula of the mortgage table

  20. Thank you for the tutorial. I was looking for a formula to incorporate both a balloon payment and periodic additional payments toward principal. I don't know anything about amortization but was able to read the page and follow the example. Best on the Internet - it was hard to find other examples that allowed for both

    Thank you!!

  21. how do you adjust 'actual principal/interest' based upon payment date? when borrower does not pay consistently on the same date.

  22. Hi! Thanks a lot for the tutorial. Can you please guide me what needs to be done if principal is fixed and payment (principal plus interest) is variable and there is a grace period of six months.

  23. I agree with Kira! I need a more fluid amortization schedule that varies monthly as different extra payment amounts are made.

  24. Hi,
    How do you include a column for variable additional payments? i.e. not necessarily $100 each time.
    Thank you
    Kira

    1. Hi Kira,
      You can just type additional payments directly in the Extra Payment column.

  25. Nice tutorial but what if your totals (e.g. in 1st tip, checking interest + principal payment) do not agree?
    How do we handle rounding? Looks to me like the IPMT and PPMT result in rounded results that aren't easy to fix. But further down, where you show extra payments, it appears you're calculating the interest & principal application without use of the IPMT or PPMT functions. Looks to me like the if you want to avoid rounding issues, you should avoid the IPMT & PPMT functions, only use the PMT function to get the periodic payment, and then calculate the periodic amounts with regular arithmetic. Yes? No? Maybe? Thanks

    1. Hi Ken,
      In theory, the rounding error cannot exceed 0.5 cents (0.005 dollar). So, the Payment and Interest + Principal may not agree only by 1 cent or less. If the difference is bigger, then there is likely to be something wrong with your model or formulas.

      I say "in theory" because in practice, Excel only shows the values rounded to 2 decimal places in cells. The underlying values returned by PMT, IPMT and PPMT are not rounded. To make sure of this, you can choose to show more decimal placed in formula cells.

  26. Dear sir,

    Please i need a loan amortization excel sheet that has weekly repayment.

    thanks.

    1. Change PaymentsPerYear to 52 and increase the number of rows in your table from 60(?) to 261..

      1. Hi, I am trying to show bi-weekly payments and have it set to 26 payments per year. However, it only shows me the end of month "EOMONTH" dates. How can I change this to show the bi-weekly date?

      2. Hello. Can you please tell me how the following would be calculated?

        Add additional funds to each weekly payment (I have weekly payments of 'x' and want to add an extra $60 to each payment.

        Add additional lump sum payment once a year

        Thank you.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)