You have just sold your house for $1,000,000 in cash. Yourmortgage was originally a 30-year mortgage with monthly paymentsand an initial balance of $800,000. The mortgage is currentlyexactly 18½ years old, and you have just made a payment. If theinterest rate on the mortgage is 5.25% (APR), how much cash willyou have from the sale once you pay off the mortgage?
Complete the steps below using cell references to given data orprevious calculations. In some cases, a simple cell reference isall you need. To copy/paste a formula across a row or down acolumn, an absolute cell reference or a mixed cell reference may bepreferred. If a specific Excel function is to be used, thedirections will specify the use of that function. Do not type innumerical data into a cell or function. Instead, make a referenceto the cell in which the data is found. Make your computations onlyin the blue cells highlighted below. In all cases, unless otherwisedirected, use the earliest appearance of the data in your formulas,usually the Given Data section.
| | |
| Sale price | $Â Â Â Â Â 1,000,000 |
| Initial balance | $Â Â Â Â Â Â Â Â 800,000 |
| Number of years | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30 |
| Periods per year | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 12 |
| Periods into the loan | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 222 |
| APR | 5.25% |
| | |
| Discount rate | |
| Monthly payment | |
| Periods remaining | |
| PV of mortgage | |
| Cash from sale | |
| | | | | |
| Requirements | | | |
| 1 | Incell D13, by using cell references, calculate thediscount rate (1 pt.). |
| 2 | In cell D14, by using cell references, calculatethe monthly payment (1 pt.).Note: (1) The output of theexpression or function you typed in this cell is expected as apositive number. (2) For the nperparameter, use cells D8 andD9. |
| 3 | In cell D15, by using cell references, calculatethe number of periods remaining on the loan (1pt.). |
| 4 | In cell D16, by using cell references, calculatethe amount that you owe on the mortgage (1 pt.).Note: The output of the expression or function youtyped in this cell is expected as a positive number. |
| 5 | In cell D17, by using cell references, calculatethe amount that you will have from the sale of your home afterpaying off the mortgage (1 pt.). |
