Calculates the monthly payment

Assignment Help Finance Basics
Reference no: EM13664603

1. You plan to buy a piece of machinery that costs $1.2 million. You'll put down 20% and finance the balance at 9% per year for 3 years, with monthly payments.
a. Create an Excel model that calculates the monthly payment and an amortization schedule for the loan.

b. You plan to replace the machinery with a more advanced model at the end of 3 years. You predict you will be able to sell the old machinery for $500,000 at that time, so you want to set up your loan so that rather than completely amortizing the amount borrowed, you end up at the end of 3 years still owing $500,000. (This is the same as setting your loan up with a $500,000 balloon payment at the end of the loan.) Set up an Excel model that calculates your monthly payment under this new scenario and construct an amortization schedule for the loan. Verify that your loan balance at the end of three years is, indeed, $500,000.

2. You are 45 years old and plan on retiring at age 60. You are in good health and expect to live to age 85. Your retirement account earns 9% per year.
a. How much must you save per month, with your first contribution at the end of the month, until retirement if you wish to withdraw $15,000 per month in retirement income? Your first withdrawal will be at the end of the first month of retirement and your last retirement contribution will be on the day you retire.

b. You don't think you can save that much per month. How much must you save per month if you delay retirement from age 60 to age 65?

c. Upon second thought, you've realized that with inflation, $15,000 per month is unlikely to meet your consumption and travel needs when you retire. Rather, you would like to guarantee that during each month of retirement you have income that has the same purchasing power that $15,000 has now. You expect inflation to be 3% per year, including your years in retirement. How much must you save per month until retirement to fund this new plan assuming you will retire at age 65? Hint: your retirement withdrawal will increase by the inflation rate every month; it won't be an annuity (which has level payments). This means you will need to project each month's retirement withdrawal and use the NPV function to find the present value of all of the payments. Project these monthly withdrawals out to the right and use monthly compounding for the inflation to make the calculation of your monthly withdrawals easier. Another hint: Your first withdrawal WON'T be $15,000! It will be much bigger than that!

3. Your auto dealer offers you a lease on an $80,000 car. The terms of the lease are as follows: You must make an initial payment of $5,000. Then, your monthly payment is $799 per month for 5 years. At the end of 5 years you can either turn in the car (and pay a penalty if you've driven it too many miles, or trashed it, but let's ignore that clause for now), or you can purchase the car outright at the end of the lease for $45,000.

What is the implied annual interest rate on this contract? That is, if this were a loan, then what would be the annual rate on the loan? Hint: use the RATE function, which will give you a monthly rate. Multiply that monthly rate by 12 to get the annual rate.

4. Consider a 20-year, $1,000 par value, 7% coupon bond with semi-annual payments.

a. What is the price if the yield to maturity is 9%?

b. What is the yield to maturity if the price is $1,250? Hint: Don't forget the RATE function will give you the 6-month rate. You need the annual rate.

c. What is the current yield (current yield is not the yield to maturity!) if the price is $1,250?

d. Suppose the bond can be called in 5 years for a 10% premium; that is, if the company calls the bond, it will pay $1,100 rather than $1,000 to redeem the bond. If the price of the bond now is $1,250, then what is the bond's yield to call? How does this compare to the yield to maturity? Why are they different?

5. You have 4 different bonds in your portfolio:
· $1,000 par value 10-year 15% coupon bonds with semiannual payments with a yield to maturity of 10%.

· $1,000 par value 10-year 5% coupon bonds with semiannual payments with a yield to maturity of 10%.

· $1,000 par value 20-year zero coupon bonds with semiannual payments with a yield to maturity of 10%.

· $1,000 par value 5-year 15% coupon bonds with semiannual payments with a yield to maturity of 10%.

a. Calculate the price of each of the bonds.

b. Suppose the yield to maturity on each of the bonds declines from 10% to 8%. Calculate the new price of each bond and calculate the percent change in bond price due to the yield change.

c. What can you say about the interest rate sensitivity of bonds? What types of bonds have the biggest percentage price change due to a change in interest rates, and what types of bonds have the smallest percentage price change?

6. You have been offered an investment opportunity that has the following cash flows:
Year 0 1 2 3 4 5
Cash Flow ? 200 400 -100 500 200

a. If other investments with comparable risk are earning 12%, then how much should you be willing to pay for this investment?

b. If you can purchase these cash flows for an initial investment of $800, then what is the annual rate of return on this investment? Hint: With what you know at this point, you have to use trial and error.

7. Consider a security that pays $100 per year forever. Securities of this riskiness are earning a rate of return of 7%.
a. What should be the price of the security?

b. What should be the price of the security one year from today?
c. Suppose you purchased the security today, received the first cash flow a year from now, and then sold it at the price you calculated in part b. What would be your rate of return on your investment? Hint: The rate of return over a 1-year period is the capital gains plus the cash payments divided by the initial price: annual return = (P1 - P0 + CF1)/P0.
d. What can you conclude from this observation?

8. Suppose a security pays $100 at the end of one year. This payment will grow at a rate of 20% a year for 2 years, and then will grow at a rate of 10% for one year, and then grow at a rate of 5% thereafter forever. Securities of this riskiness have a rate of return of 9%. See Sections 5.6 and 5.8 for valuing this type of security. Hint: Draw a timeline!

a. What should be the price of this security?

b. What should be the price of the security one year from today?

c. Suppose you purchased the security today, received the first cash flow a year from now, and then sold it at the price you calculated in part b. What would be your rate of return on your investment?

Reference no: EM13664603

Questions Cloud

Mercury and water are immiscible in containers : Two identical containers are open at the top and are connected at the bottom via a tube of negligible volume and a valve which is closed. Both containers are filled initially to the same height of1.00 m, one with water, the other with mercury, as the..
What is value of the effective spring constant of the water : A balsa wood block of mass 52 g floats Ona Lake, bobbing up and down at a frequency of 2.6 Hz. What is the value of the effective spring constant of the water?
What will be the price of the bond : What will be the price of the bond?
Decide the pressure that each ski exerts on the snow : A 65-kg skier is going down a slope oriented 33° above the horizontal. The area of each ski in contact by the snow is 0.13m2. Decide the pressure that each ski exerts on the snow.
Calculates the monthly payment : Calculates the monthly payment
What is her new rotation frequency in revolutions per second : A 45 kg figure skater is spinning on the toes of her skates at 1.0 rev/s. Her arms are outstretched as far as they will go. In this orientation, the skater can be modelled as a cylindrical torso (40 kg, 20 cm average diameter, 160 cm tall) plus two r..
What is the portfolio variance : What is the portfolio variance
A particular radioactive source produces : A particular radioactive source produces 150 mrad of 2 MeV gamma rays per hour at a distance of 1.0 m. How long could a person stand at this distance before accumulating an intolerable dose of 1 rem?
Length of the shadow of the pole on the level bottom of pool : In the figure below, a 2.00 m long vertical pole extends from the bottom of a swimming pool to a point 15.0 cm above the water. Sunlight is incident at angle θ = 55.0°. What is the length of the shadow of the pole on the level bottom of the pool in c..

Reviews

Write a Review

Finance Basics Questions & Answers

  Using foreign-currency futures options instead of underlying

Using foreign-currency futures options instead of underlying foreign-currency futures contracts:

  Computation of bond''s nominal yield to maturity

Computation of bond's nominal yield to maturity and their nominal yield to call and what return should investors expect to earn on this bond

  An article of relevance to cotemporary corporate finanance

Find an article of relevance to cotemporary "corporate financial" issues from various business publications (daily newspapers, weekly or monthly business magazines, financial web sites, etc) and finance academic journals. After reading the ar..

  Determining core income is an important first step to

determining core income is an important first step to estimating permanent income. explain. what adjustments to net

  What has been the average annual nominal rate of interest

what has been the average annual nominal rate of interest on treasury bills over the past 107 years 1900 -

  What additional earnings after taxes result from overhaul

What additional earnings, before depreciation and taxes, will result from the overhaul?

  Mickelson corporation will pay a 290 per share dividend

mickelson corporation will pay a 2.90 per share dividend next year. the next company pledges to increase its dividend

  Computation of npv of the project option and evaluation

Computation of NPV of the project option and evaluation and you are considering a project which has been assigned a discount rate of 8%

  The target capital structure of qm is 37 common stock 13

the target capital structure of qm is 37 common stock 13 preferred stock and 50 debt. if the cost ofo common equity for

  The assignment is to choose a stock that trades on the new

the assignment is to choose a stock that trades on the new york american or nasdaq. choose a day of the week and find

  Examine and discuss the characteristics of npv and the role

in working out your responses to the discussion question you should choose examples from your own experience or find

  Opinion polls attempt to predict the results of local

opinion polls attempt to predict the results of local state and federal elections. discuss six reasons why the results

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd