PENNFOSTER EXAMINATION NUMBER 06058801 (Lesson 5)

INSTRUCTIONS

In this project, you’ll create a loan amortization schedule for

an example mortgage loan. Imagine the mortgage is for a

nonresidential real property your company has purchased.

The property includes land and a building. Once you’ve

created the amortization schedule, you can use it to prepare

other financial documents. Your project is divided into several

steps for you to follow. Each step includes figures that

illustrate the concepts.

Step 1: Create a Loan Amortization

Schedule

In this first step of your project, you’ll need to create a loan

amortization schedule. The following table illustrates the payments

and interest amounts for a fixed-rate, 30-year mortgage

loan. The total amount of the mortgage is $300,000, and the

interest rate is 6 percent. This mortgage requires monthly

payments of $1,798.65, with a final payment of $1,800.23.

The table was created in Excel.

The following is an explanation of the columns in the table:

? The first column in the table, with the heading “Payment

Number,” shows the 360 payments required to pay off

the mortgage loan (30 years, with 12 monthly payments

per year).

129

Graded Project Graded Project

? The second column, with the heading “Payment Amount,”

shows the monthly payment amount.

? The third and fourth columns show the portion of the

monthly payment paid for interest, and the portion paid

towards the principal.

? The fifth column, headed “Balance,” shows the starting

balance of $300,000, and the remaining balance each

month after the principal is subtracted.

? The sixth column, headed “Current,” reflects the current

portion of the principal (12 months).

? The amounts in the “Non-Current” column are calculated

by subtracting the current portion of the principal from

the total balance.

? The “Annual Interest Expense” column provides a running

total of the interest expense on the mortgage for the

entire 12-month period.

? The “Totals” under the “6% Interest Expense” and “Principal”

columns show the final totals for the 30-year life of the

mortgage.

130 Graded Project

Payment

Number

Payment

Amount

6%

Interest

Expense

Principal Balance Current NonCurrent

Annual

Interest

Expense

0 $300,000.00 $3,684.02 $296,315.98 $0

1 $1,798.65 $1,500.00 $298.65 $299,701.35 $3,702.44 $295,998.91

2 $1,798.65 $1,498.51 $300.14 $299,401.21 $3,720.95 $295,680.26

————————————-Break in Sequence————————————-

359 $1,798.65 $17.86 $1,780.79 $1,791.28 $1,791.27 $0

360 $1,800.23 $8.96 $1,791.27 $0 $0 $0 $685.50

Totals $347,515.58 $300,000.00

Once you’ve determined how each of the amounts in the table

are obtained, you can calculate them and fill them in for all

360 payments.

Note that the table shows only the figures for the first two

payments and the last two payments; you’ll need to calculate

the amounts for the remaining payments and fill them in.

Once this loan amortization schedule is completely filled in, it

can be printed out and used to prepare other financial statements.

For example, when the first payment of $1,798.65 is

made, the following accounting journal entry would be made:

Notice that the amounts of principal and interest in this

journal entry would change for each and every payment.

When originated, the journal for the loan was created as

shown here:

Graded Project 131

Debit Credit

Mortgage Payable $298.65

Interest Expense $1,500.00

Cash $1,798.65

Debit Credit

Fixed Asset–Real Property $300,000

Mortgage Payable $300,000

The balance of this mortgage, after the first payment, is

$299,701.35. If a classified balance sheet were prepared

on this date, the current portion of the mortgage would

be $3,702.44, and the noncurrent portion of the mortgage

would be $295,998.91.

If you were to create a chart of the interest and principal

components of each mortgage payment, over the life of the

mortgage, it would look like the following illustration:

Once you’ve completed the amortization schedule for this loan,

you’ll be able to create loan amortization schedules for your

own home mortgage, automobile loan, personal loans, and

so on. You can even create a pro forma report that shows

the effects of additional principal payments on the life of your

loan (this assumes you don’t have a prepayment penalty,

which is typically the case). You may be surprised at the

effects a modest additional principal payment has on the

life of a loan.

132 Graded Project

Once the monthly schedule is completed, generate an annualized

version, using the following preferred format:

Step 2: Create a Depreciation

Schedule

The next step in your project is to create a depreciation

schedule for the (fictional) property purchased with this

loan. When the property was purchased, an appraisal was

performed. The property included separate components of

land and improvements (the building), and also included

some fixtures (appliances, such as a refrigerator). You paid

a slightly higher appraisal fee than usual, and instructed

the appraiser to provide you with the following breakdown

of values:

Graded Project 133

Year Payment

Number Balance Current Non-Current

Annual

Interest

Expense

0 $300,000.00 $3,684.02 $296,315.98 $0

1 12 $296,315.98 $3,911.24 $292,404.75 $17,899.78

2 24 $292,404.75 $4,152.47 $288,252.27 $17,672.56

————————————-Break in Sequence————————————-

28 336 $40,584.10 $19,684.22 $20,899.88 $3,043.13

29 348 $20,899.88 $20,899.88 $0 $1,899.58

30 360 $0 $0 $0 $685.50

Total $347,515.58

Appraised

Values Percentage

Land $45,000 14.29%

Improvements $260,000 82.54%

Fixtures $10,000 3.17%

Total $315,000 100.00%

Your mortgage loan cost of $300,000 must be allocated between

these different asset classes, so you can use the appropriate

depreciable life to prepare a depreciation schedule, as shown

in the following illustration:

Now, you’ll need to use the MACRS tables to determine the

amount of depreciation expense. Assume that the “improvements”

represent 39-year, nonresidential rental property and

the “fixtures” represent 7-year property. Create a depreciation

schedule using the MACRS tables on pages 308–309 of your

textbook. Create annual measures and a source document

for annual financial statement preparation. Your textbook

didn’t provide a depreciation schedule for the 39-year, nonresidential

real property, so we’ve provided one below. The

measures in the table represent the percentage by which the

improvements to the real property may be depreciated, per

year, based on the month placed in service, which in this

case was January:

The amounts in this table are carried out to the third decimal

place, so some rounding errors will prevent the improvements

from being fully depreciated through year 39. You should

prepare the depreciation schedule only through year 30, to

match the loan amortization schedule you prepared in Step 1

of the project. To check your work, you can use the following

figure, which shows part of the completed depreciation schedule:

134 Graded Project

Appraised

Values Percentage Cost

Allocation

Land $45,000 14.29% $42,857

Improvements $260,000 82.54% $247,619

Fixtures $10,000 3.17% $9,524

Total $315,000 100.00% $300,000

Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

1 2.461 2.247 2.033 1.819 1.695 1.391 1.177 0.963 0.749 0.535 0.321 0.107

2

thru

39

2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564

Step 3: Create a Schedule Combining

Interest Expenses and Depreciation

Expenses

In this step, you’ll need to create a schedule that combines

interest expenses and depreciation expenses, but only for the

first 10 years of the life of the asset. Here is how the completed

schedule should appear:

Step 4: Convert the Interest Expense

and Depreciation Expense

In this step of your project, you’ll need to convert the interest

expense and depreciation expense from pretax to aftertax dollars.

Assume the firm is subject to a 34 percent marginal tax

rate, and convert the 10-year schedule of interest expense

and depreciation expense to aftertax terms. Review Lesson 3,

Assignment 9, to obtain the applicable formulas.

Graded Project 135

Year Land Improvements Fixtures Total

1 $0 $6,094 $1,361 $7,455

2 $0 $6,349 $2,332 $8,681

————————————-Break in Sequence————————————-

29 $0 $6,349 $0 $6,349

30 $0 $6,349 $0 $6,349

Total $0 $190,213 $9,524 $199,737

Year Annual

Interest Expense

Annual

Depreciation

Expense

1 $17,899.78 $7,455

—————Break in Sequence—————

10 $15,270.50 $6,349

Remember from your lessons that operating and interest

expense results in a cash outflow, and depreciation expense

results in a cash inflow, from the depreciation tax shield.

Therefore, in this step, you’re computing a net cash outflow.

The following illustration shows how the completed schedule

should appear, with the combined annual interest expense

and depreciation expense, both converted to aftertax terms.

Step 5: Calculate the Aftertax Cash

Outflows

In this step of your project, you’ll need to calculate the present

values and net present values of the aftertax cash flows or

expenses for the project. In this case, this is the present value,

aftertax cash outflow.

You’ve calculated the aftertax cash flows for the interest

expense and the depreciation expense associated with the

purchase of this piece of non-residential real property. Now,

the final step requires you to calculate the present value of

these ATCFs for each year, and the NPV for these expenses,

in aggregate.

Using a discount rate of 10 percent, extend the table completed

in Step 4 by adding a column for the present value of ATCFs.

You’ll find a “present value of $1” table on pages A-4 and A-5

of your textbook (near the back of the book). The following

illustration shows how the completed table should appear.

136 Graded Project

Year

Pretax

Annual

Interest

Expense

Pretax

Annual

Depreciation

Expense

(a)

AT CF

or

Posttax

(1 – T)

Interest

Expense

(b)

AT CF

or

Posttax

(T)

Depreciation

Expense

(a) – (b)

AT CF

or

Posttax

Combined

Interest &

Depreciation

Expense

1 $17,900 $7,455 $11,814 $2,535 $9,279

————————————-Break in Sequence————————————-

10 $15,271 $6,349 $10,079 $2,159 $7,920

Evaluation Criteria

Your instructor will use the following criteria to evaluate your

project:

Step 1: Create the loan amortization schedule for the

property. (20 points)

Step 2: Create the depreciation schedule. (20 points)

Step 3: Create the schedule that combines interest

expenses and depreciation expenses. (20 points)

Step 4: Create a schedule that converts the interest

expense and depreciation expense to aftertax

dollars. (20 points)

Step 5: Create a schedule that shows the aftertax cash outflows.

(20 points)