In this project, you’ll create a loan amortization schedule for example mortgage loan. Imagine the mortgage is for anonresidential 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 several steps for you to follow. Each step includes figures that illustrate the concepts.Step 1: Create a Loan Amortization ScheduleIn this first step of your project, you’ll need to create a loanamortization schedule. The following table illustrates the paymentsand 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 paymentsPayment NumberPayment Amount6% Interest ExpensePrincipal BalanceCurrentNon-CurrentAnnual Interest Expense0 $300,000.00 $3,684.02 $296,315.98 $01 $1,798.65 $1,500.00 $298.65 $299,701.35 $3,702.44 $295,998.912 $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 $0360 $1,800.23 $8.96 $1,791.27 $0 $0 $0 $685.50Totals $347,515.58 $300,000.00The 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 theOnce you’ve determined how each of the amounts in the tableare obtained, you can calculate them and fill them in for all 360 payments.Note that the table shows only the figures for the first twopayments and the last two payments; you’ll need to calculatethe amounts for the remaining payments and fill them in.Once this loan amortization schedule is completely filled in, itcan be printed out and used to prepare other financial statements.For example, when the first payment of $1,798.65 ismade, the following accounting journal entry would be madeDebit CreditMortgage Payable $298.65Interest Expense $1,500.00Cash $1,798.65The 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 annualizedversion, using the following preferred format:Step 2: Create a Depreciation ScheduleThe 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 133Year PaymentNumber Balance Current Non-CurrentAnnualInterestExpense0 $300,000.00 $3,684.02 $296,315.98 $01 12 $296,315.98 $3,911.24 $292,404.75 $17,899.782 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.1329 348 $20,899.88 $20,899.88 $0 $1,899.5830 360 $0 $0 $0 $685.50Total $347,515.58The 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. 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 133Year PaymentNumber Balance Current Non-CurrentYou paida slightly higher appraisal fee than usual, and instructedthe appraiser to provide you with the following breakdownof values:Graded Project 133Year PaymentNumber Balance Current Non-CurrentAnnualInterestExpense0 $300,000.00 $3,684.02 $296,315.98 $01 12 $296,315.98 $3,911.24 $292,404.75 $17,899.782 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.1329 348 $20,899.88 $20,899.88 $0 $1,899.5830 360 $0 $0 $0 $685.50Total $347,515.58AppraisedValues PercentageLand $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 shownin the following illustration:Now, you’ll need to use the MACRS tables to determine theamount of depreciation expense. Assume that the “improvements”represent 39-year, nonresidential rental property andthe “fixtures” represent 7-year property. Create a depreciationschedule using the MACRS tables on pages 308–309 of yourtextbook. Create annual measures and a source documentfor annual financial statement preparation. Your textbookdidn’t provide a depreciation schedule for the 39-year, nonresidentialreal property, so we’ve provided one below. Themeasures in the table represent the percentage by which theimprovements to the real property may be depreciated, peryear, based on the month placed in service, which in thiscase was January:The amounts in this table are carried out to the third decimalplace, so some rounding errors will prevent the improvementsfrom being fully depreciated through year 39. You shouldprepare the depreciation schedule only through year 30, tomatch the loan amortization schedule you prepared in Step 1of the project. To check your work, you can use the followingfigure, which shows part of the completed depreciation schedule:134 Graded ProjectAppraisedValues Percentage CostAllocationLand $45,000 14.29% $42,857Improvements $260,000 82.54% $247,619Fixtures $10,000 3.17% $9,524Total $315,000 100.00% $300,000Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec1 2.461 2.247 2.033 1.819 1.695 1.391 1.177 0.963 0.749 0.535 0.321 0.1072thru392.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564 2.564Year Land Improvements Fixtures Total1 $0 $6,094 $1,361 $7,4552 $0 $6,349 $2,332 $8,681————————————-Break in Sequence————————————-29 $0 $6,349 $0 $6,34930 $0 $6,349 $0 $6,349Total $0 $190,213 $9,524 $199,737Step 3: Create a Schedule CombiningInterest Expenses and DepreciationExpensesIn this step, you’ll need to create a schedule that combinesinterest expenses and depreciation expenses, but only for thefirst 10 years of the life of the asset. Here is how the completedschedule should appear:Step 4: Convert the Interest Expenseand Depreciation ExpenseIn this step of your project, you’ll need to convert the interestexpense and depreciation expense from pretax to aftertax dollars.Assume the firm is subject to a 34 percent marginal taxrate, and convert the 10-year schedule of interest expenseand depreciation expense to aftertax terms. Review Lesson 3,Assignment 9, to obtain the applicable formulas.Graded Project 135Year Land Improvements Fixtures Total1 $0 $6,094 $1,361 $7,4552 $0 $6,349 $2,332 $8,681————————————-Break in Sequence————————————-29 $0 $6,349 $0 $6,34930 $0 $6,349 $0 $6,349Total $0 $190,213 $9,524 $199,737Year AnnualInterest ExpenseAnnualDepreciationExpense1 $17,899.78 $7,455—————Break in Sequence—————10 $15,270.50 $6,349Remember from your lessons that operating and interestexpense results in a cash outflow, and depreciation expenseresults 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 scheduleshould appear, with the combined annual interest expenseand depreciation expense, both converted to aftertax terms.Step 5: Calculate the Aftertax CashOutflowsIn this step of your project, you’ll need to calculate the presentvalues and net present values of the aftertax cash flows orexpenses for the project. In this case, this is the present value,aftertax cash outflow.You’ve calculated the aftertax cash flows for the interestexpense and the depreciation expense associated with thepurchase of this piece of non-residential real property. Now,the final step requires you to calculate the present value ofthese ATCFs for each year, and the NPV for these expenses,in aggregate.Using a discount rate of 10 percent, extend the table completedin 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-5of your textbook (near the back of the book). The followingillustration shows how the completed table should appear.136 Graded ProjectYearPretaxAnnualInterestExpensePretaxAnnualDepreciationExpense(a)AT CForPosttax(1 – T)InterestExpense(b)AT CForPosttax(T)DepreciationExpense(a) – (b)AT CForPosttaxCombinedInterest &DepreciationExpense1 $17,900 $7,455 $11,814 $2,535 $9,279————————————-Break in Sequence————————————-10 $15,271 $6,349 $10,079 $2,159 $7,920YearPretaxAnnualInterestExpensePretaxAnnualDepreciationExpense(a)AT CForPosttax(1 – T)InterestExpense(b)AT CForPosttax(T)DepreciationExpense(a) – (b)AT CForPosttaxCombinedInterest &DepreciationExpense10%PVFactorPVATCFs1 $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,053Total $166,896 $72,757NPV $53,068requirements:Step 1: Create the loan amortization schedule for theproperty. (20 points)Step 2: Create the depreciation schedule. (20 points)Step 3: Create the schedule that combines interestexpenses and depreciation expenses. (20 points)Step 4: Create a schedule that converts the interestexpense and depreciation expense to aftertaxdollars. (20 points)Step 5: Create a schedule that shows the aftertax cash outflows.(20 points)