Assignment
OVERVIEW
This project integrates quite a few components of your course. The most important thing to keep in mind, as you progress through this project, is to take one step at atime.
Do not rush through this project. After completing each step, pause, take a break, and give some thought to the task you've just completed. If necessary, refer back to the relevant lesson, assignments, and textbook chapters each step refers to. This will reinforce the learning process.
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 sev- eral 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 pay- ments 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 thetable:
- 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 peryear).
|
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 paymentamount.
- The third and fourth columns show the portion of the monthly payment paid for interest, and the portion paid towards theprincipal.
- The fifth column, headed "Balance," shows the starting balance of $300,000, and the remaining balance each month after the principal issubtracted.
- The sixth column, headed "Current," reflects the current portion of the principal (12months).
- The amounts in the "Non-Current" column are calculated by subtracting the current portion of the principal from the totalbalance.
- The "Annual Interest Expense" column provides a run- ningtotal 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.
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 state- ments. 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 Cash
|
$1,500.00
|
$1,798.65
|
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:
|
|
Debit
|
Credit
|
|
Fixed Asset-Real Property Mortgage Payable
|
$300,000
|
$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 aloan.
Once the monthly schedule is completed, generate an annual- ized version, 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 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 ofvalues:
|
|
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:
|
|
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
|
Now, you'll need to use the MACRS tables to determine the amount of depreciation expense. Assume that the "improve- ments" 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, non- residential 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 wasJanuary:
|
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 thru39
|
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, 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:
|
Year
|
Annual Interest Expense
|
Annual DepreciationExpense
|
|
1
|
$17,899.78
|
$7,455
|
|
-----Break in Sequence-----
|
|
10
|
$15,270.50
|
$6,349
|
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 aftertaxdol- lars. 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 applicableformulas.
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 taxshield.
Therefore, in this step, you're computing a net cashoutflow.
The following illustration shows how the completed schedule should appear, with the combined annual interest expense and depreciation expense, both converted to aftertax terms.
|
Year
|
Pretax Annual Interest Expense
|
Pretax Annual DepreciationExpense
|
(a) ATCF or Posttax (1 - T) Interest Expense
|
(b) AT CF or Posttax (T) DepreciationExpense
|
(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 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.
|
Year
|
Pretax Annual Interest Expense
|
Pretax Annual DepreciationExpense
|
(a) ATCF or Posttax (1 - T) Interest Expense
|
(b) AT CF or Posttax (T) DepreciationExpense
|
(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 following criteria to evaluate your project:
Step1: Create the loan amortization schedule forthe property.
Step2: Create the depreciation schedule.
Step3: Create the schedule thatcombinesinterest expenses and depreciation expenses.
Step4: Create a schedule that converts theinterestexpense and depreciation expense to aftertaxdollars.
Step5: Create a schedule that shows the aftertaxcashout- flows.