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 2. Total comments: 89

  1. Thank you for the tutorial on making an Amortization Schedule. I learned a few things from you as well.

  2. Awesome! Great help! Thank you.

  3. Am I the only one that is lost here? When setting up the table for extra payments, when i copy the formulas down for the amortization table the boxes the formula references count up as well and start referencing incorrect numbers and in some cases words. How do i copy the formula down the chart but still reference the correct boxes at the top of the table?

    1. Notice how some earlier formulas use $ symbols, such as $C$4. This locks the formula on only ever use the data in C4. Without that, then when you auto fill the formula, it changes itself to C5, C6, C7, etc.

      There are some important uses for the shifting cell reference, such as when calculating remaining balance. You want it to use the formula from the previous row, and you want that to keep shifting as it goes down.

  4. Very helpful, thank you very much for this tutorial.

  5. How can I generate this in excel?
    A loan of 180,000,000.00 at 22%. Tenor: 48 months(inclusive of 6 months moratorium). The payment plan is 14 quarterly repayment of principal of 12.857 million while interest will be serviced monthly throughout the duration of the facility. Penal rate for late payment is 1% flat on unpaid instalment in addition to the current interest repayment of rate applied on the outstanding amount.

  6. Hi Everyone,

    How do i claculate the monthly scheduled payment for a loan with a tenure of 5 years and a grace period granted for the repayment for the 1st nine months of the Year 1.

    Appreciate your kind response.

  7. Do you know of a single amortization template where you can add new loans after the first loan started amortizing and have a combined worksheet with the blended information?
    i.e. Loan1 $10,000, 6%, Start Date 01/01/21, term 60 months and Loan2 $12,000, 5%, Start Date 03/01/21, term 72 months.

    I know there are bank software package for this, but am trying to do it in Excel.

    Thanks,

  8. If you calculate monthly payments and then pay bi-weekly. How do you account for early payment and extra payment? Eg if your payment is 1200, and in month 1 you pay 1200. Let’s say 400 goes towards principal and 800 to interest, on day 30 your principal goes down 400, but, if you are paid bi-weekly and pay 600 at day 14 (200 principal and 400 interest) and 600 on day 28 you will make an extra payment in the year, and the 200 decrease on day 14 decreases the balance for interest on day 28. How do you account for this?

  9. tnx. Good prt

  10. tnx.

  11. Greetings,

    Thank you for sharing this tutorial. I'm wondering if it would be possible to get the information in the Loan Summary section without building an amortization schedule? I want a simple clean sheet to compare payment options. I know I can build the amortization schedules on a different sheets and reference those fields on a new sheet, but I have a lot of different options to consider, and would prefer not to build amortizations schedules for each if it's possible. Thanks.

  12. Well articulated. Good presentation.

  13. I believe there is an error in your loan summary calculations. Although the 30 year loan example has 360 monthly payments, with the FINAL payment in row 367 (because the first 8 rows are header). In other words, you show total payments (F2): =-SUM(B8:B360), but shouldn't it be payments (F2)=-SUM(B8:B367)? And instead of total interest (F3):=-SUM(C8:C360), shouldn't it be total interest (F3):=-SUM(C8:C367)? Also, any chance you could tell me how to modify the spreadsheet for a loan that is interest-only for the first 10 years, then typical Principal & Interest amortization for the final 20 years?

    1. Hi Beth,

      Oops, you are absolutely right. Fixed, thank you!

    2. Oops there was an error in my note about the error LOL! I meant to say "Although the 30-year loan example has 360 monthly payments, with the FINAL payment in row 367 (because the first SEVEN rows are header), the totals formulas in F2 and F3 only include up to row 360)". Thanks again. Any help on a loan with interest-only in the beginning, then switching to typical amortization at some point, would be much appreciated.

  14. Hi can someone help... if I am paying for the principle + interest only for the 7th to the 12 month, and 1st to 6 th month will be interest only, so how do i put this in the excel.

    Thanks :-)
    Sangita

  15. Thanks for this, I've tried both your template and Microsoft's one to extend beyond 360months. How can I fix this to allow me spread payments to 35years for example?

    1. Ignore my message, I've just used your tutorial to build from scratch. Thanks.

  16. How to modify the interest rate changes, frequently? please reply

  17. How about the short term?? I mean loan term is under 1 year (8 months), can I use this formula as well?

  18. This table is very helpful. However how can I create amortization table that shows the monthly payments of the APR compounding daily, monthly, and annually

  19. Hi Svetlana,

    Thankyou for building this spreadsheet, it's very helpful. I was wondering how I could include a monthly fee into the calculations?

    Regards
    David

    1. One easy way would be to add it to the Remaining Balance column. In A7 type "Monthly Fee" and then in C7 type in the amount of the fee. For example, if the fee is $25, then type "25" into C7.

      Next, add $C$7 to the Balance column.

      Doing that will also increase the number of payments. For example, a 30 year mortgage will not be paid off in 30 years with extra fees being thrown onto it, UNLESS those fees are also automatically included in the payment amount. It may be wise to add $C$7 to the Payment column, too.

  20. HI,

    Please can you tell me how I would create an amortization schedule, where there are delayed payments for the first 6 months of a five year term.

    Thanks so much.

  21. how do I add grace period? I am getting a loan for 15 years, but I am not paying anything during initial 2 years. How shall I calculate that? another thing is that my annual interest rate is different during the grace period and remaining period. I just cannot put these two parameters in the amortization shcedule

  22. 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

  23. 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.

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

  25. 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?

  26. 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

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

  28. 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!

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

  30. 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.

  31. 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!

  32. 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

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

  34. 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?

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

  36. 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.

  37. Dear team
    I need excel please guide

  38. 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).

  39. 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.

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

  41. 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!!

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

  43. 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.

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

  45. 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.

  46. 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.

  47. 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 :)