Create an Amortization Schedule in Microsoft Excel

by  About
       

Simple amortization schedule

See screen shot 1.

Formulas that do not appear in the figure:

  • The formula in cell C17 is =LOAN (LOAN is the name of cell C4).
  • The formula in cell C18 is =G17; copy the formula from cell C18 to all the cells in column C, starting from C18.

Amortization schedule with a grace period

  • The difference between a regular amortization schedule and one with a grace period is that in the latter, the repayment of the principal is delayed. The loan agreement stipulates the month in which the repayment of principal begins. The interest on the loan is calculated, and the first interest payment starts with the first month after the month that the loan was accepted.
  • See screen shot 2. Note that the formulas used are not PPMT and IPMT; these formulas are not appropriate when the calculations are not linear.

Amortization schedule for random payment

  • See the formulas in screen shot 3 for calculating the sum of the principal and interest in every payment.
  • The dates of the loan repayment are random. The interest is calculated according to the number of interest days divided by 365 days in a year.

Screenshot // Create an Amortization Schedule in Microsoft Excel
Create an Amortization Schedule in Microsoft ExcelCreate an Amortization Schedule in Microsoft ExcelCreate an Amortization Schedule in Microsoft Excel



21 thoughts on “Create an Amortization Schedule in Microsoft Excel

  1. what is the formula used to calculate a mortgage payment that includes tax, insurance and any other payment included in my monthly payment?

  2. Is it possible to create an amortization spreadsheet that will update (estimate) the associated totals (i.e., prn bal,int paid, int due,etc..) as the payments are entered each month?

  3. What would be the written formula to recalculate the number of payments in months if the interest rate was lowered but the payment stayed the same.

  4. If I know principal, interest, and # years, how do I calculate total interest paid over the life of the loan considering monthly payments?

  5. “Hi Scott,

    Don’t know if you still need this answer, but anyway:

    If thee are other elements that principle and interest in the periodic payments, then you will need to eliminate them from the payment before applying it to your loan calculation.

    Just do that calculation separately in your model before getting into the loan calculation zone.

    Alan.”

  6. “Hi Charles,

    Yes you can do this.

    Just set up your repayment schedule to refer to the brought forward balance each period (rather than calculate the brought forward balance by reference to the opening scenario).

    If you also include a cell for the ‘current’ interest rate and that is entered each period, you can also generalise for a floating rate calculation, and if you like, forecast foreward what the rates will be.

    That approach is also self-correcting if the borrower misses payments or short-pays a period, since it re-claculates based on actuals, not the original theoretical repayment schedule.

    You might also want to set up the calculation with extra periods past the original scheduled closure date. That way you can cope with someone who misses or short-pays and then continues with the original repayments, thus extending the period of the loan.

    Alan.”

  7. “Hi Des,

    It sounds like all you need is to use the NPER function which, “”returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.”"

    If the rate changes, just re-calculate using NPER or set it up so that each period is re-calculated based on the previous actuals in preceding periods (as for the answer above).

    Alan.”

  8. “Hi Shannon,

    Using the following example:

    Interest rate: 10% pa
    Periods: 60 (5 years, monthly)
    Initial loan: $10,000
    Final balance = $Nil
    Interest charged at end of period

    To get the interest payable in any one given period, we can use the IPMT function. For example, the fifth month interest is:

    IPMT((110%^(1/2))-1,5,60,10000,0,0)

    To get the total interest paid, we simply sum that function for all periods (1 through 60 inclusive), ut entering the following as an ARRAY FORMULA:

    {=SUM(IPMT((110%^(1/2))-1,ROW(1:60),60,10000,0,0))}

    Note that this must be entered with Shift-Ctrl-Enter to get the braces around the formula. Check Excel’s help for more about array formulae.

    Alan.”

  9. “Hi Bobby,

    Isn’t that exactly what the examples on this pge show you?

    If not, please post back with a more detailed query and I’m sure we can help.

    Thanks,

    Alan.”

  10. what is the formula used to calculate a mortgage payment that includes tax, insurance and any other payment included in my monthly payment?

  11. Is it possible to create an amortization spreadsheet that will update (estimate) the associated totals (i.e., prn bal,int paid, int due,etc..) as the payments are entered each month?

  12. What would be the written formula to recalculate the number of payments in months if the interest rate was lowered but the payment stayed the same.

  13. If I know principal, interest, and # years, how do I calculate total interest paid over the life of the loan considering monthly payments?

  14. “Hi Scott,

    Don’t know if you still need this answer, but anyway:

    If thee are other elements that principle and interest in the periodic payments, then you will need to eliminate them from the payment before applying it to your loan calculation.

    Just do that calculation separately in your model before getting into the loan calculation zone.

    Alan.”

  15. “Hi Charles,

    Yes you can do this.

    Just set up your repayment schedule to refer to the brought forward balance each period (rather than calculate the brought forward balance by reference to the opening scenario).

    If you also include a cell for the ‘current’ interest rate and that is entered each period, you can also generalise for a floating rate calculation, and if you like, forecast foreward what the rates will be.

    That approach is also self-correcting if the borrower misses payments or short-pays a period, since it re-claculates based on actuals, not the original theoretical repayment schedule.

    You might also want to set up the calculation with extra periods past the original scheduled closure date. That way you can cope with someone who misses or short-pays and then continues with the original repayments, thus extending the period of the loan.

    Alan.”

  16. “Hi Des,

    It sounds like all you need is to use the NPER function which, “”returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.”"

    If the rate changes, just re-calculate using NPER or set it up so that each period is re-calculated based on the previous actuals in preceding periods (as for the answer above).

    Alan.
    Re: Above
    Alan Posted on: 31-12-1969

    Jeff,

    Apologies, I misread your location as your name.

    Sorry!

    Alan”

  17. “Hi Shannon,

    Using the following example:

    Interest rate: 10% pa
    Periods: 60 (5 years, monthly)
    Initial loan: $10,000
    Final balance = $Nil
    Interest charged at end of period

    To get the interest payable in any one given period, we can use the IPMT function. For example, the fifth month interest is:

    IPMT((110%^(1/2))-1,5,60,10000,0,0)

    To get the total interest paid, we simply sum that function for all periods (1 through 60 inclusive), ut entering the following as an ARRAY FORMULA:

    {=SUM(IPMT((110%^(1/2))-1,ROW(1:60),60,10000,0,0))}

    Note that this must be entered with Shift-Ctrl-Enter to get the braces around the formula. Check Excel’s help for more about array formulae.

    Alan.”

  18. “Hi Bobby,

    Isn’t that exactly what the examples on this pge show you?

    If not, please post back with a more detailed query and I’m sure we can help.

    Thanks,

    Alan.”

Leave a Reply

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


4 − one =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>