Question: Create a spreadsheet to price a 5 year and 10 year annual pay UST. Assume the coupon = 3.50%, and the yield to maturity (ytm)

Create a spreadsheet to price a 5 year and 10 year annual pay UST. Assume the coupon = 3.50%, and the yield to maturity (ytm) is 3.50%. Face Value = $1000

a. What is the price (present Value = PV) for both?

b. Now use a 4.50% ytm (i.e. a “100bp change in ytm) and recalculate. How much did the price (PV) of both bonds change in % terms?

Please show this work on Excel.

Step by Step Solution

★★★★★

3.42 Rating (152 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Heres how you can create a spreadsheet in Excel to calculate the price present value of a 5year and ... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...

KYC's stock price can go up by 15 percent every year, or down by 10 percent. Both outcomes are equally likely. The risk free rate is 5 percent, and the current stock price of KYC is 100. (a) Price a...

In this problem, we want to create a spreadsheet to help someone estimate their income in retirement. To do this, we will have them enter several numbers: the amount of money currently in savings,...

show a numerical example of interest rate risk by comparing % changes in price for 1 year and 3 year annual coupon bonds. Assume coupon interest rate = 10%, Yield to Maturity = 5%, Face value= 100....

Can you answer these? Choose the alternative that BEST answers each of the following questions. 1. You invest $1 at 8% compounded annually. How long does it take for the $1 to double? A) B) C) D) E)...

QUESTION 1 The expected yield on junk bonds is higher than the yield on AAA-rated bonds because of the higher default risk associated with junk bonds. True False QUESTION 2 The A Company will pay a...

2. You are given the following continuously compounded forward rates: 070,2= 6.5%,o$2,3 = 8.0%, 072,4= 8.0%, and, 12,5= 8.0%. You also know that Bond A, which matures at the end of year 5 and pays...

A B D E F G 1 Callable Bond Valuation Bond Issuer: ABC Corp Assumptions Current Market Price Bid Offer 89 90 Bid/Offer prices are quoted as a percentage of par $1,000 Par or Face Value Call Premium...

Consider the following term and YTM of 5 hypothetical Treasury securities with a par value of 100 Ksh. Term Coupon rate YTM price 0.5 0 0.080 96.15 1 0.0 0.083 92.19 1.5 0.085 0.089 99.45 2 0.09...

QUESTION A Consider the following term and YTM of 5 hypothetical Treasury securities with a par value of 100 Ksh. Term Coupon rate YTM price 0.5 0 0.080 96.15 1 0.0 0.083 92.19 1.5 0.085 0.089 99.45...

Repeat Prob. 525 using maximum-shear-stress

Calculate the no. of Photion of light with Wavelength of of energy. Gooo 4 that provide I Joule

RBRVS is: a . Acronym for Real Based Reference Verification System b . The relative weight for calculating OPPS payments c . The relative weight for calculating MS - DRG payments d . Medicare...

What evidence is there in favor of the idea that the earths interior is very hot?

Find Io, (t),t>0 in the network.

The scatterplot shows the number of books read and the English grade for all 79 students in the study. The least-squares regression line y = 3.42 + 0.024x has been added to the graph. a. Interpret...

Solve each differential equation by variation of parameters. y'' - 2y' + y = e x /1 + x 2

Three funds of the Leukemia Foundation, a nonprofit welfare organization, began an investment pool on January 1, 2021. The costs and fair market values on this date were as follows: During 2021, the...

Rosalind and Timea have agreed to rescind their contract. Rosalind, during the performance of the contract, received a silk scarf designed by Timea, and Timea received payment of $500. Rosalind...

An electronics firm is planning to manufacture a new handheld gaming device for the preteen market. The data to the left have been estimated for the product. Assuming a negligible market (salvage)...

Storm doors have been installed on 50% of all homes in Anytown, USA. The remaining 50% of homeowners without storm doors think they may have a problem that a storm door could solve, but they're not...

Determine the value of W on the right-hand side of the accompanying diagram (see Figure P4-68) that makes the two cash-flow diagrams equivalent when i = 12% per year. 1,000$1.000 0. End of Year End...

For each of the 32 National Football League teams, the numbers of points scored and allowed during the 2012 season are shown below. Assuming these are sample data, answer the following questions. You...

Every April, The Mastersone of the most prestigious golf tournaments on the PGA golf touris played in Augusta, Georgia. In 2013, 60 players received prize money. The 2013 winner, Adam Scott of...

The table below shows the number of cars (in millions) sold in the United States for various years and the percent of those cars manufactured by GM. Use a statistical software package to answer the...