In the townhome investment model, do a sensitivity analysis by using a two-input data table. The two
Fantastic news! We've Found the answer you've been seeking!
Question:
In the townhome investment model, do a sensitivity analysis by using a two-input data table. The two input variables are Rent and Resell Value: Rent ($16,000, $18,000,..., $30,000) Resell value ($250,000, $280,000, ..., $550,000) Show IRR as a function of these two variables. Use conditional formatting to highlight the unprofitable combinations.
Transcribed Image Text:
Yuting is considering buying a unit of townhome in Clearwater and renting it out for the income. The townhome will cost $220,000 and she is planning to buy it with all cash. Here are some additional facts: Yuting figures she can rent out the townhome for $24000 a year, and the rent will grow by approximately 3% per year. She'll have to pay property taxes of $3000 and property insurance of $2000 at the end of the year, and she assumes the property tax will grow at 3% per year as well, since real estate generally appreciates. And she is figuring on additional miscellaneous expenses of $1000 per year. All the income from the townhouse has to be reported on their annual tax return. Currently, Yuting has a tax rate of 24%, and she thinks her rate will continue for the foreseeable future. Her accountant has explained to her that she can depreciate rental property based on MACRS 27.5 year straight-line depreciation system. At the end of year 10 she plans to resell the townhome for $500,000. Since this is not her preminary residence, she will pay 15% capital gain on the income. -Calculate the cash flow for this investment each year. If she requires 12% annual return, what are the NPV and IRR for this investment? -Conduct a sensitivity analysis (a two-input data table) to show IRR as a function of resell value and rent. Highlight the combinations that are not profitable. Yuting is considering buying a unit of townhome in Clearwater and renting it out for the income. The townhome will cost $220,000 and she is planning to buy it with all cash. Here are some additional facts: Yuting figures she can rent out the townhome for $24000 a year, and the rent will grow by approximately 3% per year. She'll have to pay property taxes of $3000 and property insurance of $2000 at the end of the year, and she assumes the property tax will grow at 3% per year as well, since real estate generally appreciates. And she is figuring on additional miscellaneous expenses of $1000 per year. All the income from the townhouse has to be reported on their annual tax return. Currently, Yuting has a tax rate of 24%, and she thinks her rate will continue for the foreseeable future. Her accountant has explained to her that she can depreciate rental property based on MACRS 27.5 year straight-line depreciation system. At the end of year 10 she plans to resell the townhome for $500,000. Since this is not her preminary residence, she will pay 15% capital gain on the income. -Calculate the cash flow for this investment each year. If she requires 12% annual return, what are the NPV and IRR for this investment? -Conduct a sensitivity analysis (a two-input data table) to show IRR as a function of resell value and rent. Highlight the combinations that are not profitable.
Expert Answer:
Answer rating: 100% (QA)
Setup Excel model as follows EXCEL FORMULA To create TwoWay Data ... View the full answer
Related Book For
Data Analysis and Decision Making
ISBN: 978-0538476126
4th edition
Authors: Christian Albright, Wayne Winston, Christopher Zappe
Posted Date:
Students also viewed these mathematics questions
-
Using a data table in Excel, perform a sensitivity analysis on the example in Simple Decision Problem.xlsx. Specifically, keep the probabilities in row 10 (for D2) as they are, but vary the...
-
A regression model with K = 16 independent variables is fit using a panel of sevenyears of data. The sums of squares for the seven separate regressions and the pooled regression are shown below. The...
-
Do the following analysis using the hypothetical data in Table 13.7. In this imaginary experiment, participants were randomly assigned to receive either no caffeine (1) or 150 mg of caffeine (2); and...
-
In December 2022, more than a year after Ontarios vaccine management system was breached, the Ontario government is notifying around 360,000 people that their data was stolen and potentially given to...
-
In the absence of air resistance, if a ball is thrown vertically upward with a certain initial speed, on returning to its original level it will have the same speed. When air resistance is a factor,...
-
The minimum application value shall be within the range of A. 5,000 to 10,000 B. 2,000 to 5,000 C. 10,000 to 15,000 D. 10,000 to 20,000
-
Water flows past a flat plate with an upstream velocity of \(U=0.02 \mathrm{~m} / \mathrm{s}\). Determine the water velocity a distance of \(10 \mathrm{~mm}\) from the plate at distances of \(x=1.5...
-
Juniper Corp. makes three models of insulated thermos. Juniper has $400,000 in total revenue and total variable costs of $240,000. Its sales mix is given below: Percentage of Total Sales Thermos A...
-
What is the study of abnormal psychology? What are your overall thoughts on the study of abnormal psychology? 2. Explain 1-2 concepts, terms, or statistics that resonated. 3. How can you use the...
-
The following information pertains to the financial statements of Buffalo Supply Company, a provider of plumbing fixtures to contractors in central Pennsylvania. Required: Reconstruct all journal...
-
Choose a service environment for a company you are familiar with and list the key physical attributes of the service environment, using the three subdimensions, and discuss their impact on customer...
-
Jayson is the manufacturing supervisor at a guacamole factory. They are working on cost classifications to better identify areas where they may need to curb costs. They are reviewing the property tax...
-
Transaction Processing and Financial Reporting Systems Overview Imagine you're working as an auditor, and during your examination of a company's financial reporting system, you come across...
-
Knowing how to classify accounts is a key piece of accounting knowledge. Reading a financial statement in todays business world is not limited to accounting personnel. Anyone involved with making...
-
Give an example of a direct relationship between two variables. Why is this type of relationship sometimes referred to as a positive relationship? Next, Give an example of an inverse relationship...
-
Choose a country other than the United States and research the following: What are the key diversity concerns for that country? Which groups are dominant and non-dominant? What are the participation...
-
Cells highlighted in green contain a drop-down list; select the appropriate account from the list. When the calculations for Cost of goods manufactured & Cost of goods sold are correct, the cells...
-
Perform the operation by first converting the numerator and denominator to scientific notation. Write the answer in scientific notation. 7,200,00/0.000009
-
A company faces the following demands during the next three weeks: week 1, 2000 units; week 2, 1000 units; week 3, 1500 units. The unit production costs during each week are as follows: week 1, $130;...
-
The file P09_42.xlsx contains many years of monthly percentage changes in the Dow Jones Industrial Average (DJIA). a. Create a histogram of the percentage changes in the DJIA. Is there any visual...
-
Explain in general why EVSI is the same, regardless of the actual cost of the information. For example, in the Acme problem EVSI is the same regardless of whether the actual cost of the test market...
-
Consider each table below. Is the data presented consistent with the Phillips curve model of wage determination? Each table has a point \(A\) and a point B. Startyour answer with true/false/...
-
In a 2012 book, The New Geography of Jobs (a book described by Barack Obama as 'a timely and smart discussion of how different cities and regions have made a changing economy work for them', and by...
-
Nature, a leading science magazine, argues (https://www.nature.com/articles/d41586-020-01518-y) that COVID19 will force universities to confront long-standing challenges in higher education, such as...
Study smarter with the SolutionInn App