Question
In: Finance
I. Suppose an apartment you want to purchase costs $500,000. You can put down 20% in...
I. Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash and take out a 30-year fixed rate mortgage loan for the remaining. You believe that you can get an APR of 6.5% on such a mortgage loan at a local bank.
a. Suppose the loan calls for equal monthly payments. Please set up a monthly amortization schedule table for the loan (Input setting: 20%, creating and copying formulas and Excel finance functions: 40%).
b. What is the sum of total payments made over the life of the loan? What is the sum of total interest paid over the life of the loan? Calculate the difference of the two numbers. (Summation function 10%, please fill color in your answer cells)
c. What is the balance of the loan after ten years? (build-in Finance function 10%)
d. You are considering if taking an adjustable rate mortgage (ARM), in which your interest rate on the loan will change with a price index. In a different worksheet, please show a graph of your monthly payment as a function of APRs, varying the APR from 4% to 8% with a unit of 0.5 percentage point change (that is, show your monthly payment corresponding to APR at 4%, 4.5%, 5%, 5.5%....7.5%, 8% and graph the relationship between PMTs and APRs). (Graphing and working with multiple worksheets: 20%)
Solutions
Expert Solution
Answer :
total loan amount = $ 500000 * (1-20%) = $ 400000
APR = 6.5 % with tenure of 30 years
| Loan Amortization Table | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Loan Principle Amount | $400,000 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Due to word limit , not able to paste all the installment detail of excel file | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Answer b: Total Payement Of loan |
910177.95 |
Total Interest Payment 510177.95 |
Difference between Total payement and Interest Pament =400000 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Answer of C: Balance after 10 years i.e after 120 installments
As per excel calculated Amonunt = $ 339104.51.
Answer D : Installement at different rates
| 4% | 4.50% | 5% | 5.50% | 6% | 6.50% | 7% | 7.50% | 8% | |
| Monthly Installments | 1909.66 | 2026.75 | 2147.29 | 2271.16 | 2398.2 | 2528.272 | 2661.21 | 2796.86 | 2935.06 |
jeff jeffy answered 2 years agoRelated Solutions
Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash...
Suppose an apartment you want to purchase costs
$500,000. You can put down 20% in cash and take out a 30-year fixed
rate mortgage loan for the remaining. You believe that you can get
an APR of 6.5% on such a mortgage loan at a local
bank.
Suppose the loan calls for equal monthly payments. Set up a
monthly amortization schedule table for the loan (Input:
20%, creating and copying formulas and Excel
finance functions: 30%).
What is the sum...
Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash...
Suppose an apartment you want to purchase costs $500,000. You
can put down 20% in cash and take out a 30-year fixed rate mortgage
loan for the remaining. You believe that you can get an APR of 6.5%
on such a mortgage loan at a local bank. Suppose the loan calls for
equal monthly payments.
1. Set up a monthly amortization schedule table for the loan
(Input: 20%, creating and copying formulas and Excel finance
functions: 30%). Use 360 months...
You are planning to purchase a house that costs $480,000. You plan to put 20% down...
You are planning to purchase a house that costs $480,000. You
plan to put 20% down and borrow the remainder. Based on your credit
score, you believe that you will pay 3.99% on a 30-year
mortgage.
Use function “PMT” to calculate your mortgage payment.
Use function “PV” to calculate the loan amount given a payment
of $1700 per month. What is the most that you can borrow?
Use function “RATE” to calculate the interest rate given a
payment of $1700...
If you want to purchase a home. You have $10,000 to put down. All you can...
If you want to purchase a home. You have $10,000 to put down.
All you can afford is $1000.00 per month and you do not want to
finance for more than 15 years @ 5%, (your taxes will be $55.00 per
month and insurance $60.00 a month), what is the amount you can pay
for the house? (Show all your work)
You financed the purchase of a $300,000 apartment with a down payment in cash of 20%...
You financed the purchase of a $300,000 apartment with a down
payment in cash of 20% of the purchase price. The remaining 80% is
financed with a mortgage with a 1% monthly interest rate over the
next 20 years. The mortgage is repaid with equal monthly
installments.
Compute the monthly installments on the mortgage. (5
points)
What is the outstanding principal balance of the mortgage after
5 years (i.e., after 60 installments)? (5
points)
Ten years later (after 120 installments),...
You are planning to purchase a condo that costs $480,000. You plan to put 20% down and borrow the remainder.
You are planning to purchase a condo that costs $480,000. You plan to put 20% down and borrow the remainder. Based on your credit score, you believe that you will pay 3.25% on a 30-year mortgage.
Use function “PV” to calculate the loan amount given a payment of $1550 per month. What is the most that you can borrow? (Be sure that PV is the amount you borrow, not the cost of the house.)
Use function “PMT” to calculate your...
You put 20% down on a home with a purchase price of $250,000. The down payment...
You put 20% down on a home with a purchase price of $250,000.
The down payment is thus $50,000, leaving a balance owed of
$200,000. A bank will loan you this remaining balance at 3.91% APR.
You will make monthly end-of-the-period payments with a 30-year
payment schedule. What is the monthly annuity payment under this
schedule?
The purchase price of a property, land and improvement, was $1,625,000. you put 20% down payment...
The purchase price of a property, land and improvement, was
$1,625,000. you put 20% down payment and the rest was financed. the
mortgage is a 30 year loan at 6.5% principal-interest payment.
taxes and insurance at the time of purchase are $18,000 each year.
the vacancy rate is 8%, while the repair expenses averaged is
$8,500 every year. you spend annually about $2,000 for
miscellaneous and advertising costs. you manage the property
yourself. determine the monthly cash flow from 30...
Suppose you are buying the first house for $500,000 with 20% down payment. You have arranged...
Suppose you are buying the first house for $500,000 with
20% down payment. You have arranged to finance the remaining amount
with a 15-year, monthly payment, amortized mortgage at nominal
annual rate of 3.6%. What is remaining balance after 10 years with
120 monthly payments?
$180,68.90
$79,777.39
$167,123.45
$157,881.37
Which of the following statements is/are
INCORRECT?
Unlimited liability and limited life are two key
advantages of the corporate form over other forms of busiess
organizations.
Limited liability is an advantage...
Suppose that you want to purchase a home for $220,000 using a down payment as described...
Suppose that you want to purchase a home for $220,000 using a
down payment as described below and you finance the rest of the
purchase by taking out a 15-year mortgage at 4.5% interest
compounded monthly. The amount of the down payment is
$56,083. Construct an amortization table for the entire length of
the loan making sure to include columns for the payment number, the
amount of the payment, the amount of each payment that goes towards
the principal, the amount...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- An investor is considering purchasing a Treasury bond with a 20 year maturity, an 8% coupon...
- Write a program that copies the contents of one file to a destination file. This program...
- how to conduct a marketing mix for 3d crest white strips
- Is big data analytics used for service innovation at Amazon? If so, please write a brief...
- A shrink-fit process has been used to enhance the pressure carrying capacity of a cylinder. During...
- Ten-year zero coupon bonds issued by the U.S. Treasury have a face value of $1,000 and...
- The Blossom Department of Transportation has issued 25-year bonds that make semiannual coupon payments at a...
ADVERTISEMENT