Follow Us on Social Media

email icon rss icon linked.in icon google plus icon twitter icon facebook icon

How to Make an Amortization Schedule + FREE Download

by Loretta Steele on March 20, 2013 · 6 comments

  
Amortization Schedule

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.

Creating amortization schedule interface Creating amortization schedule interface

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:

First Month Values First Month Values

 

Step 6

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

Last Monthly Payments Last Monthly Payments

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.

Email *
  



{ 6 comments… read them below or add one }

Jeff Brown March 20, 2013 at 3:56 pm

You’re the real deal, Loretta.

Reply

Loretta Steele March 21, 2013 at 9:44 am

Thanks Jeff, I really appreciate that.

Reply

Paul Leason March 20, 2013 at 9:11 pm

You can also download the MORTGAGE APP for your iPhone and have it at your fingertips wherever you are. I use it all the time and its a great feature. EXCEL is a great tool aswell though.

Reply

Loretta Steele March 21, 2013 at 9:51 am

Paul, I’m with you, APPs are so convenient and Excel is great when I’m working on projects with my laptop.

Thanks for reading and commenting on my article.

Reply

Robert Ricco March 26, 2013 at 10:26 am

I’ve always struggled with amortization schedules and Excel. Most often, I wound up pulling up a schedule on the web or an app, but now I have this one set up with your instruction. It’s good to have it on my laptop for use anytime. Thanks, Loretta.

Reply

Loretta March 26, 2013 at 11:26 pm

Robert, you are most welcome. I am glad to know you were able to find the blog post useful.

Loretta

Reply

Leave a Comment

Comment Policy:

• Use your real name and only your name in the field designated for your name.
• No keywords allowed as anchor text in the name or comment fields.
• No signature links allowed under your comments
• You may use links in the body of your comment, but it must be relevant to the discussion at hand, and not merely be some promotional link.
• We will have NO reservations about deleting your content if we feel you are posting merely to get a link without adding value to our discussion.
If you add value, but still post keywords, we'll use your comment, but remove your link and keywords.
• For more information about acceptable practice, see our site rules.

Want your photo to appear next to your comments? Set up your Gravatar today.

Previous post:

Next post: