Complete the following table using the Excel functions like we did for homework: Hint: There is...

Complete the following table using the Excel functions like we did for homework:
Hint: There is no function for calculating coupon payments.
(Assume semi-annual coupon payments and the bond's par value is $1,000.)

Bond	Coupon Rate	Yield to Maturity	Periods to Maturity	Annual Coupon Payment	Current Bond Value	Discount or Premium
A	3.0%	5.0%	10
B	4.5%	4.0%	10
C	3.5%	6.0%	10
D	5.0%	5.0%	10
E	11.0%	10.0%	10

Solutions

Expert Solution

Hey there !!

In this question we have to calculate the following using excel functions :

1. Annual Coupon Payment

2. Current Bond Value

3. Discount/ Premium

Look at the table, In the table, i have completed the missing values and you can see how a particular figure is appearing by looking at the column heading.

I have explained each and every column. Refer the content in brackets in the columns.

A	B	C	D	E	F	G	H	I	J	K
Bond	Coupon Rate	Yield to Maturity/ Discount Rate	Periods to Maturity	Par Value of Bond	Semi Annual Coupon Rate = Column B/2 ie Coupon Rate/2	nper = Period to maturity x 2 (Column D x 2)	Annual Coupon Payment = B X E	Semi Annual coupon Payment = F x E	Current Bond Value Using PV Formula	Discount or Premium =E-J
A	3%	5%	10	1000	1.50%	20	30	15	? 844	? -156
B	5%	4%	10	1000	2.25%	20	45	22.5	? 1,041	? 41
C	4%	6%	10	1000	1.75%	20	35	17.5	? 814	? -186
D	5%	5%	10	1000	2.50%	20	50	25	? 1,000	? 0
E	11%	10%	10	1000	5.50%	20	110	55	? 1,062	? 62

To calculate current bond value using excel, we have used PV Formula.

Let me explain you how to use PV function in excel...

Type this in excel = - pv (rate, nper, pmt, [fv],[type])

Now, Rate to be used is= Discount Rate / 2 (since semi annual payments)

nper = Periods to maturity * 2 (since semi annual payment)

pmt = Semi annual coupon payment

[FV] = face value of bond

Put the above figures in the table as per the given formula and then you will get the current price of the bond.

Do let me know if you have any other concerns....happy to help....

All the best !!! :)

ekkarill92 answered 1 year ago

Next > < Previous

Related Solutions

using excel functions Complete the following without a table (round each calculation to the nearest cent...

using excel functions Complete the following without a table (round each calculation to the nearest cent as needed): 1. principal time rate of compounding interest compounded NUMBER of periods compounded total amount Total interest $ 500 1 year 8% quarterly A. ? B. ? C.? Solve the previous problem by using compound value (FV). See Table 12.1. Lionel Rodgers deposits $7,000 in Victory Bank, which pays 4% interest compounded semiannually. How much will Lionel have in his account at the...

using excel functions Using Table 13.1, (a) find the value of an investment after 4 years...

using excel functions Using Table 13.1, (a) find the value of an investment after 4 years on an ordinary annuity of $5,000 made semiannually at 4%; and (b) recalculate, assuming an annuity due. Wally Beaver won a lottery and will receive a check for $2,500 at the beginning of each 6 months for the next 6 years. If Wally deposits each check into an account that pays 6%, how much will he have at the end of the 6 years?...

using excel and it's functions The table shows a sample of the maximum capacity (maximum number...

using excel and it's functions The table shows a sample of the maximum capacity (maximum number of spectators) of sports stadiums. The table does not include horse-racing or motor-racing stadiums. 40,000 40,000 45,050 45,500 46,249 48,134 49,133 50,071 50,096 50,466 50,832 51,100 51,500 51,900 52,000 52,132 52,200 52,530 52,692 53,864 54,000 55,000 55,000 55,000 55,000 55,000 55,000 55,082 57,000 58,008 59,680 60,000 60,000 60,492 60,580 62,380 62,872 64,035 65,000 65,050 65,647 66,000 66,161 67,428 68,349 68,976 69,372 70,107 70,585 71,594...

Excel Homework: We are going to start working on sample statistics—that is, on measures we can...

Excel Homework: We are going to start working on sample statistics—that is, on measures we can sue to describe a sample. The two that we will learn about today are histograms, and 5 number summaries (which can be represented using box-plots). 1. You are designing a game, and considering having players roll a six-sided die and a 20-sided die, and add the values of each die. To get a picture of what happens when someone rolls dice like that, you...

Complete the following using the present value table 12.3.

Complete the following using the present value table 12.3.amount desired at end of period, length of time, rate, compounded.On PV table 12.3period used, rate used, pv factor used, pv of amount desired at end of period. 12-10. $20,000 20 years 8% Annually

Use the Excel time value of money functions to complete the following problems. Highlight your answers....

Use the Excel time value of money functions to complete the following problems. Highlight your answers. Upload your solution on blackboard. 1) Manny’s grandparents gave him $1,800 for his birthday. He opened a savings account that pays 4% annually. How much money will he have in 7 years if he does not make any withdrawals? N= I= PV= PMT= FV= 2) Your parents will retire in 25 years. They currently have $100,000 in savings. They think they will need $1,000,000...

What excel codes would one need to complete the following functions? From the original sample of...

What excel codes would one need to complete the following functions? From the original sample of 100 data values you generated (including any outliers), create a new data column and label it as Measles. Use the following criteria to define the measles percent as either low, mid or high. Low Measles immunization is 60 percent or younger Mid Measles immunization is between 60 and 90 excluding endpoints High Measles immunization is 90 percent and more

Instructions: Complete the following problems using the format outlined in the homework requirements document. 1. Photons...

Instructions: Complete the following problems using the format outlined in the homework requirements document. 1. Photons are incident on an Aluminum plate and electrons are emitted as a result of the Photoelectric effect. Provide answers to the following questions: a. What threshold energy must the photon have to produce a photoelectron from Aluminum, which has a work function of 4.20 eV? b. Calculate the maximum energy of a photoelectron ejected from Aluminum by UV light with a wavelength of 1500...

Complete the following using present value. (Use the Table provided.)

Time Value of Money Complete the following exercise using MS Excel. Using the Present Value and...

Time Value of Money Complete the following exercise using MS Excel. Using the Present Value and Future Value Equations 4. If you invested $200 at 5%, how much would it be worth in 30 years? 5. How many years does it take to double your money if it is invested at 6%? 6. If you invest $10,000 in a 20 year annuity paying 5%, what would be the annual payment made to you? 7. If you have a student loan...

Subjects