Question

Real Estate Investment & Analysis – Fixed & Variable Rate Mortgages

The purpose of this project is to get you familiar with the technical side of different mortgage types. You will be using Excel to create amortization schedules, determine the remaining mortgage balance, and calculate the equity of the home owner at different points of time.

General Instructions for parts 1-4:
For each part you are required to create an “input box” and an “output box” area. The user should be able to change ANY of the values included in the input box and receive the corresponding values included in the output box. The amortization table schedule should also change with different values entered into the input box. You are advised to use the Excel template I provide you for this project.
The input and output boxes should include the following variables: original house value, initial mortgage amount, interest rate, additional monthly payment to principal (a constant amount that the borrower elects to pay over and above the required payment each and every month), holding period (in months – until the property is sold), expected home price annual appreciation rate, mortgage payment, initial loan-to-value, mortgage balance at end of the holding period and equity at end of holding period.

Part 1: Fully amortized fixed rate mortgage (CPM)
1. Create a monthly amortization schedule for a fully amortized $300K, 15yr, 3.50% fixed rate mortgage. The original loan balance is 75% of the value of the house when initiated and the house is expected to appreciate at a rate of 2.0% annually.
2. What is the remaining loan balance at the end of the holding period if the homeowner sells the home after 58 months?
3. Illustrate with a well-labeled graph the amount of equity the homeowner builds throughout the holding period of the loan.
4. What is the owner equity in the house at the time the house is sold?

Part 2: Partially amortized fixed rate mortgage (CPM)
1. Create a monthly amortization schedule for a partially amortized $250K, 15yr, 4.00% fixed rate mortgage. The mortgage is 40% amortized over the life of the loan. The original loan balance is 80% of the value of the house when initiated and the house is expected to appreciate at a rate of 2.0% annually.
2. What is the remaining loan balance at the end of the holding period if the homeowner sells the home after 73 months?
3. Illustrate with a well-labeled graph the amount of equity the homeowner builds throughout the holding period of the loan.
4. What is the owner equity in the house at the time the house is sold?

Part 3: Negative amortization fixed rate mortgage (CPM)
1. Create a monthly amortization schedule for a negatively amortized $280K, 5yr, 4.50% fixed rate mortgage. The monthly payments on the mortgage are $700. The original loan balance is 98% of the value of the house when initiated and the house is expected to depreciate at a rate of 1.75% annually.
2. What is the remaining loan balance at the end of the holding period if the homeowner sells the home after 59 months?
3. Illustrate with a well-labeled graph the amount of equity the homeowner builds throughout the holding period of the loan.
4. What is the owner equity in the house at the time the house is sold?

Part 4: Fully amortized Variable rate mortgage
1. Assume that you know in advance (at the time of the mortgage initiation) the future prime rate at any point of time. According to you, the prime rate will be the following:
At mortgage initiation: 3.50%
6 months after mortgage initiation: 3.75%
10 months after mortgage initiation: 4.00%
21 months after mortgage initiation: 4.25%
32 months after mortgage initiation: 4.50%
52 months after mortgage initiation: 5.00%
56 months after mortgage initiation: 5.50%
From here on, the prime rate remains the same for the reminder time of the loan.
2. Create a monthly amortization schedule for a fully amortized $320K, 15yr, 2/1 hybrid ARM mortgage that charges a rate of prime minus 1.0%. The original loan balance is 70% of the value of the house when initiated and the house is expected to appreciate at a rate of 2.5% annually.
3. What is the remaining loan balance at the end of the holding period if the homeowner sells the home after 58 months?
4. Illustrate with a well-labeled graph the amount of equity the homeowner builds throughout the holding period of the loan.
5. What is the owner equity in the house at the time the house is sold?

Solution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Real Estate Financing

This is only a preview of the solution. Please use the purchase button to see the entire solution

$68.00

or $1 if you
register a new account!

Related Homework Solutions

Investment Analysis and Portfolio Management
Homework Solution
$55.00
Business
Finance
Investment
Analysis
Portfolio
Management
Stock
Share
Strike Price
Closing Price
LEAPS
Margin
Interest
Walmart
Pay-off
Risk
Debt Obligations
CDS
Treasury Bonds
IBM
Binomial Valuation
Black-Scholes
Spot-Futures Parity
Finance Questions: Risk, Inflation, Interest, Bonds
Homework Solution
$16.00
Accounting
Business
Finance
Financial Mathematics
Inflation
Interest
Bonds
Face Value
YTM
Maturity Risk Premium
Default Risk Premium
Treasury Securities
Percentage
Market Price
Kulpa Fishing Supplies: A Company That Is Seeking To increase Its Value
Homework Solution
$80.00
Accounting
Business
Financial Management
Sales
Costs
Assets
Values
Consultancy
Economy
Administration
Marketable Securities
Shares
Stock Market
Industry
Cash Flow
Investors
EROIC
WACC
MVA
Managerial Behavior
Pension Plans - Article Review (350 words)
Homework Solution
$23.00
Business
Finance
Pension
Retirement
Income
Savings
Defined Benefit
Defined Contribution
Plan
Stock Market
Crash
Liability
Cost
Risk
Finance Questions
Homework Solution
$25.00
Business
Finance
Direct
Indirect
Exchange
Rate
Interest
Value
Dollar
Euro
Yen
Pound
Zloty
Business Questions
Homework Solution
$33.00
Business
Accounting
Administration
Economy
Finance
Deposit
Balance
Savings Account
Financial Tables
APR
Time Value of Money
Principal
Payments
Tuition
Mortgage
Get help from a qualified tutor
Live Chats