1. Use excel to complete a 5 year spreadsheet for buying versus renting. Data: home cost $500,000, loan info 80% L/V, 30 years, 4% monthly, appreciation rate in price AND rent 5%, tax rate 40%, 1st year rent $2,500/month, property taxes plus insurance and maintenance 2%, sale transaction costs 3%, holding period 5 years. Make your columns for time zero and each of the 5 years, and your bottom line the ATCFlows for each year. For year 5 it will be the sum of the after tax operating cash flow and the ATCFsale. Calculate the IRRat for buying.
2. Graduated payment problem. a. Use excel to calculate the initial payment of the following FHA GPM loan: $300,000, 30 years, 3%, monthly, 4 increases or steps (years 2, 3, 4, and 5-30) of 10% each. When I solve this problem I actually write the TVM formulas using parentheses and the exponential key ^. Use whatever method you like but remember it must work on excel with NO calculator assistance. It must solve for both loans. b. now change the loan to $430,000, 6%, 30 years, monthly, same step increases. Solve for new payment.
3. ARM problem. Local lender Nice and SLO Mortgage offers a 30 year, $250,000 ARM under the following terms: Teaser rate (for one year) 2% Index 1 year COFI Annual reset Margin 2.5% Interest rate cap 2% annually, 5% lifetime..note that the adjusted rate can never be above 7% No payment caps No negative amortization 2 points charged Build a 5 year spreadsheet for this loan. The following forward COFI rates apply: BOY2 4%, BOY3 5%, BOY4 5.5%, BOY5 4.5%. You need one or more if statements to get excel to choose between the capped and uncapped lifetime interest rates. Solve for the yield (APR) to the lender.