[Valachi's Homepage]

A. Introduction

Solving mortgage calculation problems can be accomplished quickly and accurately with the aid of computer spreadsheet packages such as EXCEL. The following EXCEL-based examples are designed to guide you through the process of solving the basic types of problems encountered in various real estate courses. In reading this section, it is assumed that the reader is familiar with the process of compounding and discounting in finance.

[back]

B. The Basic Layout

After opening EXCEL, you should see a grid of cells with each column labeled alphabetically and each row labeled numerically. A cell is identified as the intersection of a column and a row, such as B4 (shaded).

Above this grid is a series of pull-down menus, which can be activated by clicking on the desired menu title (e.g., FILE, EDIT, VIEW, etc.), and a set of tool-bars containing pictorial shortcut keys known as icons. These icons are designed to simplify the processing and formatting of information contained within your spreadsheet. Throughout the following examples, we will provide instructions using both the pull-down menus and shortcut icons.

[back]

C. Getting Started

Mortgage operations employ the same five pieces of information:

• Number of periods (N)
• Periodic interest rate (I)
• Present value (PV)
• Periodic payment (PMT)
• Future value (FV)

Given any four of these operations, you should be able to solve for the fifth unknown quantity. Because all mortgage operations incorporate this same set of information, one easy way to solve many problems quickly is to develop a spreadsheet template that incorporates this information. Enter each of the five input labels in column A as shown.

Values for each of these components then may be entered in column B and referenced in formulas to solve for the unknown factor. The primary advantage of using this method is the ability to alter one or more of the inputs and let the computer automatically recalculate the new solution. The spreadsheet template now can be used to solve basic mortgage calculation problems.

[back]

D. Fully-Amortizing, Constant Payment Mortgage -- Monthly Payment

Perhaps the most common loan payment pattern used in real estate finance is the fully amortizing, constant payment mortgage. This payment pattern means simply that a level, or constant, monthly payment is calculated on the original loan amount at a fixed interest rate for a given term. The payment includes interest and some repayment of principal. At the end of the term the original loan amount is completely repaid, or full amortized. However, the amount of amortization varies each month.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years. Calculate the monthly payment. To solve this problem, enter the appropriate information into the input section of your template as shown.

The monthly payment is $617.17. Cell B7 is shaded to indicate it is a solution rather than an input assumption. To replicate this answer using your spreadsheet, do the following:

[back]

E. Monthly and Annual Loan Constants

The mortgage constant represents the amount of each periodic loan payment expressed as a percentage of the original loan, necessary to pay the contract rate of interest and the entire principal in equal periodic installments over the term of the loan. The mortgage constant is sometimes called the installment to amortize since it is the periodic payment necessary to repay a loan of $1 completely without resorting to a balloon payment. Thus, the mortgage constant is always the periodic payment for a loan of $1 expressed on an annual basis.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years. Calculate the monthly and annual loan constants. Enter the following information into the input section of the template.

The monthly loan constant (i.e., the periodic payment on a loan amount of $1) is 0.01029, as shown in cell B7. The annual loan constant, shown in cell B8, is 0.12343. The calculation was made as follows:

[back]

F. Loan Amortization Schedule

A table showing the loan principal amount outstanding and the allocation of the fixed mortgage payment between interest and a return of principal is called an amortization schedule.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years (360 months); monthly payment, $617.17. Prepare a monthly loan amortization schedule for months 1 through 6. For simplicity, assume that you already computed the monthly payment using EXCEL (see section D., above). Enter the following column headings in cells A7 through E7: Period, Payment, Interest, Principal, and Balance.

The amortization schedule was calculated as follows:

Note: Now that we have the amortization template set up, you might want to try some "what if" analysis. For example, click on cell B2 (periodic interest rate) and change 12% to 11% and press Enter. Notice that the monthly payment changes from $617.17 to $571.39 and the amortization schedule changes accordingly.

[back]

G. Outstanding Loan Balance

Because most mortgage loans are repaid before they mature, mortgage lenders must be able to determine the balance on a fully amortized, constant payment loan at any time.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the outstanding loan balance at the end of 10 years. Enter the new assumption information into the input section of the template. The outstanding loan balance is equal to the present value (PV) of the remaining monthly payments over 20 years.

The loan balance of $56,051 shown in cell B7 was calculated, using present value (PV), as follows:

Alternatively, the same solution to the problem could have been calculated using future value (FV). We must change the number of periods from 20 years to 10 years, and insert the original loan amount of $60,000 in the input section of the template.

The loan balance of $56,050 at the end of year 10 was calculated as follows:

[back]

H. Total Interest Paid -- 30-Year Loan Versus 15-Year Loan

The total interest paid on a fully amortizing, constant payment loan is determined by simply: (1) multiplying the monthly payment (which consists of principal and interest) by the number of months to maturity, and (2) subtracting the original loan principal from the result computed in step (1).

Example (30-year loan): Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the total interest paid over 30 years. Enter the relevant assumptions into the input section of the template.

The total interest paid over 30 years of $162,181 was calculated as follows:

Example (15-year loan): Loan amount, $60,000; interest rate, 12%; number of periods, 15 years. Calculate the monthly payment and the total interest paid over 15 years. Enter the assumptions into the input section of the template.

The monthly payment of $720.10 and the total interest paid of $69,618 were calculated as follows:

[back]

I. 30-Year Loan: Specified Higher Payment, Early Payoff

In our illustration of how the amortization schedule works, we saw that each month the lender computes interest due on the outstanding balance of the loan. By prepaying part of the balance of the loan, you can avoid interest charges on that sum. That is, by prepaying a portion of the balance due, you are eliminating the interest cost on that amount month after month, throughout the duration of the loan. By prepaying a small amount of the principal balance each month, you are constantly reducing the amount upon which the lender could charge interest.

By making a prepayment, you are saving all of the compound interest the lender was planning to charge on that amount for the duration of the loan. Prepayments are not additional costs; they are simply small amounts of principal paid before they were due. It should be noted, by prepaying your mortgage you will be losing some interest deductions for income tax purposes and you will be parting with cash that could potentially be used for other investments (that could yield a higher return).

In this section, we will illustrate the mechanics of the fixed-sum prepayment plan. Under this plan, your prepayments are fixed in amount each month.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the new loan term, assuming that an extra payment of $40 per month is paid in addition to the regular monthly payment of $617.17 (or $657.17). Enter the relevant assumptions into the input section of the template.

The new loan term of approximately 245 months was calculated as follows:

[back]

J. 30-Year Loan: Specified Early Payoff, Higher Payment

In Section I., above, we illustrated the fixed-sum prepayment plan for reducing mortgage costs. In this section, we will illustrate the mechanics of the fixed-term prepayment plan. Under this plan, you choose the length of the mortgage term you desire and your payments are set to that term.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the new monthly payment, assuming the loan is to be paid off in 20 years. Enter the relevant assumptions into the template.

The new monthly payment of $660.65 was calculated as follows:

[back]

K. Loan Fees and Effective Borrowing Cost

Loan fees or points are incurred in many types of real estate financing arrangements. Points are additional interest charged by a lender at origination of a loan; one point equals 1% of the loan amount. These charges increase borrowing costs. The focus in this section is on how to include these charges in computing the effective borrowing cost (or effective interest rate) on loan alternatives when financing a real estate transaction.

The lender must disclose to the borrower the annual percentage rate (APR) being charged on a loan. Calculation of the APR is generally made in the manner shown in the example below, assuming the loan will be outstanding until maturity. The APR in the example would be disclosed at loan closing to the borrower by rounding the effective interest rate up or down to the nearest 1/8 of 1%. In the example, the 12.41% effective rate would be rounded to the nearest 1/8 of 1% and disclosed to be 12.375%. The APR reflects origination fees and points and treats them as additional income or yield to the lender.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the effective interest rate to the borrower (yield, or IRR, to the lender), assuming the lender charges 3 points ($1,800) and the loan is held to maturity. Assuming that the borrower pays the points from separate funds, the lender actually "invests" $58,200 ($60,000 - $1,800). Enter the assumptions into the input section of the template.

The effective interest rate of 12.41% was calculated as follows:

[back]

L. Loan Fees, Early Payoff, and Effective Borrowing Cost

When loan fees are charged and the loan is paid off before maturity, the effective interest cost of the loan increases even further than when the loan is repaid at maturity.

Example: Loan amount, $60,000; interest rate, number of periods, 30 years; monthly payment, $617.17. Calculate the effective interest rate to the borrower (yield, or IRR, to the lender), assuming the lender charges 3 points ($1,800 that the borrower pays from separate funds) and the loan is paid off in 5 years. The initial step is to calculate the loan balance at the end of 5 years. Enter the following relevant assumptions into the input section of the template. 

The outstanding balance of the loan at the end of 5 years of $58,598 was calculated as follows:

The second step is to calculate the effective interest rate. Enter the following assumptions into the template.

The effective interest rate of 12.82% was calculated as follows:

[back]

M. Loan Fees, Early Payoff, Prepayment Penalty and Effective Borrowing Cost

Many mortgages provide that the borrower must pay a prepayment penalty should the borrower desire to repay the loan before the maturity date. Because of the use of prepayment penalties, we want to know the effective mortgage loan yield (interest cost) when both loan origination fees and a prepayment penalty are charged on the loan.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the effective interest cost to the borrower (yield, or IRR, to the lender), assuming the lender charges 3 points ($1,800 that the borrower pays from separate funds), the loan is paid off in 5 years, and the lender charges a prepayment penalty of 3% of the outstanding balance ($1,758). The initial step is to calculate the loan balance at the end of 5 years. Enter the relevant information into the template.

The loan balance at the end of year 5 of $60,356 was calculated as follows:

The second step is to calculate the effective interest rate. Enter the following assumptions into the template.

The effective interest rate of 13.25% was calculated as follows:

[back]

N. Charging Fees to Achieve a Yield

In the preceding examples, we have developed the notion of the effective borrowing cost and yield from a given set of loan terms. However, we should consider how these are determined by lenders when pricing a loan. By continually monitoring investment alternatives and competitive conditions, lenders establish loan offer terms for various categories of loans, given established underwriting and credit standards for borrowers. Hence, a set of terms designed to achieve a competitive yield on categories of loans representing various ratios of loan-to-property value are established for borrowers who are acceptable risks.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. Calculate the number of points the lender must charge to achieve a 13% yield (IRR), assuming that the loan is expected to be repaid at the end of 10 years. The initial step is to calculate the loan balance at the end of year 10. Enter the relevant assumptions into the template.

The loan balance at the end of year 10 of $56,050 was calculated as follows:

The next step is to calculate the amount to be funded by the lender and the number of points to be charged. Enter the new information into the input section of the template.

The loan amount funded by the lender of $56,717 and the points charged of 5.47% were calculated as follows:

[back]

O. Market Value of a Loan

The market value of a loan is the amount that a new lender or investor would pay to receive the remaining payments on the loan. It can be thought of as the amount that could be loaned so that the remaining payments on the loan would give the lender a return equal to the current market rate of interest.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. The market interest rate is 16%. Calculate the market value of the loan, assuming that it is held to maturity. Enter the relevant assumptions into the input section of the template.

The market value of the loan (at 16%) of $45,895 was calculated as follows:

[back]

P. Yield to Loan Investor, Early Payoff

If the loan in Section O., above, is purchased at a discount and it is paid off early (i.e., prior to the maturity date), the effective yield (IRR) to the investor is higher than if the loan had been held to maturity.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 years; monthly payment, $617.17. The loan is purchased by an investor for $45,895 (see previous example). Calculate the yield (IRR) to the investor, assuming that the loan is paid off at the end of 5 years. The initial step is to calculate the loan balance at the end of year 5. Enter the relevant assumptions into the template.

The outstanding loan balance at the end of year 5 of $58,598 was calculated as follows:

The second step is to calculate the effective interest rate. Enter the following assumptions into the template.

The annual effective yield (IRR) of 19.45% was calculated as follows:

[back]

Partially amortized mortgages require periodic payments but, unlike the fully amortized loan, the loan is not paid off completely by the payments. A balance remains at maturity that must be repaid in one relatively large lump sum; the final principal payment is called a balloon payment.

Example: Loan amount, $60,000; interest rate, 12%; number of periods, 30 (i.e., amortized over 30 years), due in 10 years; monthly payment, $617.17 (based on a 30-year amortization schedule). Calculate the balloon payment at the end of year 10. Enter the relevant assumptions into the template.

 The balloon payment was calculated as follows:

[back]

R. Practice Problems

[back | Home | TVM Calculation | Finance Department Homepage]