Using Using Spreadsheets in Spreadsheets in Eng EconEng Econ(Separating Principle and (Separating Principle and Interest)Interest)©2005 Dr. Bradley C. Paul©2005 Dr. Bradley C. Paul

The tax deduction and The tax deduction and equity problemequity problem

The handy magic numbers are designed The handy magic numbers are designed to sweep cash in standard positions into to sweep cash in standard positions into the pot.the pot.

We can always get questions they really We can always get questions they really weren’t designed to answerweren’t designed to answer This case - how much is interest and how This case - how much is interest and how

much is building equitymuch is building equity

Enter another answer - the spreadsheetEnter another answer - the spreadsheet

One of Our Prepared One of Our Prepared Resources is a Mortgage Resources is a Mortgage CalculatorCalculator

Fixed Rate Mortgage

Annual % Rate 5.75 %

Years to Repay 30 yearsAmount Borrowed 180000 $Down Payment 20000 $Closing Fees 3000 $Point Paid Upfront 0Points "Rolled into loan" 2Property Value Growth Rate 2.1 %Effective Tax Rate forHome Mortgage Tax Deduction 15 %

Yellow input fields call forInformation on the proposedloan

(the web site has a version of thisLecture that shows you how toBuild your own spreadsheet)

Input the Data on the Local Input the Data on the Local Bank Loan from Union Bank Loan from Union ShaftersShafters

Fixed Rate Mortgage

Annual % Rate 8 %

Years to Repay 15 yearsAmount Borrowed 25200 $Down Payment 2800 $Closing Fees 735 $Point Paid Upfront 0Points "Rolled into loan" 0Property Value Growth Rate 5 %Effective Tax Rate forHome Mortgage Tax Deduction 15 %

Outputs Summary Data for Outputs Summary Data for Loan (assumed he kept it Loan (assumed he kept it for 15 years)for 15 years)

Monthly P+I Payment 240.8243

Fees Paid 735 $Interest Paid 18148.38 $Tax Savings from Interest 2722.257Starting Value of Home 28000 $Ending Value of Home 58209.99 $Total Money Paid for Home 46883.38After Tax Money Paid for Home 43426.12% increase in home cost 67.44064 %

We’re not looking at longTerm home ownership –We just want to find theLeast expensive way forHerby and Hanna to haveA roof over their heads whileHerby goes to school

(Classic All Cost AlternativesProblem)

Here Below is Info We Here Below is Info We WantedWanted

Payment Schedule Principle Remaining Tax TaxMonth Debt interest Payment Retired Debt Deduction Savings

1 25200 168 240.8243 72.82433 25127.182 25127.17567 167.5145 240.8243 73.30982 25053.873 25053.86585 167.0258 240.8243 73.79855 24980.074 24980.0673 166.5338 240.8243 74.29054 24905.785 24905.77676 166.0385 240.8243 74.78581 24830.996 24830.99094 165.5399 240.8243 75.28439 24755.717 24755.70656 165.038 240.8243 75.78628 24679.928 24679.92028 164.5328 240.8243 76.29152 24603.639 24603.62875 164.0242 240.8243 76.80013 24526.83

10 24526.82862 163.5122 240.8243 77.31213 24449.5211 24449.51649 162.9968 240.8243 77.82755 24371.6912 24371.68894 162.4779 240.8243 78.3464 24293.34 1983.234 297.485213 24293.34254 161.9556 240.8243 78.86871 24214.47

Note that at first most of your paymentsGo to interest

By keeping trackOf the interestWe could calculateHow much weCould deduct onSchedule A andWhat it would saveUs bottom line onOur taxes

Tax AssumptionTax Assumption Lets assume Herby can deduct his interest, Lets assume Herby can deduct his interest,

but that he is only in the 15% tax bracketbut that he is only in the 15% tax bracket The money Herby saves on his taxes may The money Herby saves on his taxes may

be a positive flow into his pocket as a be a positive flow into his pocket as a refund checkrefund check Year #1 $1983 * 0.15 = $297.48Year #1 $1983 * 0.15 = $297.48 Year #2 $1907.9 * 0.15 = $286.19Year #2 $1907.9 * 0.15 = $286.19 Year #3 $1826.33 *0.15 = $273.95Year #3 $1826.33 *0.15 = $273.95 Year #4 $1738.01 * 0.15 = $260.70Year #4 $1738.01 * 0.15 = $260.70 Year #5 $1642.35 * 0.15 = $246.35Year #5 $1642.35 * 0.15 = $246.35

Cash Flow for Buying Cash Flow for Buying with Local Loanwith Local Loan

0 1 - 12 13 - 24 25 - 36 37 - 48 49 - 60 61 - 69 73

$3,535

$240.88 per month

$6,358.68

$297.48 $286.19 $273.95 $260.70 $246.35 $174.63

Can do the Same Thing Can do the Same Thing for the Internet Loanfor the Internet Loan

Fixed Rate Mortgage

Annual % Rate 6.25 %

Years to Repay 30 yearsAmount Borrowed 25200 $Down Payment 2800 $Closing Fees 1260 $Point Paid Upfront 0Points "Rolled into loan" 1.5Property Value Growth Rate 5 %Effective Tax Rate forHome Mortgage Tax Deduction 15 %

Monthly P+I Payment 157.4881

Cash Flow for Internet Cash Flow for Internet LoanLoan

0 1 - 12 13 - 24 25 - 36 37 - 48 49 - 60 61 - 69 73

$4,060

$157.49 per month

$238.52 $235.64 $232.55 $229.27 $225.79 $166.92

$1630.95