Question

Step 1: Create a Loan Amortization Schedule In this first stepof your project, you’ll need to create a loan amortizationschedule. The following table illustrates the payments and interestamounts for a fixed-rate, 30-year mortgage loan. The total amountof the mortgage is $300,000, and the interest rate is 6 percent.This mortgage requires monthly payments of $1,798.65, with a finalpayment of $1,800.23. The table was created in Excel. The followingis an explanation of the columns in the table:

■The first column in the table, with the heading “PaymentNumber,” shows the 360 payments required to pay off the mortgageloan (30 years, with 12 monthly payments per year).

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
Total		$347,515.58	$300,000.00

■The second column, with the heading “Payment Amount,” showsthe monthly payment amount.

■The third and fourth columns show the portion of the monthlypayment paid for interest, and the portion paid towards theprincipal.

■The fifth column, headed “Balance,” shows the starting balanceof $300,000, and the remaining balance each month after theprincipal is subtracted.

■The sixth column, headed “Current,” reflects the currentportion of the principal (12 months).

■The amounts in the “Non-Current” column are calculated bysubtracting the current portion of the principal from the totalbalance.

■The “Annual Interest Expense” column provides a running totalof the interest expense on the mortgage for the entire 12-monthperiod.

■The “Totals” under the “6% Interest Expense” and “Principal”columns show the final totals for the 30-year life of themortgage.

Once you’ve determined how each of the amounts in the table areobtained, you can calculate them and fill them in for all 360payments. Note that the table shows only the figures for the firsttwo payments and the last two payments; you’ll need to calculatethe amounts for the remaining payments and fill them in. Once thisloan amortization schedule is completely filled in, it can beprinted out and used to prepare other financial statements. Forexample, when the first payment of $1,798.65 is made, the followingaccounting journal entry would be made:

Debit Credit

Mortgage Payable $298.65

Interest Expense $1,500.00

Cash $1,798.65

Notice that the amounts of principal and interest in thisjournal entry would change for each and every payment. Whenoriginated, the journal for the loan was created as shown here:

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 thisdate, the current portion of the mortgage would be $3,702.44, andthe noncurrent portion of the mortgage would be $295,998.91. If youwere to create a chart of the interest and principal components ofeach mortgage payment, over the life of the mortgage, it would looklike the following illustration:

Mortgage Principal and Interest

Interest Components of Interest

Principal Component of Payments

360 Monthly Payments

Once you’ve completed the amortization schedule for this loan,you’ll be able to create loan amortization schedules for your ownhome mortgage, automobile loan, personal loans, and so on. You caneven create a pro forma report that shows the effects of additionalprincipal payments on the life of your loan (this assumes you don’thave a prepayment penalty, which is typically the case). You may besurprised at the effects a modest additional principal payment hason the life of a loan.

Once the monthly schedule is completed, generate an annualizedversion, using the following preferred format:

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

Step 2: Create a Depreciation Schedule

The next step in your project is to create a depreciationschedule for the (fictional) property purchased with this loan.When the property was purchased, an appraisal was performed. Theproperty included separate components of land and improvements (thebuilding), and also included some fixtures (appliances, such as arefrigerator). You paid a slightly higher appraisal fee than usual,and instructed the appraiser to provide you with the followingbreakdown of values:

	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 betweenthese different asset classes, so you can use the appropriatedepreciable life to prepare a depreciation schedule, as shown inthe following illustration:

	Appraised Values	Percentage	Cost Allocation
Land	$45,000	14.29%	$42,857
Improvement	$260,000	82.54%	$247,619
Fixtures	$10,000	3.17%	$9,524
Total	$315,000	100.00%	$300,000

Now, you’ll need to use the MACRS tables to determine the amountof depreciation expense. Assume that the “improvements” represent39-year, nonresidential rental property and the “fixtures”represent 7-year property. Create a depreciation schedule using theMACRS tables on pages 308–309 of your textbook. Create annualmeasures and a source document for annual financial statementpreparation. Your textbook didn’t provide a depreciation schedulefor the 39-year, nonresidential real property, so we’ve providedone below. The measures in the table represent the percentage bywhich the improvements to the real property may be depreciated, peryear, based on the month placed in service, which in this case wasJanuary:

Year	Jan	Feb	March	April	May	June	July	Aug	Sept	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 decimalplace, so some rounding errors will prevent the improvements frombeing fully depreciated through year 39. You should prepare thedepreciation schedule only through year 30, to match the loanamortization schedule you prepared in Step 1 of the project. Tocheck your work, you can use the following figure, which shows partof 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 andDepreciation Expenses

In this step, you’ll need to create a schedule that combinesinterest expenses and depreciation expenses, but only for the first10 years of the life of the asset. Here is how the completedschedule should appear:

Year	Annual Interest Expense	Annual Depreciation Expense
1	$17,899.78	$7,455

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

10

$15,270.50

$6,349

Step 4: Convert the Interest Expense and DepreciationExpense

In this step of your project, you’ll need to convert theinterest expense and depreciation expense from pretax to aftertaxdollars. Assume the firm is subject to a 34 percent marginal taxrate, and convert the 10-year schedule of interest expense anddepreciation expense to aftertax terms. Review Lesson 3, Assignment9, to obtain the applicable formulas.

Remember from your lessons that operating and interest expenseresults in a cash outflow, and depreciation expense results in acash inflow, from the depreciation tax shield. Therefore, in thisstep, you’re computing a net cash outflow. The followingillustration shows how the completed schedule should appear, withthe combined annual interest expense and depreciation expense, bothconverted to aftertax terms.

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 & DepreciationExpense
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

Step 5: Calculate the Aftertax Cash Outflows In this step ofyour project, you’ll need to calculate the present values and netpresent values of the aftertax cash flows or expenses for theproject.

In this case, this is the present value, aftertax cash outflow.You’ve calculated the aftertax cash flows for the interest expenseand the depreciation expenseassociated with the purchase of this piece of non-residential realproperty. Now, the final step requires you to calculate the presentvalue of these ATCFs for each year, and the NPV for these expenses,in aggregate. Using a discount rate of 10 percent, extend the tablecompleted in Step 4 by adding a column for the present value ofATCFs. You’ll find a “present value of $1” table on pages A-4 andA-5 of your textbook (near the back of the book). The followingillustration shows how the completed table shouldappear.

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 & DepreciationExpense	10% PV Factor	PV ATCFs
1	$17,900	$7,455	$11,814	$2,535	$9,279	0.9091	$8,436

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

10	$15,271	$6,349	$10,079	$2,159	$7,920	0.3855	$3,053
Total	$166,896	$72,757
NPV							$53,068

Evaluation Criteria Your instructor will use the followingcriteria to evaluate your project: Step 1: Create the loanamortization schedule for the property. (20 points) Step 2: Createthe depreciation schedule. (20 points) Step 3: Create the schedulethat combines interest expenses and depreciation expenses. (20points) Step 4: Create a schedule that converts the interestexpense and depreciation expense to aftertax dollars. (20 points)Step 5: Create a schedule that shows the aftertax cash outflows.(20 points)