Loan Amortization
Your family is buying its first home in Denver and the lender says: "a fixed monthly payment of so many dollars for 30 years". That payment does not come out of nowhere: it comes from one formula applied to the money you owe. By the end of this page you will be able to work out that payment yourself, and see how much of it is really interest.
The monthly payment formula
Each month the balance grows by the interest and shrinks by the payment. The payment $a$ is chosen so that the balance after the last payment, $C_n$, is exactly $0$. You don't need to derive it: you just use the formula below (in a spreadsheet it is the PMT function).
A high school student wants to buy an electric mountain bike costing $\$2,400$. The store offers financing at a nominal annual interest rate of $4.8 \%…
A high school student wants to buy an electric mountain bike costing $\$2,400$. The store offers financing at a nominal annual interest rate of $4.8 \%$ spread across monthly installments. Calculate what monthly interest rate ($i$) will be applied to each installment, expressing it both as a percentage and as a decimal.
To set up a small recording studio and start his YouTube channel, Mateo has taken out a loan of $4\,500$ dollars to be repaid over 2 years in equal monthly installments.…
To set up a small recording studio and start his YouTube channel, Mateo has taken out a loan of $4\,500$ dollars to be repaid over 2 years in equal monthly installments. The bank charges a nominal annual interest rate of $6\%$. Calculate the amount of the monthly installment that Mateo must pay.
A 24-year-old female entrepreneur decides to open a graphic design studio. To acquire the necessary equipment (high-end computers, software licenses and furniture), she…
A 24-year-old female entrepreneur decides to open a graphic design studio. To acquire the necessary equipment (high-end computers, software licenses and furniture), she applies for a loan from a financial institution to be repaid over 5 years in equal monthly installments of $\$425.50$. The institution charges a nominal annual interest rate of $5.4 \%$.
Calculate the initial principal that the bank granted her to set up her business. Round the result to the nearest dollars cent.
Breakdown of the payment: interest and principal
Each payment $a$ is split into two parts: the interest for the period (what it costs to hold the debt) and the principal (the amortization, what really reduces what you owe).
Emma has taken out a loan of $\$3,000…
Emma has taken out a loan of $\$3,000$ to pay for her college tuition and course materials. The bank applies the French amortization system (equal installments) with a monthly interest rate of $0.5 \%$. If the monthly installment is $\$133.06$, calculate what amount of the first installment corresponds exclusively to interest payment.
A young entrepreneur applies for a loan of $12\,000$ dollars to buy a used van for her delivery business. The loan will be paid off over 4 years with quarterly payments…
A young entrepreneur applies for a loan of $12\,000$ dollars to buy a used van for her delivery business. The loan will be paid off over 4 years with quarterly payments and an annual interest rate of $8\%$. Determine the portion of the first payment that goes toward interest and how much is actually used to pay down the debt.
A family takes out a 20-year mortgage of $\$160,000$ at a fixed annual interest rate of $3.25 \%…
A family takes out a 20-year mortgage of $\$160,000$ at a fixed annual interest rate of $3.25 \%$ with monthly payments. After exactly 10 years of faithfully paying the monthly installments, they receive an inheritance and consider paying off the outstanding debt.
Calculate the breakdown of the 121st installment, specifying which part corresponds to interest payment and which part to principal amortization.
The amortization schedule
| Quarter | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 0 | — | — | — | $\$8{,}000.00$ |
| 1 | $\$589.20$ | $\$160.00$ | $\$429.20$ | $\$7{,}570.80$ |
| 2 | $\$589.20$ | $\$151.42$ | $\$437.78$ | $\$7{,}133.02$ |
| 3 | $\$589.20$ | $\$142.66$ | $\$446.54$ | $\$6{,}686.48$ |
Given the following incomplete amortization schedule for a loan for a graphics tablet, calculate the missing values marked with letters, knowing that the monthly…
Given the following incomplete amortization schedule for a loan for a graphics tablet, calculate the missing values marked with letters, knowing that the monthly interest rate is $1 \%$:
| Month | Outstanding balance | Interest | Principal repaid | Payment (Annuity) |
|---|---|---|---|---|
| 1 | $\$500.00$ | $\$5.00$ | $\$40.00$ | (A) |
| 2 | (B) | $\$4.60$ | $\$40.40$ | $\$45.00$ |
A group of friends decides to buy a professional mixing console that costs $1\,800$ dollars. They pay $300$ dollars in cash and finance the rest at an interest rate of…
A group of friends decides to buy a professional mixing console that costs $1\,800$ dollars. They pay $300$ dollars in cash and finance the rest at an interest rate of $10\%$ per year (nominal, with a monthly compounding period, that is, a monthly rate $i=\frac{0.10}{12}$) to pay it back over 6 months with constant monthly payments.
Remember that the constant payment $a$ that repays a principal $C$ in $n$ periods with rate $i$ per period is:
$a = C\times\dfrac{i}{1-(1+i)^{-n}}$
Create the first two rows of the amortization table, stating in each one: outstanding principal, interest for the period, principal repaid and monthly payment. Round to the nearest cent.
We compare two loan options to finance a renovation of $\$30,000$ to be repaid over 4 years with monthly payments: Option A: Nominal annual interest of $5 \%…
We compare two loan options to finance a renovation of $\$30,000$ to be repaid over 4 years with monthly payments:
-
Option A: Nominal annual interest of $5 \%$ with no initial fees.
-
Option B: Nominal annual interest of $4.2 \%$ but with an origination fee of $1.5 \%$ on the principal requested, paid in cash at signing.
Calculate the total cost of each option (sum of all installments plus fees) and determine which is more advantageous for the client.
Paying off a loan early
Sometimes some extra money comes your way (a tax refund, a bonus, a gift) and you want to put it toward your loan. An extra payment goes to the principal, so the balance drops right away. You then have two options:
To finance an end-of-year trip, a student association takes out a loan of $3\,000$ dollars at an annual compound interest rate of $5\%$. The agreement is to repay it in…
To finance an end-of-year trip, a student association takes out a loan of $3\,000$ dollars at an annual compound interest rate of $5\%$. The agreement is to repay it in a single lump sum payment after 18 months. What will the total interest amount to? If they had chosen to amortize it monthly over the same period, would they pay more or less interest in total? Reason your answer without needing to calculate the complete schedule.
A mortgage loan of $\$200,000$ is taken out over a period of 25 years with monthly installments. For the first two years, the interest rate is fixed at $2.5 \%…
A mortgage loan of $\$200,000$ is taken out over a period of 25 years with monthly installments. For the first two years, the interest rate is fixed at $2.5 \%$ per year. From the start of the third year, the interest rate becomes variable, changing to $4.75 \%$ per year.
Calculate by how much the family's monthly installment will increase from the third year onwards due to this interest rate rise.
Lab: building and modifying the amortization schedule
Work in Excel or Google Sheets with a loan of $\$5{,}000$ over 24 months, 6% APR, no fees. Predict whether lowering the monthly payment by lengthening the term also reduces the total cost. Enter in a spreadsheet: H1 = 5000, H2 = 0.005 and H3 = 24; H4 contains =PMT(H2,H3,-H1), which gives the same result as =H1*H2/(1-(1+H2)^(-H3)). If H2 is zero, use =H1/H3.
In row 2, put period 0 in A2 and the initial balance =H1 in E2. Row 3 represents the first payment: A3 =A2+1, B3 =$H$4 (payment), C3 =E2*$H$2 (interest), D3 =B3-C3 (principal) and E3 =E2-D3 (balance). Drag down to period 24. Format to two decimal places, keeping internal precision.
The recurrence lets you see how a payment reduces the debt:
Modify the schedule to include an extra payment of $\$500$ just after payment 12: subtract it from the balance of that period and recalculate the payment for the remaining 12. Compare keeping the term with keeping the payment; in the second case, stop the process when the debt is cleared and adjust the last payment. Also try zero interest and check that the balance never goes on to become negative. Compare the recurrence and the closed formula: which helps with changes in conditions and which avoids going through all the rows. Submit a recommendation justified by both monthly payment and total cost, not just one of them.
According to data from the Mortgage Statistics of the National Statistics Institute (INE), the average amount of mortgage loans for home purchases in Spain is around…
According to data from the Mortgage Statistics of the National Statistics Institute (INE), the average amount of mortgage loans for home purchases in Spain is around $\$144,000$, with an average amortization term of 24 years at a fixed nominal annual interest rate ($\text{TIN}$) of $3.6 \%$ through constant monthly payments.
- a) Model the described average mortgage: calculate the monthly interest rate $i$, the total number of payments $n$, and the constant monthly payment $a$ paid by the buyer.
- b) Break down the first monthly payment ($k=1$): calculate how much is used to pay interest ($I_1$) and how much to amortize borrowed principal ($A_1$). Determine what percentage of the first payment corresponds to interest and what percentage directly amortizes debt.
- c) The Bank of Spain recommends that the monthly mortgage payment should not exceed $35 \%$ of net household income. Determine what the minimum net monthly income of a household must be in order to take out this average mortgage while meeting this financial prudence criterion.