How to Make an Amortization Schedule + FREE Download

In This Article

Excel is an excellent tool for calculating values based on money.  With a number of built in functions, Excel can lend itself to helping investors figure out the bottom line.  There are over 50 financial functions in Excel.  The financial function calculations can be categorized into;

• Simple interest
• Compound interest
• Annuity
• Repayment
• Price
• Investment
• Amortization

This article will focus upon the amortization calculations using Excel’s functions.  At the end of this article will be a step-by-step creation of a Loan Amortization Schedule.  Before we get to the loan schedule, a foundation of financial concepts must be presented.

Time Value of Money

Consider this proposal.  I will give you $500,000 to help you invest in an income property. But the catch is you must wait five years to receive the money.  My question to you: wait five years or receive $500,000 now?  The answer should be pretty straight-forward, you need the money now to invest.  Five years from now, your investment requirements might have changed.  The $500,000 may or may not be as important. You can do more with the money now, than in the future.

The above example demonstrates the concept of “time value of money”.  Money that is available to you today is more valuable than money in the future when purchasing investment properties. You can take money that is available to you today and invest it in income properties.  However, if you have to wait until a later date, those same dollars will not be as valuable.

The time value of money must be considered whenever an investor is analyzing financial reports.  Tracking your money or investments at any given time is paramount.  Making your money work for you requires knowing how much money you have available for investing.

Looking at an amortization schedule will show you in detail the payments that will be made on an investment loan.  The schedule shows the payoff of the loan with interest and principal payments calculated.  Having an Excel based amortization schedule makes it easy to see the effects of changes in interest rate, the length of the term and the number of payments in a year.

Change the loan interest rate to 7% versus 6.26%.  Or pay off the loan in 20 years instead of 30 years.  Make six payments within each year instead of 12 monthly payments.  All these issues can be addressed using an amortization schedule.  The schedule will help track those payments giving an investor an insight into opportunities for possible future investments.  Without knowing the status of your investment dollars, how will you be able to take advance of opportunities presented to you?

Foundation Concepts to Understand

A loan amortization schedule will require the input of the loan amount, the interest rate, the loan term or period and the number of payments in a year.  After the values have been entered, the schedule should automatically calculate the payments, interest, principal and loan balance.  Excel functions will create the automatic values.  The three Excel functions that are used by the loan schedule are; PMT(), IPMT() and PPMT().

The three functions require a number of arguments in order to return the calculated values.  The list of arguments and their definitions are:

• Rate  –  the interest rate of the loan
• Per  –  the number of the term period
• Nper  –  the total number of periods to repay the loan
• PV  –  the original loan amount or the Present Value
• fv  –  the residual debt in a given time or the Future Value. [optional]
• type  –  1 = regular payments at the start of the period, 0 = payments at the end of the period.  If not specified, the default is 0.  [optional]

PMT() is the amount of the regular payments on a loan.

PMT(Rate, Nper, PV, [fv], [type])

IPMT() is the interest portion of the loan that decreases as time passes.

IPMT(Rate, Per, Nper, PV, [fv], [type])

PPMT() is the principal portion of a loan that increases as time passes.

PPMT(Rate, Per, Nper, PV, [fv], [type])

Using the three functions makes it easy to create a loan amortization schedule in Excel.

Creating a Loan Amortization Schedule

In order to follow the steps to create this loan schedule you must have a basic understanding of using Excel.  For those of you who are not comfortable with the steps or would prefer a finished product, I will place this Loan Amortization Schedule in the BiggerPockets ‘FilePlace’.

Step 1

Create the following spreadsheet.  In the upper left are the loan conditions to be entered and will be used for the schedule at the bottom.

Step 2

Apply the proper number formatting for all the numbers in the schedule.

Comma Style	Number (no decimals)	Percentage
Loan Amount	Term in Years	Interest Rate
Payment	Payments per Year
Interest
Principal
Balance
All Cumulative values

 

Step 3

Enter the following sample values into the upper left of the spreadsheet.

Loan Amount              925,000.00
Term in Years             30
Interest Rate               7.25%
Payments per Year     12

These values will be used as a test schedule.

Step 4

The loan schedule will have a maximum of 30 years of monthly payments.  Therefore, we will need to apply payment, interest, principal and balance calculations for 360 months.

Starting at cell ‘A13’, type the number 1, then type the number 2 in cell ‘A14’.  For the rest of the months up to 360, use the AutoFill feature in Excel.  AutoFill by selecting cells A13:A14, click and drag the small box at the lower right of cell A14 until the numbers reach 360.  Your months should now expend from the number 1 in cell A13 to 360 in cell A372.

Step 5

Time to fill in the formulas and functions for the schedule.  Make sure you make the PMT(), IPMT() and PPMT() values negative.

1. E12:     =C3
2. B13:     =-PMT($C$5/$C$6,$C$4*$C$6,$C$3)
3. C13:    =-IPMT($C$5/$C$6,A13,$C$4*$C$6,$C$3)
4. D13:    =-PPMT($C$5/$C$6,A13,$C$4*$C$6,$C$3)
5. E13:     =E12-D13
6. G13:    =G12+B13
7. H13:    =H12+C13
8. I13:      =I12+D13

Your spreadsheet should have the following values:

 

Step 6

Select cells B13:I13 and drag down to the 360^th month to fill in the entire schedule for 30 years of monthly payments.  The last months of your schedule should look like this:

That completes the Loan Amortization Schedule.  Try entering other values in the input cells at the top left and see how the payments are presented.

This was an example of a very simple amortization schedule.  There are additional formatting features that could be applied to make the schedule easier to read.

Download Your Amortization Schedule For Free Now

I will be uploading a free “Loan Amortization Schedule” in the BiggerPockets ‘Resources’, under ‘Spreadsheets’ within the ‘FilePlace’.  Click HERE to get it for free! The schedule will be unprotected to allow you to see the formulas and additional formatting used in its creation.  Make sure to get your copy.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.