In this project, youll 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 youve 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, youll 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

Payment Number

Payment Amount

6% Interest Expense Principal Balance

Current

Non-Current 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 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

Once youve 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; youll 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 Debit Credit Mortgage Payable $298.65 Interest Expense $1,500.00 Cash $1,798.65 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.

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

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 Ann

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, youll 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 308309 of your textbook. Create annual measures and a source document for annual financial statement preparation. Your textbook didnt provide a depreciation schedule for the 39-year, nonresidential real property, so weve 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

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

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

Step 3: Create a Schedule Combining

Interest Expenses and Depreciation

Expenses

In this step, youll 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, youll 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

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, youre 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, youll need to calculate the present values and net present values of the aftertax cash flows orexpenses for the project. In this case, this is the present value, aftertax cash outflow.

Youve 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. Youll 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