Almost everyone has to borrow money sometime. For example, if you are buying or already own a house or rental property, you will have to take out a mortgage and pay it down or if you buy a car on credit, you will have to pay some amount each month. Whenever you have to pay back a loan, you can use the spreadsheet provided to determine the payment amount, the interest you are paying each payment and how the loan amount decreases over time. You can also use it to evaluate different alternatives. You may be surprised how much is interest early in the loan.
When you take out a loan, there are only a few parameters that you must specify to determine how much is paid against the amount owing and how much goes towards interest. In addition, you need to know how interest is calculated so that you can compare different loan alternatives.
This spreadsheet can be used for both a loan and a mortgage as there is really no difference between them when calculating the repayment schedule. Terms that apply to each are different and they can be confusing. However, the approach is the same when determining the repayment schedule.
Why Another Mortgage Calculator
When I did a Google search for “Mortgage Calculator”, it came back with over 50 million results! I thought that the over 30 million results for a search for “Retirement Calculator” that would list my RetireBase application was a lot, but I still published it as I think it provides a set of features that no other calculator provides. The same is true with the mortgage calculator spreadsheet provided with this post.
In particular, I want to make sure that the interest calculations are correct and that users can have an ownership of their data on their own computer. I also want to add a mortgage calculator to my catalog of spreadsheets that I can use for other purposes. For example, a future post will deal with the dangers of moving debts and topping-up your mortgage, just because the interest rate is lower than those on your credit cards or line of credit.
There are many very good mortgage calculators on the internet. All banks and financial institutions provide online calculators and there are many private ones. There are also many that provide a spreadsheet that you can download. Not all use the interest calculations described below and used in the spreadsheet provided by this post, so examine them carefully before relying on them.
The Five Parameters That You Must Specify
To calculate an amortized loan (i.e. one that will have a zero amount after a set number of payment periods), the following must be provided. These are used in the spreadsheet.
- Starting Amount – The principal for a mortgage or the amount of the loan.
- Annual Percentage Rate – This is the quoted rate (see below for more detail).
- Compounding Frequency – Usually annually, semi-annually, quarterly, monthly or daily (1, 2, 4, 12 or 365 times per year, respectively) as described below. This is set by the lender.
- Amortization Years – The number of years for the loan (not the term of the loan, which is usually 5 years for mortgages before they have to be renewed or refinanced).
- Payment Periods/Year – The number of payments that will be made each year. Usually this is 1, 2, 12, 26 or 52 for yearly, semi-annually, monthly, half-monthly, bi-weekly, or weekly, respectively. Usually, you determine this based on when you are paid and it can be different from the compounding frequency.
- Payment/Period – The payment against the remaining loan or mortgage amount and the interest for each of the periods specified above. This is calculated by using a formula to fit the amortization years, but the spreadsheet permits you to enter any value you want, which will change the years.
There Are Three Interest Rates Involved
Interest is basically the cost of borrowing money. It is usually expressed as a percent which is then used to determine the interest on the amount left on the loan. However, there are 3 interest rates that are involved in determining the actual interest: Annual Percentage Rate, Effective Annual Rate and Interest Rate/Period.
Annual Percentage Rate (APR) – The financial institution that is loaning you the money will quote a rate, for example, 6%. By convention, this rate is called the Annual Percentage Rate and abbreviated as APR. APR is a very well-known and used rate.
Effective Annual Rate – However, the APR is normally not the interest you pay on the loan because the institution can decide how compounding is to be done. For example, Canadian banks are required by law to compound semi-annually, while in the U.S.A. it is monthly. The yearly interest rate that this results in is always higher than the APR. This rate is referred to as the Effective Annual Rate (EAR) and it is also a well-known and used rate. When getting a quote for a loan always ask for the Effective Annual Rate as you can then compare loans. The EAR is calculated from the APR using the compounding frequency. If you are interested in the equations, they are at the end of this post.
Interest Rate/Period – There is one more rate that you need to know about and that is the Interest Rate Per Period. It is this rate that is applied to the amount at the end of the period to calculate the interest for the period. It is calculated from the EAR and the number of payments per year. Again, the equations are given at the end of this post.
Click on Download Spreadsheet and open it so that you can try out your own situation. The spreadsheet is loaded with data for the example given below so that you can see the type of values to be used.
To clear any cell, just click on it and press the Delete key and then the Tab key or click on another cell. Only delete cells that are outlined or else you will remove the equations that are used for calculations.
The spreadsheet has four area: Entering Data starting in the top-left below the credits; Calculated or Actual values to the right of it, 4 charts and the Amortization Schedule starting at row 31. Each of the variables in the first two areas have numbers to the right. These are used in the description below to make it easier to find the corresponding line on the spreadsheet.
The best way to explain the spreadsheet is use the data pre-loaded into the spreadsheet. You can then adjust it to your own circumstances. You can also try out different options, such as interest rates to see how the totals change.
Starting Amount (#1) – Enter the principal of mortgage or the amount of the loan. Do not enter any other expenses unless they are added or subtracted from the principal.
Annual Percentage Rate – (#2) Enter the quoted rate for the mortgage. The Effective Annual Rate is calculated in #13 and the Interest Rate/Period is calculated in #14.
Compounding Frequency (#3) – When the cell is entered, a drop-down menu will appear to the right. Select one of the values given: Annually, Semi-annually or Monthly.
Amortization Years (#4) – Enter the number of years that the loan or mortgage is to be paid over. It will accept a decimal as well (e.g. 11.25).
Payment Periods/Year (#5) – When the cell is entered, a drop-down menu will appear to the right. Select one of the values given: 1, 12, 24, 26 or 52 (for annually, monthly, twice-monthly, bi-weekly or weekly, respectively).
Payment/Period (#6) – This value has been set to be that of #16 (By Formula) which is calculated so that the payments will take the amount to zero at the end of the amortization years. You may enter a value into this cell and override the default. However, if you change any of the first 5 variables, the charts and results may not show any results. In this case you can enter the value found in #16 or enter a formula to use the value of #16 directly. The formula to enter into cell D11 is =H11. If you are doing a monthly budget, use this amount for the mortgage.
Start Date (#7) – Enter the date the mortgage either started or is set to start. It is formatted for yyyy-mm-dd to make it easier to distinguish between the month and the day.
Payment/Year (#8) – This is calculated by multiplying the Payment Periods/Year by the Payment/Period (#5 x #6). If you are doing a yearly budget, use this amount for the mortgage.
Years x Periods/Year (#9) – This is calculated by multiplying Amortization Years by Payment Periods/Year (#4 x #5). Compare this to #19 which is the Actual Total Periods which will different if a value has been entered into Payment/Period (#6) that is different from that in #16.
$ Rounding Error (#11) – If you enter a value into #6, some of the charts may have the x-axis shifted downward. Just enter a larger number into this cell to restore the chart. The largest value I have encountered is $12.00.
Chart 1 has 4 lines graphs plotted against the % or the Total Periods. This removes the number of periods and means that the x-axis and all lines always goes from 0% to 100%. Compare this to Chart 2 which shows the Period # along the x-axis and all lines stop 5 years before the last value on the axis due to how Excel plots values. Using a percent makes it easier to see how fast the Remaining Amount (blue line) is being reduced. Notice that, for the example, it takes about 60% of the 25 years to pay off 50% of the loan. The red line is the cumulated interest which levels off after about 80%. The X marks the Now value or the location of Today’s date. The remaining 2 lines are the Cumulated Payments (purple) and Cumulated Amount (light blue).
Chart 3 shows the amounts paid to principal and interest for each payment. This is very useful because it shows how much interest consumes early in the mortgage. The blue line is for the $ Applied to the Amount and the red line is for the $ Interest/Period. Notice in the example that payment to the principal and interest are equal for the first few years.
Chart 4 is a slightly different chart because it plots the % Amount Left (blue) and the % Interest/Period (red). The former has the same shape as the Remaining Amount of Chart 1 while the latter has the same shape as the Interest/Period of Chart 2. Giving them as a percent instead of dollar amounts makes it clear how much is either left of the principal or spent on interest.
Total Payments (#12) This is calculated by summing 2 columns in the Amortization Table: Payment/Period and Extra Payment. Subtracting the Starting Amount (#1) from this amount yields the Total Interest paid, which is shown on #20.
Effective Annual Rate (#13) – See the description above and at the end of this post for details.
Interest Rate/Period (#14) – See the description above and at the end of this post for details. The Excel EFFECT function is used instead of the formula described at the end of this post because it is faster and has less coding. (The nominal_rate is the Annual Percentage Rate and the npery is the compounding frequency.)
Actual Years (#15) – This is calculated by multiplying Actual Total Periods (#19) by Payment Periods/Year (#12). Compare this to Amortization Years (#25). They will be different if the Payment/Period (#6) is not the same as the By Formula amount (#16).
By Formula (#16) – The formula used is the Excel PMT (Payment) function as it is faster than using the equation. It uses the Starting Amount (#1), Amortization Years (#4), Payment Periods/Year (#5) and the Interest Rate/Period (#14) to give the Payment/Period needed to take the Remaining Amount to zero at the end or the amortization years.
Last Payment Date (#17) – This is found from the Mortgage Schedule by looking for the last date showing. It is useful to see this date without having to scroll down in the Schedule.
Now (#18) – This has the Today Excel formula so that each time the spreadsheet is opened this date will change. It is used in the Now column of the Schedule and at a few support columns to put an X on the charts to show Today’s date. This is very useful to see where you are on the repayment of the loan.
Actual Total Periods (#19) – This is obtained from the Remaining Amount column of the Schedule by looking for when it becomes zero. Compare it to #9.
Total Interest (#20) – This is calculated by summing the Interest/Payment column of the Mortgage Schedule.
Total Interest/Amount (#21) – Dividing the Total Interest (#20) by the Starting Amount (#1) gives the percent shown in this cell. For large interest rates and long amortization, this amount may surprise you.
The Amortization Table starts at row 31. It has 9 columns and enough rows for a 30 year mortgage paid weekly. The rows are
- Period #
- Payment Date
- $ Starting Amount
- Extra Payment
- $ Interest/Period
- $Applied to Amount
- $Remaining Amount
Each of the cells in the table (except Extra Payment) have formulas that are not to be modified or else the calculated values and charts will be incorrect.
If you want to make any additional payments in a period, enter it into the Extra Payment column.
When the Remaining Amount reaches zero, the Period # value is set to #NA. This is done so that the charts will not extend beyond this period.
To Print the Schedule and not include all the extra rows that have #NA, either change the Print Area (under the file menu) or only print the pages that have real data.
Additional Schedule Columns
To the right of the Remaining Amount column are columns used for the charts. Do not change any of the formulas or the charts will be in error.
How is Effective Annual Rate Calculated?
Now that you know the 3 interest rates, how are they related and how are they used to calculate the actual interest that you pay each period? The following shows how this is done.
The Compounding Frequency (CF) determines how the Annual Percentage Rate (APR) is applied to your loan. Basically, the APR is divided by the number of times compounding is done in a year and then compounding is applied.
For example, for a 6% APR and semi-annual compounding (i.e. twice per year), the rate applied each 6 months would be 6% / 2 = 3%. If compounding is monthly, 6% / 12 = .5% would be used each month.
Now, the interest rate for each time compounding is applied is used to determine the Effective Annual Rate (EAR) by using the standard compound interest formula as follows (the symbol ^ means the power of):
- EAR = ( ( 1 + ( APR / CF ) ) ^ CF ) – 1
with APR and EAR expressed as a decimal and CF is a whole number.
For example, a 10% APR and semi-annual compounding (2 per year), the formula is
- EAR = ( ( 1 + ( .10 / 2 ) ) ^ 2 ) – 1
- EAR = ( ( 1.05 ) ^ 2 ) – 1
- EAR = ( 1.1025 ) – 1
- EAR = .1025 (or 10.25%)
That is, for a 10% quoted rate (or Annual Percentage Rate) with semi-annual compounding, the Effective Annual Rate is 10.25% or .25 of a percent higher than the quoted rate. If monthly compounding is used, the 10% quoted rate results in an EAR of 10.47131%.
For 6%, semi-annually, the EAR is 6.09%. For 6%, monthly, the EAR is 6.16778%.
Clearly semi-annual compounding is to your advantage compared to monthly.
How is Interest Rate/Period Calculated?
You might think that the Interest Rate per Period can be simply calculated by dividing the EAR by the number of periods in a year that payments are made. However, this would result in a yearly interest that is higher than the EAR because of the compounding effect for each period. Instead the formula to be used is the inverse of the standard compounding formula as shown below.
We start with what is desired at the end of the year (the EAR), and use the Interest Rate/Period (IRP) and the Number of Periods (NP) in the compounding formula and then solve for IRP.
- EAR = ( ( 1 + IRP ) ^ NP ) – 1
and solving for IRP, we have
- IRP = ( ( 1 + EAR ) ^ ( 1 / NP ) ) – 1
For a 10% Quoted Rate, compounded semi-annually which results in an EAR of 10.25%, and monthly payments, the equation for IRP is
- IRP = ( ( 1 + .1025 ) ^ ( 1 / 12 ) ) – 1
- IRP = ( ( 1.1025 ) ^ ( 1 / 12 ) ) – 1
- IRP = ( 1.0081648… ) – 1
- IRP = .0081648… (or .81648…%)
That is, the interest payments per month will be .81648…% of the amount owing at the start of the period. When applied to the loan, the result after 12 payments will be the EAR.
You can check this on the spreadsheet by using a starting amount of $100,000, Annual Percentage Rate of 10%, semi-annual compounding, 12 Payments Periods/Year and a $0 payment. The result at the end of the 12th period is $110,250 which is exactly the Effective Annual Rate.
Scatter charts are used because they will only plot values that do not have an #NA value. By setting the Period # column to have this value when the previous period’s Remaining Amount is zero, the charts will stop at this previous month. This feature is used in the spreadsheet provided by Managing Expenses and Cash Flow – Part 4: Design and described in the “Getting Cash Flow Chart to Stop at an Empty Month” section. It is also used in a spreadsheet provided by vertex42.com. I adapted some of the formulas used on the spreadsheet and am grateful to the author. Vertex42 has a large catalog of Excel spreadsheets.
Interest rate formulas – Many references to the formulas can be found by an internet search for APR and EAR. After dealing with all of the different terms and the different terminology for the equations, I settled on the ones used in this post. There is no one reference that I recommend because they can cause confusion with the terms and description used in this post. They also do provide the formulas for all three interest rates. However, the equations used here are the standard compound interest formula and are used by all references, with different variables. Some references that may be of use if you want more background are the following: Investopedia APR and EAR, York University (A. Marshal) course notes – A Guide to Mortgage Interest Calculations in Canada and a diagram showing the relationship between Quoted Rate vs. EAR vs. APR.
Online Loan and Mortgage Calculators – There are many, many available on the internet. One that I like because it has a set of sliders to enter the variables, it uses the proper interest calculations and has charts and tables that can be displayed and printed by clicking on buttons can be found here.
Drop-down menus – Another feature of the spreadsheet provided by this post is the used of a drop-down menu for Compounding Frequency and Payment Periods/Year. How to create this menu is described in this Microsoft Support document. The defined names used are to the right of the charts.