Tutorials
The program is really two programs in one, linked together. The top of the screen is a Financial CALCULATOR. The bottom of the screen is the SPREADSHEET amortization schedule
CALCULATOR :
The default information in the calculator numerical data boxes is easily changed by left clicking the white box with the mouse and erasing the numbers by pressing the Backspace key.
Enter new information and press the Tab key to move to the next input box. If you hold the Tab key down it moves in a continuous loop through the 11 input boxes. The data entered does not require commas only numbers and decimal points. The principal is in dollars without commas. The annual Interest rate percent is entered as a number with no percentage sign. The amortization period is entered as years; thus a 39-month amortization period would be entered as 39/12 = 3.25 years.
Calculating the missing variable:
You can change any three of the first four input boxes and the fourth box will be calculated. After the tab is pressed for the third data box change the remaining fourth box will be calculated automatically.
You can calculate the Principal you could borrow for an interest rate 4.125 amortized for 27 years with monthly payments of $800 dollars. Answer: $156,170.92
You can calculate the Amortization Period for an interest rate of 3.875 for a Principal amount of $159,000 making monthly payments of $900.
Answer: 21.84 years, that’s 262 payments and a partial small 263rd payment of $118.11
You can calculate the Annual Interest Rate for a principal of $160,000 amortized for 24 years paying $800 per month. Answer: 3.2402%
Calculate the Payment at a rate of 3.125% for 30 years for a Principal $300,000
Answer: $1,285.13 per month.
Maximum Calculator inputs:
The maximum Principal allowed entered is 99 million. The maximum annual interest rate is 25% and the maximum amortization period is 40 years.
That loan would require 480 monthly blended payments of $2,062,603.77 and the last payment #481 of $ 4,542.70
When utilizing the 3 out of 4 its always best to double check the fourth box by pressing F6 key to double check the iterative loop was fully finished.
For three out of four to work properly one must enter numbers in the 3 boxes, even if one of the three boxes already contain the numbers you just happen to need. Clearing it out with the Backspace a re-entering the same number ensures the program knows there have been three box changes and thus go into an iterative mode to calculate the correct answer.
If you change only one box, you must decide which of the other 3 boxes to recalculate. Move to that box to calculate with the mouse and press the F6 key or using the mouse to click on the prompt message in front of that box. The box will be calculated and yield the same answer. F6ing the box or left clicking on the boxes prompt, calculate the same answer.
Advance Date/ Interest Adjustment Date:
The Interest Adjustment Date must always be later than the Advance Date. Let’s say your sitting in the loan officer’s office signing the papers on December 1st and he is advancing the money on December 1 but you want the monthly payments to be on the 15th of each month because your pay cheque is deposited into your account on the 15th.
The first payment on December 15th is interest for the use of the money for 15 days and in essence makes the 15th the new advance date and thus January 15th is the first actual blended payment.
Compounding:
The selection of the type of “compounding” determines the value of the interest factor that is used at the end of each payment period to calculate the interest portion of the blended payment. In the USA the normal compounding is usually “monthly” which is the hard-wired, default, of most financial calculators.
In Canada, the normal compounding for mortgages is “semi-annual”. For American mortgages the normal compounding is always set at monthly. All loans and mortgages in the USA utilize monthly compounding. In Canada mortgages are semi-annual compounding and personal loans are monthly compounding. Its ironic and confusing for most Americans and Canadians that we are forced to talk about compounding (regarding calculations) even though interest calculations on all loans and mortgages are based upon simple interest. The only scenario in which interest calculated on a mortgage or a loan is compounded is when a payment is missed and the interest is added to the outstanding balance thus interest from that point on is compounded. The principal portion of a missed bended payment must eventually be paid to the lender. Unfortunately, the legalese and compounding nomenclature is so deeply rooted in the industry it’s a common myth that mortgages utilize compound interest. While on the topic mortgages and loans are calculated the same way in Canada and the USA. An interest calculation is performed at the end of each month (time period) and is shown on a blended payment amortization schedule as the interest portion. The only obvious difference is that the compounding makes the monthly interest factor numerically different. For example, for an annual interest rate of 12% the the American “monthly compounding” interest factor would be 0.01
The Canadian “semi-annual compounding” interest factor for an annual interest rate of 12% would be 0.0097588 a little bit less. This is covered in greater detail in other sections of the amortization.com web site articles.
Here are other interest factors for various “compounding”.
As mentioned earlier, all hard-wired financial calculators assume the year is divided into 12 equal months. Thus, an equal month has 30.4166666 days. You can see that the exact day 365 factor yields the correct 360-day interest factor as per a financial calculator, that’s $975.88 interest for the use of $100,000 for 30.41666 days. There are advantages to having a monthly payment loan based upon a 365-day year amortization schedule.
Exact day amortization schedules (365-day year) are quite common in the USA because the monthly interest factor is based on a 28, 29, 30 and 31 day time period for the specific month. The interest factor changes each month and the interest portion of the blended payments do not smoothly decline as in a 360-day year monthly schedule.
Payments Per Year:
You will note you are allowed considerable freedom in choice. In Canada the weekly and the biweekly payment plans introduced in the early 1980s have become very popular due to their obvious savings in interest.
Days Per Year:
Obviously, there are 365 days in a year. Calculations by all hard-wired financial calculators assume the year is divided into 12 equal periods (months). That implies the average month is 30.41666666 days long and is handled by selecting the “360” days per year, aka, Bankers Year. In the USA virtually all monthly mortgages and loans are calculated using the 360 mode. The only exception is the exact day monthly payment schedule where 365 is chosen because it makes it easier to calculate closing costs (interest costs) in the middle of the month instead of prorating interest and getting into the per diem swamp that usually confuses most people.
In Canada both the 360 and 365 mode are used for monthly payment mortgages. In Canada the 365-day mode is used for all weekly and biweekly payment mortgages.
Term:
One usually leaves the Term blank (empty) and in Canada the Term is only used on three occasions. If one is performing an IRD calculation, a discounting calculation, or if one wanted to quickly print out the amortization schedule only up to the number that appears in the Term box instead of the full Amortization Period.
Printing an amortization schedule:
Go to File selection on the top of the CALCULATOR portion of the main screen and chose the full spreadsheet or the highlighted range to print.
Residual Value:
Usually left blank. It is used only if one is interested in having a specific balance outstanding at the end of the amortization period. Its an ideal way to verify if your lease payments are within reason.
Say you leased a $24,000 car for 4 years at 12% and were told the Residual Value after 4 years was $12,000 if you wanted to buy the car. Your lease payments should be approximately $436.01 per month. I say approximately because lease payments are usually made in advance and that complicates this simple calculation. The 29-cent difference is due to the iterative method employed in the calculation. Practically speaking its close enough to $12,000.
Heading:
Whatever you type in that box in the Calculator it will show up as the header on each page of an amortization schedule.
Calculator Menu Bar
File Preferences Edit Format Utilities Help
SPREADSHEET:
The linked program (to the Calculator) below is a SPREADSHEET program that will display a blended payment amortization schedule based upon the information initially entered in the CALCULATOR. The SPREADSHEET calculates a blended payment amortization schedule based upon the information calculated in the CALCULATOR.
The way to access the spreadsheet amortization schedule is to use the mouse and click on any cell in the spreadsheet. Once you are in the SPREADSHEET amortization schedule and you make changes you never need to go back to the CALCULATOR. The CALCULATOR then, only serves as a reference point. A reference point to compare your interest savings or extra costs because of spreadsheet changes.
Saving your Spreadsheet amortization schedule:
Using your mouse, you click on the FILE selection, in the header above the CALCULATOR.
Making changes in the SPREADSHEET amortization schedule:
The only data cells in the spreadsheet that can be directly changed are in the columns named;
Date
Payment
Int %
Prepay
Notes
If in haste inadvertently leave the Spreadsheet (without saving your changes) by clicking on any of the first four prompts to the numerical data boxes in the CALCULATOR you will get a warning to save your spreadsheet changes.
Comparing interest costs because of changes:
Here is a practical example of changes and how comparing costs of the Calculator and the Spreadsheet are easily achieved. Suppose your lender allowed you to skip the first three payments (highlighted in yellow) on your mortgage to help your business or to allow you to use that money to buy new kitchen appliances on your new home.
Changes made in the spreadsheet show up as coloured cells. Subtracting the initial green calculator interest $257,085.83 from the blue spreadsheet interest $266,335.86 shows it will cost you an additional $9,250.03 in interest over the 30 years if you do nothing else but finish off the mortgage as per the schedule.
Make sure your lender is not playing with words because “skip” and “defer” have different meanings. If “skip” means you do not pay back the principal portions of the 3 missed blended payments the lender is only charging you the extra $9,360.97 in interest over the 30 years.
If the word skip means “defer’.
The lender might ask for the missing principal portions of the 3 missed blended payments ($2,580.51) to be paid back at the end of the mortgage and test your memory.
Rounding and insignificant interest differences:
The Calculator Int.= 270,308.72 is 87 cents higher than the Spreadsheet Int.= 270,307.85
The Calculator performs the interest calculation as a single calculation whereas the Spreadsheet does 361 single calculations and the number of decimal places and the rounding selection comes into play. See Rounding under the Preferences Menu.
Prepaying principal:
A blended payment contains an interest portion and a principal portion. The total of the two for each payment always add up to the same amount.
Prepaying future principal portions (pre paying principal rule, PPP) of a blended payment is an excellent way to save on interest and if it done according to the amortization schedule you are immediately aware of your interest savings over the life of the mortgage. This can be done by simple addition but you must have an amortization schedule to perform the task.
This example screen below, demonstrates, the prepaying principal rule, that if the total of all the principal portions of payments #13 to #24, are paid along with the 12th payment.
The total savings in interest is the total of the interest portions of payments #13 to #24. The Interest and Principal columns from #13 to #24 are highlighted with the mouse so that the totals are shown on the bottom (Selected)
This example screen above, demonstrates paying the total of the principal portions of the 12 blended payments in the second year, #13 to #24, are paid along with the 12th payment. The total savings in interest is the total of the interest portions of payments #13 to #24. The savings in interest of $11,934.45 is due to prepaying principal in the amount of $408.87 of principal, as can be seen directly on the Selected line on the SPREADSHEET above.
$270,308.72 Calculator Int.
– 258,373.40 Spreadsheet Int.
– =========
$11.935.32 savings
The 87cent difference is insignificant as explained earlier due to rounding and decimal place precision.