Hey again . please see attachment. Thank you!!! let me know if you need anything else

A B 1 SALLY & DAVE'S CONDO--Template 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 Condo purchase price Annual rent Property tax, annual Other expenses, annual Depreciation Tax rate 100,000.00 24,000.00 1,500.00 1,000.00 4,000.00 30% Mortgage Principal Interest Term Annual payment 50,000.00 8% 10 $7,451.47 40 41 42 43 44 C D #VALUE! #VALUE! Calculation of income for tax purposes Year Rent Miscellaneous expenses Property taxes Other expenses Depreciation Mortgage interest Reportable income Taxes Net income 0 1 2 Cash flow to Sally & Dave Net income Add back depreciation Take out mortgage principal repayment Equity cash flow Terminal value Total equity cash flow IRR--compound return to equity #VALUE! Terminal value Estimated resale value, year 10 Book value Taxable gain Taxes Net after tax #VALUE! #VALUE! #VALUE! E F G H I J 3 4 5 6 7 8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 K L 9 10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 A 1 2 3 4 5 6 7 8 9 19 20 21 22 23 24 C D E THE EXCEL FUNCTIONS IPMT AND PPMT Loan principal Interest rate Loan term (years) Annual payment 10,000 12% 5 $2,774.10 #VALUE! Loan table 10 11 12 13 14 15 16 17 18 B Year 1 2 3 4 5 Payment split: Payment Principal at at end of beginning of year year 10,000.00 2,774.10 8,425.90 2,774.10 6,662.91 2,774.10 4,688.37 2,774.10 2,476.87 2,774.10 Using the IPMT and PPMT functions Year 1 2 3 4 5 =IPMT($B$4,A20,$B$5,-$B$3) Interest 1,200.00 1,011.11 799.55 562.60 297.22 Repayment of principal 1,574.10 1,762.99 1,974.55 2,211.49 2,476.87 Payment split: Repayment Interest of principal 1,200.00 1,574.10 1,011.11 1,762.99 799.55 1,974.55 562.60 2,211.49 297.22 2,476.87 F 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 #VALUE! #VALUE! 19 20 21 22 23 24 #VALUE! #VALUE! #VALUE! CHAPTER 4 MINI-CASE: SALLY AND DAVE'S CONDOFINANCING WITH A MORTGAGE* This version: April 2011 Overview This mini-case takes us back to b-school grads Sally and Dave. You'll perhaps recall from PFE Chapter 4 that they're thinking of buying a condo which will cost $100,000. In Chapter 4, Sally and Dave were planning to finance the condo purchase without borrowing. In this case we consider the case where they take out a mortgage to finance the investment. The point of this case is to get you to think about the effect of financing on returns. It should also lead to a discussion of the relation between financing and risk. Case facts Here are the facts: Sally and Dave intend to take a 10-year mortgage for $50,000. The mortgage has interest rate of 8%, compounded annually. Repayment of the mortgage is in equal annual payments of interest and principal. Sally and Dave can rent out the condo for $2,000 per month. They'll have to pay property taxes of $1,500 annually and they're figuring on additional miscellaneous expenses of $1,000 per year. All the income from the condo has to be reported on their annual tax return. Currently Sally & Dave have a tax rate of 30%, and they think this rate will continue for the foreseeable future. The full cost of the condo can be depreciated over 25 years on a straight-line basis. To calculate the return from owning the condo, Sally and Dave assume that they will sell the condo at the end of 10 years for $100,000. Any gain over book value on the sale is, of course, taxable. Assignment 1. Use the template for this case to calculate Sally and Dave's IRR on their equity investment. (Terminology: Since the cost of the condo is $100,000 and since they're borrowing $50,000, the equity investment is $50,000.) Remember that for income tax purposes depreciation and interest on the mortgage are expenses, but that repayment of mortgage principal is not an expense. Use Excel's IPMT and PPMT functions (see explanation below). 2. Show (in a data table) the effect on the equity IRR when the mortgage goes from $0, $10,000, $20,000, ... , $90,000 . Explain your results. * nd This is a case to accompany Chapter 4 of Principles of Finance with Excel, 2 Edition by Simon Benninga (Oxford University Press, 2011). All rights are reserved, and you may not use this case without permission from the author simon@simonbenninga.com . PFE Chapter 4 real estate mini-case page 1 3. Show (in a data table) the effect on the equity IRR when the tax rate varies from 0% to 40% (in steps of 5%). 4. Suppose that Sally and Dave take a $50,000 mortgage with a 25-year term. They still plan to sell the apartment at the end of year 10. At this date they will repay the remaining mortgage principal with a 2% penalty for early repayment. Calculate the equity IRR. Excel note A mortgage is a loan which usually involves flat annual repayments of principal and interest.1 We discussed such loans in Chapter 2, where we showed how to build a loan table which describes the annual breakdown of the payment into interest and principal. Excel has two functions, IPMT and PPMT, which do this breakdown without the necessity of a loan table. You will find these functions handy in this case. Because interest is deductible for tax purposes and repayment of loan principal is not, this case requires you to distinguish between the two. That's where , IPMT and PPMT come in. Here's an example: Suppose you borrow $10,000 for 5 years at 12%. The flat annual payment of principal + interest on this loan is $2,774.10 (see cell B6 below). The loan table shows how this payment is split each year between interest and repayment of principal. A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 B C D E F THE EXCEL FUNCTIONS IPMT AND PPMT Loan principal Interest rate Loan term (years) Annual payment 10,000 12% 5 $2,774.10