In the excel setup of a loan amortization problem, which of the following occurs?

This lesson discusses the Periodic Repayment (PR), one of six compound interest functions presented in Assessors’ Handbook Section 505 (AH 505), Capitalization Formulas and Tables. The lesson:

• Explains the function’s meaning and purpose,
• Discusses the process of loan amortization,
• Provides the formula for the calculation of PR factors, and
• Contains practical examples of how to apply the PR factor.

PR: Meaning and Purpose

The PR is the payment amount, at periodic interest rate i and number of periods n, in which the present worth of the payments is equal to $1, assuming payments occur at the end of each period.

The PR is also called the loan amortization factor or loan payment factor, because the factor provides the payment amount per dollar of loan amount for a fully amortized loan. The PR factors are in column 6 of AH 505.

The PR can be thought of as the “opposite” of the PW$1/P which was discussed in Lesson 6; mathematically, the PR and the PW$1/P factors are reciprocals as shown below:

Conceptually, the PW$1/P factor provides the present value of a future series of periodic payments of $1, whereas the PR factor provides the equal periodic payments the present value of which is $1.

Loan Amortization

If a loan is repaid over its term in equal periodic installments, the loan is fully amortized. In a fully-amortized loan, each payment is part interest and part repayment of principal. Over the term of a fully amortized loan, the principal amount is entirely repaid.

From the standpoint of the lender, a loan is an investment. In an amortized loan, the portion of the payment that is interest provides the lender a return on the investment, and the portion of the payment that is principal repayment provides the lender a return of the investment.

An amortization schedule shows the distribution of loan payments between principal and interest throughout the entire term of a loan. Amortization schedules are useful because interest and principal repayment may be treated differently for income tax purposes and it is necessary to keep track of the separate amounts for each. The loan amortization schedule below shows an amortization schedule for a 10-year loan, at an annual rate of 6%, with annual payments.

The formula for the calculation of the PR factors is

Where:

• PR = Periodic Repayment Factor
• i = Periodic Interest Rate, often expressed as an annual percentage rate
• n = Number of Periods, often expressed in years

In order to calculate the PR factor for 4 years at an annual interest rate of 6%, use the formula below:

Viewed on a timeline:

On the timeline, the four payments are negative because from a borrower´s perspective they would be cash outflows. The amount borrowed, $1, is positive because from the borrower´s perspective it would be a cash inflow.

To locate the PR factor in AH 505, go to page 33 of AH 505. Go down 4 years and across to column 6. The PR factor is 0.288591.

Link to AH 505, page 33

Example 1:
You have just borrowed $50,000, to be repaid in equal annual installments at the end of each of the next 20 years. The annual interest rate is 8%. What is the amount of each annual payment?

Solution:

• PMT = PV × PR (8%, 20 yrs, annual)
• PMT = $50,000 × 0.101852
• PMT = $5,092.60

• Find the annual PR factor (annual compounding) for 8% at a term of 20 years. In AH 505, page 41, go down 20 years and across to column 6 to find the correct factor of 0.101852.
• The annual payment of $5,092.60 is the loan amount of $50,000 multiplied by the PR factor.

Link to AH 505, page 41

Example 2:
You borrow $200,000 to buy a house, using a fully-amortizing mortgage with monthly payments for 30 years at an annual interest rate of 5%. What is your monthly payment of principal and interest?

Solution:

• PMT = PV × PR (5%, 30 yrs, annual)
• PMT = $200,000 × 0.005368
• PMT = $1,073.60

• Find the monthly PR factor (monthly compounding) for 5% at a term of 30 years. In AH 505, page 28, go down 30 years and across to column 6 to find the correct factor of 0.005368.
• The monthly payment of $1,073.60 is equal to the loan amount multiplied by the monthly PR factor.

Link to AH 505, page 28

Example 3:
John borrows $75,000 at an annual rate of 6%, repayable in equal annual payments at the end of each of the next 10 years. How much of John’s first payment is principal and how much is interest?

Solution:

• PMT = PV × PR (6%, 10 yrs, annual)
• PMT = $75,000 × 0.135868
• PMT = $10,190

• Find the annual PR factor (annual compounding) for 6% at a term of 10 years. In AH 505, page 33, go down 10 years and across to column 6 to find the correct factor of 0.135868.
• The annual payment of $10,190 is the loan amount multiplied by the annual PR factor. The payment is divided between interest and principal repayment.
• The outstanding loan balance for the first year is $75,000, so the interest amount for the first year is $75,000 × 0.06 = $4,500.
• The amount of principal repayment is $10,190 (annual payment) - $4,500 (interest amount) = $5,690.

Link to AH 505, page 33

Example 4:
A friend just about ready to retire has $400,000 in his 401k retirement account. If he can earn an annual rate of 4% on the account and wishes to exhaust the fund over 20 years with equal annual withdrawals, how much can he withdraw at the end of each year?

Solution:

• PMT = PV × PR (4%, 20 yrs, annual)
• PMT = $400,000 × 0.073582
• PMT = $29,433

• Find the annual PR factor (annual compounding) for 4% at a term of 20 years. In AH 505, page 25, go down 20 years and across to column 6 to find the correct factor of 0.073582.
• The annual payment of $29,433 is the $400,000 balance in the retirement account multiplied by the annual PR factor.
• The friend could withdraw $29,433 at the end of each year for 20 years, assuming he could earn an annual rate of 4% on the account balance. After 20 years the account would be empty.

Link to AH 505, page 25

The primary use of the PR factor is to provide the amount of the periodic payment necessary to retire a given loan amount. But you can also use it to provide the amount of periodic payment that a given amount will support, assuming an annual interest rate and term, as in this example.

Example 5:
You take out a $100,000 mortgage loan at an annual rate of 6% with monthly payments for 30 years. You plan to sell the property after 12 years. At that time, what will be the outstanding balance (i.e., remaining principal) on the loan?

Solution:

The first step is to calculate the payment amount:

• PMT = PV × PR (6%, 30 yrs, monthly)
• PMT = $100,000 × 0.005996
• PMT = $599.60

• Find the monthly PR factor (monthly compounding) for 6% at a term of 30 years. In AH 505, page 32, go down 30 years and across to column 6 to find the correct monthly factor of 0.005996.
• The monthly payment of $599.60 is the loan amount of $100,000 multiplied by the monthly PR factor.

Link to AH 505, page 32

The remaining balance of an amortizing loan is the present value of the loan’s remaining payments discounted at the loan’s contract rate of interest. The second step is to discount the remaining 18 years of monthly payments using the PW$1/P factor at 6%.

• PV = PMT × PW$1/P (6%, 18 yrs, monthly)
• PV = $599.60 × 131.897876
• PV = $79,085.97

• Find the monthly PW$1/P factor (monthly compounding) for 6% for 18 years. In AH 505, page 32, go down 18 years and across to column 5 to find the correct monthly PW$1/P factor of 131.897876.
• The remaining loan balance of $79,085.97 is the payment amount of $599.60 multiplied by the PW$1/P factor.

Link to AH 505, page 32

Page 2

This lesson discusses the Mortgage Constant (MC), which is listed in the monthly tables of Assessors’ Handbook Section 505 (AH 505), Capitalization Formulas and Tables. The lesson:

• Explains the meaning and purpose of the MC,
• Explains how to find MC factors in AH 505 and calculate MC factors, and
• Contains practical examples of how to apply the MC factor.

MC: Meaning and Purpose

The MC factor provides the annualized payment amount per $1 of loan amount for a fully-amortized loan with monthly compounding and payments.

Mathematically, the MC factor is simply the monthly PR factor multiplied by 12. The MC factor is also known as the annualized mortgage constant or constant annual percent. The MC factors are in column 7 of the monthly pages of AH 505.

Calculating MC Factors

To locate the MC factor for a term of 30 years at an annual interest rate of 6%, go to page 32 of AH 505, go down 30 years and across to column 7. The MC factor is 0.0719461. MC factors are found in Column 7 of the monthly tables only.

Link to AH 505, page 32

You can confirm that the MC factor is the monthly periodic repayment factor multiplied by 12:
0.005996 × 12 = 0.071952 (small difference due to rounding)

This means that for every $1 of loan amount, the annual total of the 12 monthly payments will be $0.071952 (or $0.072). Or, stating it another way, the sum of the 12 monthly payments will be equal to 7.1952% (or 7.2%) of the loan amount.

We could have calculated the MC factor by first calculating the monthly PR factor and then multiplying it by 12 (note that both i and n must be expressed in months, not years) using the formula below:

• MC = PR × 12
• MC = 0.00599551 × 12
• MC = 0.0719461

Example 1:
A buyer takes out a mortgage loan for $250,000 at an annual rate of 8% with monthly payments for 30 years. What percentage of the original loan amount will she pay on an annualized basis?

Solution:

• The problem simply asks for the MC factor, which we can look up directly in AH 505.
• Go to AH 505, page 40, column 7, 30 years, to find the MC factor of 0.0880517. This is equivalent to 8.80517%

Link to AH 505, page 40

One can confirm the answer by calculating the monthly payment, multiplying it by 12, and dividing this product by the original loan amount (difference between factor and table and calculation due to rounding):

• PMT = PV × PR (8%, 30 years, annual)
• PMT = $250,000 × 0.007338
• PMT = $1,834.50

• MC = (PMT × 12) ÷ PV
• MC = ($1,834.50 × 12) ÷ $250,000
• MC = $22,014 ÷ $250,000
• MC = 0.088056, or 8.8056%

Example 2:
In the band of investment method for deriving an overall capitalization rate (RO), the rate is a weighted average of the equity dividend rate (RE) and the mortgage constant (MC), with the weightings based on the respective proportions of equity and debt. The current equity dividend rate is 10% and a loan can be obtained at an annual interest rate of 6% with monthly payments for 30 years at a loan-to-value ratio of 75%. Calculate an overall capitalization rate using the band of investment.

Solution:

• Find the MC factor in AH 505, page 32, column 7, for 30 years. The correct MC factor is 0.0719461.
• Use the band of investment method to estimate the overall rate (RO) using the calculation shown in the table below.
• The estimated overall capitalization rate (RO) is 7.90%.

Link to AH 505, page 32

Page 3

This lesson discusses the frequency of compounding and its affect on the present and future values using the compound interest functions presented in Assessors’ Handbook Section 505 (AH 505), Capitalization Formulas and Tables. The lesson:

• Explains compounding frequency and intra-year compounding,
• Demonstrated calculation of FW$1 and PW$1 factors given monthly compounding, and
• Concludes with generalizations with respect to frequency of compounding and future and present value.

Intra-Year Compounding

Up to this point, we generally have assumed that interest was calculated at the end of each year, based on the principal balance at the beginning of the year and the annual interest rate. That is, we have assumed that interest was compounded (or discounted) on an annual basis, and in solving problems we have used the annual compounding pages in AH 505.

Compounding interest more than once a year is called "intra-year compounding". Interest may be compounded on a semi-annual, quarterly, monthly, daily, or even continuous basis. When interest is compounded more than once a year, this affects both future and present-value calculations.

With intra-year compounding, the periodic interest rate, instead of being the stated annual rate, becomes the stated annual rate divided by the number of compounding periods per year. The number of periods, instead of being the number of years, becomes the number of compounding periods per year multiplied by the number of years.

As shown in the following table:

With monthly compounding, for example, the stated annual interest rate is divided by 12 to find the periodic (monthly) rate, and the number of years is multiplied by 12 to determine the number of (monthly) periods.

In lesson 2, we calculated the annual FW$1 factor at a stated annual rate of 6% for 4 years with annual compounding. The resulting factor was 1.262477.

Now let’s calculate the FW$1 for an annual rate of 6% for 4 years, but with monthly compounding. In this case, the periodic monthly rate is 0.5% (one-half of one percent per month, 6% ÷ 12), and the number of monthly compounding periods is 48 (12 periods/year × 4 years).

In order to calculate the FW$1 factor for 4 years at an annual interest rate of 6%, with monthly compounding, use the formula below:

• FW$1 = (1 + i)n
• FW$1 = (1 + 0.5%)48
• FW$1 = (1 + 0.005)48
• FW$1 = (1.005)48
• FW$1 = 1.270489

The FW$1 factor with monthly compounding, 1.270489, is slightly greater than the factor with annual compounding, 1.262477. If we had invested $100 at an annual rate of 6% with monthly compounding we would have ended up with $127.05 four years later; with annual compounding we would have ended up with $126.25.

AH 505 contains separate sets of compound interest factors for annual and monthly compounding. Factors for annual compounding are on the odd-numbered pages; factors for monthly compounding are on the even-numbered pages.The FW$1 factor for 4 years at an annual interest rate of 6%, with monthly compounding, is in AH 505, page 32 (monthly page).

Link to AH 505, page 32

In lesson 3, we calculated the PW$1 factor at an annual rate of 6% for 4 years with annual compounding. The resulting factor was 0.792094.

Let’s calculate the PW$1 factor for 4 years at an annual interest rate of 6%, with monthly compounding. In this case, the periodic monthly rate is 0.5% (one-half of one percent per month, 6% ÷ 12), and the number of monthly compounding periods is 48 (12 periods/year × 4 years).

In order to calculate the PW$1 factor for 4 years at an annual interest rate of 6%, with monthly compounding, use the formula below:

The PW$1 factor for 4 years at an annual interest rate of 6%, with monthly compounding, can be found in AH 505, page 32. The amount of the factor is 0.787098.

Link to AH 505, page 32

The following two generalizations can be made with respect to frequency of compounding and future and present values:

• When interest is compounded more than once a year, a future value will always be higher than it would have been with annual compounding, all else being equal.
• When interest is compounded more than once a year, a present value will always be lower than it would have been with annual compounding, all else being equal.

Thus, with our examples for the FW$1 and the PW$1:

• Given FW$1, at a rate of 6%, for a term of 4 years: 1.270489 (compounded monthly) > 1.262477 (compounded annually)
• Given PW$1, at a rate of 6%, for a term of 4 years: 0.787098 (compounded monthly < 0.792094 (compounded annually)

We would have obtained similar results with FW$1/P and PW$1/P, respectively.

Most appraisal problems involve annual payments and require the use of annual factors. Monthly factors are also useful because most mortgage loans are based on monthly payments, and it is often necessary to make mortgage calculations as part of an appraisal problem.

For other compounding periods, the factors for which are not included in AH 505, the appraiser can calculate the desired factor from the appropriate compound interest formula. As noted, AH 505 contains factors for annual and monthly compounding only.

Page 4

This lesson discusses annuities in the context of the compound interest functions presented in Assessors’ Handbook Section 505 (AH 505), Capitalization Formulas and Tables. The lesson:

• Defines an annuity and two types of annuity,
• Explains how to convert an ordinary annuity factor into the corresponding annuity due factor, and
• Contains examples of converting annuity factors.

Definition of an Annuity

An annuity is a series of equal cash flows, or payments, made at regular intervals (e.g., monthly or annually). The payments must be equal, and the interval between payments must be regular.

The following compound interest functions in AH 505 involve annuities:

• Future Worth of $1 Per Period (FW$1/P)
• Sinking Fund Factor (SFF)
• Present Worth of $1 Per Period (PW$1/P)
• Periodic Repayment (PR)

  The two remaining compound interest functions -- the future worth of $1 (FW$1) and the present worth of $1 (PW$1) -- are not annuities because they apply to single payments rather than to a series of payments.

There are two types of annuities:

• Ordinary Annuity
• Annuity Due

An ordinary annuity is an annuity in which the cash flows, or payments, occur at the end of the period.

An ordinary annuity of cash inflows of $100 per year for 5 years can be represented like this:

The cash flows occur at the end of years 1 through 5. And the first cash flow occurs at the end of year 1.

Most appraisal problems involve ordinary annuities; that is payments are assumed to occur at the end of the period. All of the formulas and factors in AH 505 pertain to ordinary annuities only.

An annuity due is an annuity in which the cash flows, or payments, occur at the beginning of the period. An annuity due is also called an annuity in arrears.

An annuity due of cash inflows of $100 per year for 5 years can be represented like this:

The cash flows occur at the beginning of years 1 through 5. And the first cash flow occurs at time 0 (now).

As noted, most appraisal problems assume that payments occur at the end of the period (ordinary annuity). But if payments occur at the beginning of the period (annuity due), an ordinary annuity factor in AH 505 can be converted to its corresponding annuity due factor with a relatively simple calculation.

Although financial calculators and spreadsheet software make it even easier to convert from an ordinary annuity to an annuity due, it is useful to understand how to "manually" convert the ordinary annuity factors in AH 505 to annuity due factors.

Conversion of ordinary annuity factor to annuity due factor for FW$1/P or PW$1/P:

To determine the Future Worth of $1 Per Period (FW$1/P) or Present Worth of $1 Per Period (PW$1/P) factor for an annuity due, refer to the corresponding factor in AH 505 for an ordinary annuity and multiply it by a factor of (1 + the periodic interest rate).

The periodic rate will differ depending on the compounding interval in the problem. For example, with annual compounding, the periodic rate would be the same as the annual rate; with monthly compounding the periodic rate would be the annual rate divided by 12.

Example 1: Conversion to annuity due factor for FW$1/P
Calculate the FW$1/P factor for 4 years at an annual interest rate of 6% with annual compounding, assuming payments occur at the beginning of each year.

Solution:
With annual compounding, the periodic rate equals the annual rate (6 percent, or 0.06).

• Annuity Due Factor = Ordinary Annuity Factor (in AH 505, page 33) × (1 + periodic rate)
• Annuity Due Factor = 4.374616 × (1 + 0.06)
• Annuity Due Factor = 4.637093

Example 2: Conversion to annuity due factor for PW$1/P
Calculate the PW$1/P factor for 4 years at an annual interest rate of 6% with monthly compounding, assuming payments occur at the beginning of each month.

Solution:
With monthly compounding, the periodic rate is 6% ÷ 12 = one-half of one percent per month, or 0.06 ÷ 12 = 0.005.

• Annuity Due Factor = Ordinary Annuity Factor (in AH 505, page 32) × (1 + periodic rate)
• Annuity Due Factor = 42.580318 × (1 + 0.005)
• Annuity Due Factor = 42.793220

Conversion of ordinary annuity factor to annuity due factor for SFF or PR:

To determine the Sinking Fund Factor (SFF) or Periodic Repayment (PR) Factor for an annuity due, refer to the corresponding factor in AH 505 for an ordinary annuity and divide it by a factor of (1+ the periodic interest rate). Be sure to divide, not multiply, when converting the SFF and PR factors. Note: the periodic rate will differ depending on the compounding interval in the problem.

Example 3: Conversion to annuity due for SFF
Calculate the SFF for 4 years at an annual interest rate of 6% with annual compounding, assuming payments occur at the beginning of each year.

Solution:

Example 4: Conversion to annuity due for PR
Calculate the PR factor for 4 years at an annual interest rate of 6% with monthly compounding, assuming payments occur at the beginning of each month (monthly annuity due factor).

Solution: