70.2K

Verified Solution

Link Copied!

Produce an application that meets the following requirements:

• Accepts end-user inputs for a required set of values
• Calculates the expected income/expenses totals, monthly cash flow and annual Return on Investment for a real estate property.
• Form to automate data entry and includes protected cells and formulas. Should not accept bad data such as a number in the property name. User should get an error with an example of how to enter the data.
• Produces output based on the resulting calculations. This output should be clearly formatted for printing (printer or PDF document).
• User has an option to save the output to a table (on a separate worksheet) for later reference and comparison. HINT: The macro must output data to the next available row, it should NOT overwrite any data.
• Macros and buttons as necessary.
• Workbook should open to the documentation worksheet.
• Documentation worksheet must contain name, purpose, date and instructions.
• Clear user input buttons on the documentation worksheet that take the user to the data input form (form should NOT be on the documentation worksheet)

Minimal worksheets:

1. Documentation
2. Data input form
3. Data table sheet [Worksheet must be tested and therefore data table should have a minimum of 3 saved properties].
4. Extra credit: Output reports (on a separate worksheet) that provides clear comparison of various investment. For instance a table that shows varies the purchase amount and interest rate.

Required INPUTs:

• Property Name and address
• FINANCING: Property Purchase Price, Down payment amount, Interest Rate, Term of Loan.
• INVESTMENT COSTS: Closing Costs, Furnishings/Appliances, Improvements.
• INCOME: Rental Income, Vacancy Allowance
• EXPENSES: Property Taxes, Home Insurance, Repair Costs, Water, Gas/Electric
• RETURN ON INVESTMENT: Anticipated Resale Value, Years Property Held

Required calculated OUTPUTs:

• FINANCING: Mortgage (Amount Borrowed), Mortgage Payment
• INVESTMENT COSTS: Total Amount Invested.
• INCOME: Monthly Rental Income (after Vacancy Allowance)
• EXPENSES: Annual Mortgage Payments, Annual Expenses, Total Annual Expenses
• CASH FLOW: Average Property Cash flow (Monthly Income minus Monthly Expenses)
• RETURN ON INVESTMENT: Total Income Realized during Years Held, Annual ROI (Resale Value + Total Income Realized minus Total Amount Invested divided by Number of years Held).

Answer & Explanation Solved by verified expert

See Answer