Docstoc

mortgage amortization 30yr fixed

Document Sample
mortgage amortization 30yr fixed Powered By Docstoc
					Only modify cells that are bold black.
                                                         Adam Schwartz, adam@schwartz-home.com
         Loan Details
        Property Value    $1,200,000
         Percent Down         30%                                         Proprerty Tax         1.15%
           Interest rate    6.750%                            Homeowner's Insurance             $2,400
                  Points    0.800%                                  Maintenance Costs           $3,600
           term (years)        30                        Loan Closing Cost excl points         $11,760
        Down Payment       $360,000                                         Selling Cost         5.0%
          Loan amount      $840,000                         Home appreciation per yr             3.0%
          Cost of points    $6,720                              Investment rate per yr           8.0%
     Total closing cost    $378,480                               Marginal Tax Bracket          34.3%
    Number of months          360                         Capital gains rate (fed+state)        29.3%
     Monthly payment      $5,448.22     after 10 years             Years to keep home             10
  Total Interest+Points  $1,128,081     $537,035.27          Monthly Rent + Rent Ins.          $3,500
     Total cost of loan $1,979,840.64   $672,266.88         Rent Increase rate per yr             3%

                                                   Amortization Table
                                                                                           Effective monthly
     Year 1               Interest        Principal              Tax Savings                   payment
    Year Total           $63,146.40        $8,952.28              $18,553.97                   $5,717.61
     Year 2
  Cum. Year Total       $118,949.48        $18,527.90             $16,035.21                  $5,367.51
     Year 3
  Cum. Year Total       $174,085.83        $28,770.24             $15,806.52                  $5,386.56
     Year 4
  Cum. Year Total       $228,509.02        $39,725.74             $15,561.91                  $5,406.95
     Year 5
  Cum. Year Total       $282,169.40        $51,444.04             $15,300.27                  $5,428.75
     Year 6
  Cum. Year Total       $335,013.86        $63,978.27             $15,020.41                  $5,452.07
     Year 7
  Cum. Year Total       $386,985.59        $77,385.22             $14,721.06                  $5,477.02
     Year 8
  Cum. Year Total       $438,023.82        $91,725.68             $14,400.87                  $5,503.70
     Year 9
  Cum. Year Total       $488,063.56      $107,064.63              $14,058.38                  $5,532.24
     Year 10
  Cum. Year Total       $537,035.27      $123,471.61              $13,692.05                  $5,562.77
     Year 11
  Cum. Year Total       $584,864.60      $141,020.97              $13,300.22                  $5,595.42
     Year 12
  Cum. Year Total       $631,472.01      $159,792.25              $12,881.10                  $5,630.35
     Year 13
  Cum. Year Total       $676,772.41      $179,870.54              $12,432.79                  $5,667.71
     Year 14
  Cum. Year Total       $720,674.80      $201,346.83              $11,953.28                  $5,707.67
     Year 15
  Cum. Year Total       $763,081.84      $224,318.48              $11,440.37                  $5,750.41
     Year 16
  Cum. Year Total       $803,889.41      $248,889.60              $10,891.75                  $5,796.13
   Year 17
Cum. Year Total   $842,986.15     $275,171.55   $10,304.94   $5,845.03
   Year 18
Cum. Year Total   $880,252.92     $303,283.46   $9,677.26    $5,897.34
   Year 19
Cum. Year Total   $915,562.33     $333,352.75   $9,005.88    $5,953.28
   Year 20
Cum. Year Total   $948,778.07     $365,515.70   $8,287.75    $6,013.13
   Year 21
Cum. Year Total   $979,754.37     $399,918.08   $7,519.63    $6,077.14
   Year 22
Cum. Year Total   $1,008,335.30   $436,715.84   $6,698.02    $6,145.61
   Year 23
Cum. Year Total   $1,034,354.08   $476,075.74   $5,819.20    $6,218.84
   Year 24
Cum. Year Total   $1,057,632.32   $518,176.20   $4,879.19    $6,297.17
   Year 25
Cum. Year Total   $1,077,979.18   $563,208.02   $3,873.73    $6,380.96
   Year 26
Cum. Year Total   $1,095,190.57   $611,375.32   $2,798.26    $6,470.59
   Year 27
Cum. Year Total   $1,109,048.18   $662,896.40   $1,647.91    $6,566.45
   Year 28
Cum. Year Total   $1,119,318.47   $718,004.80    $417.47     $6,668.99
   Year 29
Cum. Year Total   $1,125,751.68   $776,950.27     $0.00      $6,703.77
   Year 30
Cum. Year Total   $1,128,080.64   $840,000.00     $0.00      $6,703.77
                               Investment Value
                               Interest Payments    $   537,035
                              Principle Payments    $   123,472
                                     Yearly Costs   $    60,000
                                     Property Tax   $   138,000
                                  Down Payment      $   360,000
                                 Opportunity Cost   $   351,778
                                     Tax Savings    $   203,538    Invest rent
                                                                      TRUE
                                             Rent   $   481,483      savings
                           Value of home at end     $ 1,612,700
                       Remaining loan amnt due      $   716,528
                                    Selling Costs   $    80,635      Exclude
                                                                      TRUE        You can exclude up to $250,000 (or $50
                                                                  capital gain
                     Cost of buying vs. renting     $   117,398
                                   Cost per year    $    11,740 Negative number means you're better
                                                                off buying a home.

Eff. non-principal                Affordability                            AMT calculation
 monthly pymts
     $4,971.59                     Gross Income     $     220,000          AMT income        $134,973.60
                                     Exemptions            3             AMT exemption        $66,250.00
   $4,569.54               Effective Fed tax rate       18.1%                     AMT         $17,868.13
                         Effective State tax rate        6.0%              AMT liability           $0.00
   $4,533.04                           Medicare     $        3,190
                                  Social Security   $      11,750                              FALSE
                                                                                           Turn off AMT
   $4,493.99                           state SDI    $        1,271
                                          401(k)    $      28,600
   $4,452.23            Federal Taxable Income      $     104,236
                          State Taxable Income      $     134,974
   $4,407.55                           Total Tax    $      57,080     36.6%
                         Net Disposable Income      $     134,320     FALSE
   $4,359.77          Amnt leftover each month      $        5,245

   $4,308.66                                            FALSE

   $4,254.00         Federal Standard Deduction $         10,000
                       Federal Exemption amount $          8,774
   $4,195.52            State Standard Deduction $         6,508

   $4,132.98
                              You can deduct interest payments for home
   $4,066.08                  purchase loans up to $1M. Any amount after that
                              is not deductible. This spreadsheet does not
   $3,994.52
                              check for that possibility!
   $3,917.98

   $3,836.11

   $3,748.53
$3,654.87

$3,554.68

$3,447.51

$3,332.88

$3,210.27

$3,079.13

$2,938.85

$2,788.80

$2,628.31

$2,456.64

$2,273.02

$2,076.62

$1,791.65

$1,449.63
exclude up to $250,000 (or $500,000 if married) from capital gain on home sale if you've lived there for two years.




             I'm assuming congress will extend the 2007 exemption level.
           Taxable Income             Amount Due
                                                   Above the
          Above     Below      This   Plus this % excess over
Single        0       7825       0       10%            0
            7825     31850      783      15%          7825
           31850     77100     4386      25%         31850
           77100    160850    15699      28%         77100
          160850    349700    39149      33%        160850
          349700             101469      35%        349700
Married       0      15650       0       10%            0
           15650     63700     1565      15%         15650
           63700    154200     8773      25%         63700
          154200    321700    31398      28%        154200
          321700    699400    78298      33%        321700
          699400             202939      35%        699400

                                   Taxable income $ 104,236
                                        Total Tax $   18,907
                                Marginal Tax Rate   25.0%
                                Effective Tax rate  18.1%
           Taxable Income          Amount Due
                                                 Above the
          Above     Below   This    Plus this % excess over
Single       0       6827      0        1%            0
           6827     16186     68        2%          6827
          16186     25546    255        4%         16186
          25546     35463    630        6%         25546
          35463     44818   1225        8%         35463
          44818             1973       9.3%        44818
Married      0      13654      0        1%            0
          13654     32372    137        2%         13654
          32372     51092    511        4%         32372
          51092     70926   1260        6%         51092
          70926     89636   2450        8%         70926
          89636             3947       9.3%        89636

                                 Taxable income $ 134,974
                                      Total Tax $    8,163
                              Marginal Tax Rate   9.3%
                              Effective Tax rate  6.0%
              Year      1          2       3       4       5      6       7
       Appreciation   7.0%       7.0%   -7.0%   -10.0%   7.0%   7.0%    7.0%
 Total Appreciation   7.0%      14.5%    6.5%    -4.2%   2.5%   9.7%   17.4%
 Avg. Yearly Apprc.   3.7%




Monthly payment vs. duration of loan
     Loan Amount $1,000,000
  Number of Years     30
     Interest Rate 6.500%
 Monthly Payment $6,320.68
   8       9      10
 7.0%    7.0%    7.0%
25.6%   34.4%   43.8%
 Date     Change
7/26/04   Add "include rent savings" toggle button.


7/27/04   Added rent increase.
10/7/04   Corrected standard deduction
10/7/04   Opportunity cost redefined to be amount of profit lost from
          investment of initial costs.
10/7/04   Changed federal tax bracket from being a user input to be
          determined from the federal tax schedule.
 4/1/06   Updated tax schedules.
          Added a cell for loan points.
          Fixed tax table lookup for married filing status.
          Decide to calculate tax savings based on marginal, rather
          than average, tax rate.
          Added tax on the sale of the home.
 4/8/06   In cells B14 and C14 removed downpayment
          Reduced estimate in tax savings columns

          Removed closing cost other than points from tax savings

6/16/07   Added button to select btw rent or own when computing net
          disposable income leftover each month.
6/23/07   Fixed up some formulas relating to points on the loan.
 7/1/07   Prevented divide by zero in calculating marginal tax rates.

7/15/07  Forgot to include closing points in first year's effective
         monthly payments.
10/13/07 Added a column for effective monthly, non-principal
         payments.
12/11/07 Add back cost of points to taxable income (L87, L88) since
         this deduction only applies to first year.
 3/24/08 Tax savings correction (E31).

3/24/08   Added a crude estimation of the effect of AMT.
3/28/08   Updated the tax tables to 2007.
5/17/08   Cell B12 turns red if mortgage payment exceeds 1/3rd of
          income.
5/24/08   Added cell B11: total closing cost.
Details
When selected, the difference between rent and effective mortgage payments is
invested each year at the investment rate. The returns are reinvested and taxed each
year.
Rent increases a fixed percent per year.
The tax savings from interest equals MAX( standard deduction, mortgage interest )
Initial fixed costs are added explicitly to cost of buying a home.

This may underestimate the marginal tax rate because the federal tax table lookup
uses taxable income - interest.




Note that both approaches are just approximations.


Downpayment isn't part of the cost of a loan.
I assume that if you own a home, AMT will prevent you from deducting state income
tax from your federal taxes.

Most closing costs are not tax-deductible.




This is essentially the "rent" you pay to own your home.


I was subtracting out both state tax and the standard deduction to compute the tax
savings. Now I just subtract the maximum of the two.
I reduce the tax savings by the AMT amount (O45).




Assumes 1.4% of loan amount for administrative costs, loan origination, appraisal, etc..

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:5/18/2012
language:English
pages:11
fanzhongqing fanzhongqing http://
About