This article will focus upon the amortization calculations using Excel\u2019s functions.\u00a0 At the end of this article will be a step-by-step creation of a Loan Amortization Schedule.\u00a0 Before we get to the loan schedule, a foundation of financial concepts must be presented.<\/p>\n
Time Value of Money<\/h2>\n
Consider this proposal.\u00a0 I\u00a0will give you $500,000 to help you invest in an income property.\u00a0But the catch is you\u00a0must wait five years to receive the money.\u00a0 My question to you: wait five years or\u00a0receive $500,000 now? \u00a0The answer\u00a0should be pretty straight-forward, you need the money now to invest.\u00a0\u00a0Five years from now, your investment requirements might have changed.\u00a0 The $500,000 may or may not be as\u00a0important.\u00a0You can do more with the\u00a0money now, than in the future.<\/p>\n
The above example demonstrates the concept of \u201ctime value of money\u201d.\u00a0 Money that is\u00a0available to you today is more valuable than money in the future when purchasing\u00a0investment properties.\u00a0You can take money that is available to you today and invest it in income properties.\u00a0 However, if you have to wait until a later date, those same dollars will not be as valuable.<\/p>\n
The time value of money must be considered whenever an investor is analyzing financial reports.\u00a0 Tracking your money or investments at any given time is paramount.\u00a0 Making your money work for you requires knowing how much money you have available for investing.<\/p>\n
Looking at an amortization schedule will show you in detail the payments that will be made on an investment loan.\u00a0 The schedule shows the payoff of the loan with interest and principal payments calculated.\u00a0 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.<\/p>\n
Change the loan interest rate to 7% versus 6.26%.\u00a0 Or pay off the loan in 20 years instead of 30 years.\u00a0 Make six payments within each year instead of 12 monthly payments.\u00a0 All these issues can be addressed using an amortization schedule.\u00a0 The schedule will help track those payments giving an investor an insight into opportunities for possible future investments.\u00a0 Without knowing the status of your investment dollars, how will you be able to take advance of opportunities presented to you?<\/p>\n
Foundation Concepts to Understand<\/h2>\n
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.\u00a0 After the values have been entered, the schedule should automatically calculate the payments, interest, principal and loan balance.\u00a0 Excel functions will create the automatic values.\u00a0 The three Excel functions that are used by the loan schedule are; PMT(), IPMT() and PPMT().<\/p>\n
The three functions require a number of arguments in order to return the calculated values.\u00a0 The list of arguments and their definitions are:<\/p>\n
- \n
- Rate<\/b>\u00a0 –\u00a0 the interest rate of the loan<\/li>\n
- Per<\/b>\u00a0 –\u00a0 the number of the term period<\/li>\n
- Nper<\/b>\u00a0 –\u00a0 the total number of periods to repay the loan<\/li>\n
- PV<\/b>\u00a0 –\u00a0 the original loan amount or the Present Value<\/li>\n
- fv<\/b>\u00a0 –\u00a0 the residual debt in a given time or the Future Value. [optional]<\/li>\n
- type<\/b>\u00a0 –\u00a0 1 = regular payments at the start of the period, 0 = payments at the end of the period.\u00a0 If not specified, the default is 0.\u00a0 [optional]<\/li>\n<\/ul>\n
PMT() is the amount of the regular payments on a loan.<\/p>\n
PMT(Rate, Nper, PV, [fv], [type])<\/p>\n
IPMT() is the interest portion of the loan that decreases as time passes.<\/p>\n
IPMT(Rate, Per, Nper, PV, [fv], [type])<\/p>\n
PPMT() is the principal portion of a loan that increases as time passes.<\/p>\n
PPMT(Rate, Per, Nper, PV, [fv], [type])<\/p>\n
Using the three functions makes it easy to create a loan amortization schedule in Excel.<\/p>\n
Creating a Loan Amortization Schedule<\/h2>\n
In order to follow the steps to create this loan schedule you must have a basic understanding of using Excel.\u00a0 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 \u2018FilePlace\u2019<\/a>.<\/p>\n
Step 1<\/h3>\n
Create the following spreadsheet.\u00a0 In the upper left are the loan conditions to be entered and will be used for the schedule at the bottom.<\/p>\n
![\"Creating](\"https:\/\/www.biggerpockets.com\/blog\/wp-content\/uploads\/2013\/03\/setup.jpg\" "\\"\\"")
<\/a>Creating amortization schedule interface<\/figcaption><\/figure>\n Step 2<\/h3>\n
Apply the proper number formatting for all the numbers in the schedule.<\/p>\n
\n\n
\n Comma Style<\/b><\/td>\n Number (no decimals)<\/b><\/td>\n Percentage<\/b><\/td>\n<\/tr>\n \n Loan Amount<\/td>\n Term in Years<\/td>\n Interest Rate<\/td>\n<\/tr>\n \n Payment<\/td>\n Payments per Year<\/td>\n <\/td>\n<\/tr>\n \n Interest<\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n Principal<\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n Balance<\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n All Cumulative values<\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n
Step 3<\/h3>\n
Enter the following sample values into the upper left of the spreadsheet.<\/p>\n
Loan Amount\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 925,000.00
\nTerm in Years\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 30
\nInterest Rate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7.25%
\nPayments per Year\u00a0\u00a0\u00a0\u00a0 12<\/p>\nThese values will be used as a test schedule.<\/p>\n
Step 4<\/h3>\n
The loan schedule will have a maximum of 30 years of monthly payments.\u00a0 Therefore, we will need to apply payment, interest, principal and balance calculations for 360 months.<\/p>\n
Starting at cell \u2018A13\u2019, type the number 1, then type the number 2 in cell \u2018A14\u2019.\u00a0 For the rest of the months up to 360, use the AutoFill feature in Excel.\u00a0 AutoFill by selecting cells A13:A14, click and drag the small box at the lower right of cell A14 until the numbers reach 360.\u00a0 Your months should now expend from the number 1 in cell A13 to 360 in cell A372.<\/p>\n
Step 5<\/h3>\n
Time to fill in the formulas and functions for the schedule. \u00a0Make sure you make the PMT(), IPMT() and PPMT() values negative.<\/p>\n
- \n
- E12:\u00a0 \u00a0\u00a0 =C3<\/li>\n
- B13:\u00a0\u00a0 \u00a0 =-PMT($C$5\/$C$6,$C$4*$C$6,$C$3)<\/li>\n
- C13: \u00a0\u00a0 =-IPMT($C$5\/$C$6,A13,$C$4*$C$6,$C$3)<\/li>\n
- D13:\u00a0 \u00a0 =-PPMT($C$5\/$C$6,A13,$C$4*$C$6,$C$3)<\/li>\n
- E13:\u00a0\u00a0\u00a0\u00a0 =E12-D13<\/li>\n
- G13:\u00a0\u00a0\u00a0 =G12+B13<\/li>\n
- H13:\u00a0\u00a0\u00a0 =H12+C13<\/li>\n
- I13:\u00a0\u00a0\u00a0\u00a0\u00a0 =I12+D13<\/li>\n<\/ol>\n
Your spreadsheet should have the following values:<\/p>\n
![\"First](\"https:\/\/www.biggerpockets.com\/blog\/wp-content\/uploads\/2013\/03\/Month1.jpg\" "\\"\\"")
<\/a>First Month Values<\/figcaption><\/figure>\n <\/p>\n
Step 6<\/h3>\n
Select cells B13:I13 and drag down to the 360th<\/sup> month to fill in the entire schedule for 30 years of monthly payments.\u00a0 The last months of your schedule should look like this:<\/p>\n
![\"Last](\"https:\/\/www.biggerpockets.com\/blog\/wp-content\/uploads\/2013\/03\/lastmonths.jpg\" "\\"\\"")
<\/a>Last Monthly Payments<\/figcaption><\/figure>\n That completes the Loan Amortization Schedule.\u00a0 Try entering other values in the input cells at the top left and see how the payments are presented.<\/p>\n
This was an example of a very simple amortization schedule.\u00a0 There are additional formatting features that could be applied to make the schedule easier to read.<\/p>\n
Download Your Amortization Schedule For Free Now<\/h2>\n
- Per<\/b>\u00a0 –\u00a0 the number of the term period<\/li>\n