Ann would like to buy a house.
It costs $800,000.
Her down payment will be $40,000.
She will take out a mortgage for $760,000.
It will be a 30 year, fully amortizing, FRM, with constant monthly payments and monthly compounding.
The annual interest rate is 4.00%.
She must pay 2.5% in fees at the time of the loan.
Note: the home is bought and the loan is taken in month 0, the first payment is due in month 1.
In the spreadsheet where it says âcash inflowâ, âoutflowâ and ânet cash flowâ you should only take into account cash flow related to the mortgage.
1. Fill in the spreadsheet for Ann. (It is called an amortization schedule.)
2. Compute Annâs annualized IRR for the mortgage in the spreadsheet. (Use the net cash flow.)
(2.a) What is the annualized IRR for the mortgage?
(2.b) Is it higher or lower than the mortgage contract rate?
(2.c) Why?
3. Plot Annâs mortgage balance in one graph. Place the figure here.
4. Plot Annâs monthly mortgage payment, interest payment and principal payment in one graph. Place the figure here.
She forecasts four possible scenarios for house price appreciation (HPA).
Optimistic Case: 4.5% annual HPA, hence 4.5/12% monthly HPA
Base Case: 2.5% annual HPA, hence 2.5/12% monthly HPA
Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA
Very Bad Case: -6% annual HPA, hence -6/12% monthly HPA
5. Plot Annâs home equity every month under each of the four HPA scenarios in one graph. Place the figure here.
6. Assume Ann will make the required monthly payment every month for 30 years.
(6.a) How much home equity will Ann have after 10 years (120 months) of payments under each of the four scenarios?
(6.b) After 30 years?
Scenario :
HPA
Home Equity in 10 years
Home Equity in 30 years
Optimistic
4.50%
Base
2.50%
Pessimistic
0.00%
Very Bad
-6.00%
Ann would like to buy a house.
It costs $800,000.
Her down payment will be $40,000.
She will take out a mortgage for $760,000.
It will be a 30 year, fully amortizing, FRM, with constant monthly payments and monthly compounding.
The annual interest rate is 4.00%.
She must pay 2.5% in fees at the time of the loan.
Note: the home is bought and the loan is taken in month 0, the first payment is due in month 1.
In the spreadsheet where it says âcash inflowâ, âoutflowâ and ânet cash flowâ you should only take into account cash flow related to the mortgage.
1. Fill in the spreadsheet for Ann. (It is called an amortization schedule.)
2. Compute Annâs annualized IRR for the mortgage in the spreadsheet. (Use the net cash flow.)
(2.a) What is the annualized IRR for the mortgage?
(2.b) Is it higher or lower than the mortgage contract rate?
(2.c) Why?
3. Plot Annâs mortgage balance in one graph. Place the figure here.
4. Plot Annâs monthly mortgage payment, interest payment and principal payment in one graph. Place the figure here.
She forecasts four possible scenarios for house price appreciation (HPA).
Optimistic Case: 4.5% annual HPA, hence 4.5/12% monthly HPA
Base Case: 2.5% annual HPA, hence 2.5/12% monthly HPA
Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA
Very Bad Case: -6% annual HPA, hence -6/12% monthly HPA
5. Plot Annâs home equity every month under each of the four HPA scenarios in one graph. Place the figure here.
6. Assume Ann will make the required monthly payment every month for 30 years.
(6.a) How much home equity will Ann have after 10 years (120 months) of payments under each of the four scenarios?
(6.b) After 30 years?
| Scenario : | HPA | Home Equity in 10 years | Home Equity in 30 years |
| Optimistic | 4.50% | ||
| Base | 2.50% | ||
| Pessimistic | 0.00% | ||
| Very Bad | -6.00% |
Related questions
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)
