Finance project help! – For rajdeep

I am having a real difficult time figuring out the formulas to input for this project.  In fact, I have knocked my head up and down figuring out what formula goes where.  I need to get this done.  I attached a PDF version of the project.  HELP!

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 a time.

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

G
ra

d
e

d
P

ro
je

c
t

G
ra
d
e
d
P
ro
je
c
t

■ 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 run-
ning 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.

Graded Project130

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

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:

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.

Graded Project132

Once the monthly schedule is completed, generate an annual-
ized 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 PaymentNumber 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 “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 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:

Graded Project134

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 dol-
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 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 AnnualInterest 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.

Graded Project136

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 out-
flows. (20 points)

Graded Project 137

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

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

SUBMITTING YOUR FILES

Your documents should be created using a computer program
such as Excel, and using a standard font in 12-point size. To
submit your project, make sure you include the following:

■ Your completed amortization schedule

■ Depreciation schedule

■ Interest and depreciation schedule

■ Aftertax interest and depreciation schedule

■ Aftertax cash outflow schedule

You’ll submit your project online. To send the files via an
e-mail attachment,

1. Go to http://www.takeexamsonline.com and log on as
a student.

2. Go to My Courses.

3. Click on Take Exam next to the graded project number
06058901.

4. Enter your e-mail address in the box provided. (Note:
This information is required for online submission.)

5. Attach your file or files as follows:

a. Click on the Browse box.

b. Locate the file you wish to attach.

c. Double-click on the file.

d. Click on Upload File.

6. Click on Submit Files.

Graded Project138