Terms and Conditions of Loans and Mortgages – Excel

Loans are among the most significant financial transactions involving interest. Loan represents a contract between a borrower and lender. The borrower is provided by the lender with an amount of money, which is repaid later, and on which the interest is paid. Mortgage is generally a long-term loan used primarily for the purpose of purchasing a piece of property such as a home. The real estate property serves as a security for the repayment of the loan and the payment of the interest.

To make the best loan decision, one has to consider many factors. The key factors are: amortization, term of the loan, interest rate, payment schedule, prepayment privileges. Here are some definitions for these words.

Principal refers to the actually borrowed amount of money. Therefore from the borrower’s position this amount will decrease every time the payment is made and represents the balance.

Amortization refers to the number of years it would take to repay a loan at full.

The term of the loan refers to number of years (or months) for which interest rate is negotiated. In the case of loans, amortization usually equals to term. In the case of mortgages, term is usually shorter, from six months up to 10 years, where the usual amortization period is 25 years.

Interest rate: Loan agreement must contain a statement showing, among other things, the rate of interest calculated annually or semi-annually. Mortgage interest has traditionally been quoted as nominal annual rate based on semi-annual compounding.

Calculation of Loans: Example in Excel

A loan with a principal of $200,000 to be paid back to the bank in X years. The interest rate is 5%.

Based on the repayment schedule three forms of mortgage are examined:

1. loan with one repayment;
2. loan with constant periodic repayment;
3. loan with constant payments for the total of interest and repayment.

1. Loan with One Repayment: Panel 3.1

In this case, the borrower makes only one repayment that occurs at the end of amortization period, that is year 10. However borrower has to pay interest out of the current principal for every year over 10 years.

To proceed, start with the Repayment column since it is constant for years 1-9 and independent of all other columns. Value of Repayment in year 10 equals to original principal. Continue with calculation of Principal that decreases every year by Repayment made in previous year. Then, interest can be calculated for every year as current Principal multiplied by interest rate. Finally, total payment represents borrower’s cash flow; the amount of money the borrower has to pay to the lender and consists of Repayment and Interest.

You should build the table to pay only interest rate each year ($10000) and then a total payment at the end of the period of $200000 + $10000 for total payment of $300K. Remember you don’t make any repayment (against the principal throughout the term).
2. Loan with Constant Periodic Repayments

In this case, the borrower makes constant repayments in the amount of P/AP every year for 10 years. However borrower has to also pay interest out of current principal for every year over 10 years.
To proceed, start with the Repayment column since it is constant for years 1-15 and independent of all other columns. Continue with calculation of Principal that decreases every year by Repayment made in previous year. Then, Interest can be calculated for every year as current Principal multiplied by interest rate. Finally, Total payment represents borrower’s cash flow; the amount of money the borrower has to pay to the lender and consists of Repayment and Interest.

You should build the table to pay interest rate each year (5% of principal), and $10000 repayment each year, a total payment of $172K. Remember you don’t make any repayment (against the principal throughout the term).
3. Loan with Constant Payments for the Total of Interest and Repayment

Loans with constant repayments (example before) have the same repayment in each year, but the interest payments decrease over time as the principal becomes smaller. Therefore total payments borrower has to make every year vary from maximum of $20,000 to minimum of$11,000.

It may be more convenient for borrower to have a repayment scheme such, that the total payments (including repayment and interest are the same for each year! So we are interested in calculation ofloans with CONSTANT TOTAL PAYMENTS.

We examine two ways of calculating the constant total payments:

1. Algebraic calculation:

Let us denote:

Desired constant annual payment: A
The initial loan principal: P
Amortization period: t years
Interest rate: r

The FV of amount owed after t years according to compound interest: (1 + r)^t * P

Note, this amount represents how much borrower owes after ‘t’ years if he does not make any repayment at all during ‘AP’.

The FV of annual payments A during t years: ((1 + r)^t -1 / r )* A

Note this amount represents how much borrower repays in t years if every year repays amount of A.

Then, after t years, how much borrower owes must equal how much he repays, therefore:

A = rP / (1-(1+IR)^-AP)

The last expression gives us desired annual constant payments. In excel there is a special function that will enable this: PMT (r, t, -P) = PMT (IR, AP, -P)

That’s three examples for the price of one. Please post questions. We did not do the excel work for you so you’ll have to try it yourself.