Bond Purchase Price using Excel

Tallulah

New member
Joined
Jun 21, 2011
Messages
22
I trying to determine the range of prices that one should consider in order to purchase a bond. The information provided is as follows:

The bond has a face value of $7000 and a bond rate of of 6% per year, payable at 3 percent semiannually. The bond has a remaining life of 5 years. If you want to earn at least 8% per year compounded semiannually, at waht range of prices should you be willing to purchase the bond.

So, I am not sure how to determine my monthly payment (if there is a min and a max or just one payment I shoudl be considering) or how to determine the price ranges.

I have determined that the number of periods is 10 (5 years x 2 periods/year). To calculate the payment amount, I am not sure which rate to use--3% or 8%/2 or both to determine min and max prices. If I use both 3% and 8%/2, I calculate payments of $210 and $280, respectively. From there I calculate the min and max purchase prices using the excel formula PV(rate, nper, pmt,-pv). However, I am further confused on which rate I use in this equation. Do I use the 3% and 8%/2 that I used to calculate the payment/period or the bond rate of 6%.

Can someone please help guide me through this problem. THank you.
 
The coupon is $7000*0.03 every half year. Your return has nothing to do with this.

Your purchase price is based on your desired return. Your minimum return should correspond to your maximum purchase price.
 
I do not even understand the question. if you are willing to purchase the bond at x, would you not be willing to buy at any lower price? Would you be willing to buy it if you were paid to do so? So I do not get "range" unless x to minus infinity is meant. Did you specify the problem exactly as it was posed?
 
Can you answer this:
you wish to borrow an amount, rate being 4% per period, and offer the following payments:
$210 per period for 9 periods
a final payment of $7210 at 10th period;
how much will the amount borrowed be?

If you can't recognize that as the equivalent to your problem,
then you need classroom help.
 
I figured out the answer to the question. It is $6,4312.24. I used the excel function =PV(0.04,10,-(7000*.03),-7000,)

The question I posted is exactly how it was written in the book. Additionally, thank you for pointing out that I am struggling with these concepts. I am currently in an online class with a teacher that expects us to read a chapter and then do the work without providing any examples or explanations. He is also unwilling to provide adequate answers to your question in fear that he is giving away the answer.
 
Tallulah said:
I figured out the answer to the question. It is $6,432.24. I used the excel function =PV(0.04,10,-(7000*.03),-7000,)
That's correct...but you should be able to do these by formula; relying on a calculator is bad news!

Formula for PV of amount A: A / (1 + i)^n
In your case: 7000 / 1.04^10 = 1703.288...

Formula for PV of regular payment D : D[1 - 1/(1 + i)^n] / i
In your case: 210(1 - 1/1.04^10) / .04 = 4728.949...

Add 'em up: 1703.288... + 4728.949... = 6432.237...

n is the number of periods and i is the interest rate...
Ya'll ok with that??
 
Dennis:
Thank you for the explanation. However, I dont understand why I would use 2 different PV formulas. Is it because I have to determien the payment about per month to the bond holder as well as waht the final payment (the par value) would be at the end of the period as separate calculations?
 
C'mon buddy...look at what Excel does; as you said : "excel function =PV(0.04,10,-(7000*.03),-7000,)"
That's calculating the PV of 10 payments of 7000*.03 = 210 ; then the PV of 7000; see that?
Excel uses the 2 formulas to do this.
 
Top