In this first step of your project, you’ll need to create a loan
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
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 you’ve determined how each of the amounts in the table
are obtained, you can calculate them and fill them in for all
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:
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: 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:
Create a Schedule Combining
Interest Expenses and DepreciationExpenses
3In 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:
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.
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.
5..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,
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.