 |
|
|
 |
New Member
|
|
Mar 1, 2010, 05:38 PM
|
|
Finance Excel Question
I am creating a worksheet for a "bond calculator" as assigned. I have calculated Price using the PV formula, Interest on Interest plus Coupon using the FV formula, and expected price at the end of Investment Horizon. I am asked to compute total future dollars but I am not sure if this is the same as expected price at the end of I/H or if this is the sum of the expected price and the interest on interest and coupon. I have currently input it as the sum of Interest on interest and coupon, and expected price at the end of I/H but I need to be sure because I have to go on to use in in the rate formula for Horizon Return and then make some tables... the numbers I have are as follows:
Coupon Rate: 8%
Par Value: $1,000
Term to maturity: 30 years
periods per year: 2
yield to maturity: 10%
Holding period: 10 years
reinvestment rate: 6%
expected yield at the end of I/H: 9%
I have calculated:
Price: $810.71
Interest on interest plus coupon: $6,522.14
Expected price at the end of I/H: $907.99
Total Future Dollars: $7,430.13
Horizon Return: 23%
Any help is appreciated
Robert
|
|
 |
Uber Member
|
|
Mar 2, 2010, 12:58 AM
|
|
I hope you meant it when you said "any help." :p
I know how to do time value of money, but I'm coming more from an accounting than finance point of view, so not sure about some of those terms. I can definitely help with part of it, and maybe all of it with some questions answered, if you know the answers. I also never do this stuff in Excel, so can't help you there - in fact, those terms may be Excel terms, I don't know. Perhaps someone will come along who knows that part.
First off, the 810.71 is correct.
I would figure "total future dollars" to mean future cash flows, which would absolutely not be the same thing as an expected price. The price would be the present value at some point in time. If you sold the bonds, that would be ONE of the future cash flows, but not "the same as." (Unless it's a zero coupon bond, which it's not.)
I'm not sure what the "investment horizon" is. You've treated it as the 10 years in the bond, because you did the expected price as 907.99, which is what I came up with using the 9% expected yield after a 10 year period. (Leaving 20 years.) So we're either both wrong or both right. :p
OK, now the questions. I don't understand the whole reinvestment part. I don't know what exactly you're reinvesting and for how long. But there's a rate (6%), so it must be done. The first senario I see is that you take your interest payments that you're getting from the bond and reinvesting those as you get them. Is that the idea? Then at the end of 10 years, you have all those interest payments plus the interest at 6% (i.e. the future value of $40 annuity), plus the 907.99 you sell the bonds for. Is that all right? Does it stop there? Or are you then taking all of that and continuing to invest that for the next 20 years? It doesn't really specify. I've tried stopping at the end of 10 years, and I've also tried taking everything you'll have at that point and continuing to invest it at 6% for the next 20 years. (The rest of the life of the bonds, though in reality that would be irrelevant.) No matter what I do, I cannot get your answers of 6522.14 or 7430.13. I don't know where you've gotten those.
If I could find out exactly what's happening with the "reinvestment" part, I could tell you how to do the rest. (Or at least what you need to be doing mathematically since I don't know how to do it in Excel.)
I have currently input it as the sum of Interest on interest and coupon, and expected price at the end of I/H
By interest on interest, I'm assuming you mean the 6% that could be gotten on the $40 interest payments you're getting. But either way, the error in here, possibly what's throwing your numbers off, is that you won't get the coupon and the expected price. You wouldn't get the coupon unless you held it for the 30 years. If it's going to be sold (or even company call it in) after 10 years, you'll get the expected price. You can't get both. In fact, maybe that answered everything, I don't know.
|
|
 |
Uber Member
|
|
Mar 2, 2010, 01:12 AM
|
|
I see where you got the 6522.14. That's the FV of 30 years worth of $40 interest payments. I still don't understand the senario of the reinvestment but that doesn't make sense to me. The only way you would have 30 years worth of those payments is if you didn't sell the bond after 10 years, in which case the 907.99 becomes irrelevant. You're either keeping the thing or you're selling the thing. If you sell it, you can keep reinvesting the money you have already gotten, but you won't be getting those $40 payments anymore.
Then I see that your last number is adding on the 907.99. That's like saying you're keeping the bond for 30 years, getting all the interest payments, but then selling it at a price that was good 20 years ago, instead of getting the coupon back.
You have to choose. You're either selling it at 10 years, at which point in time you get 907.99 (not 1000), and the $40 payments stop. (And you can continue to invest that whole amount.) OR, you keep it for 30 years, keep getting the payments, but then get $1000 at the end of that. You seem to be trying to mix the two.
You can of course do both, and then compare them. But "interest on interest plus coupon" doesn't equal 6522.14 cause it doesn't include coupon. It's just the FV of the interest payments.
|
|
 |
Senior Member
|
|
Mar 2, 2010, 06:10 AM
|
|
Robert, I agree with Morgaine that we all first need to be clear on just what's being asked for. Here's my stab at how the question sees things as playing out:
You buy the bond today for $810.71 (your current pricing of the bond is correct). You hold the instrument for 10 years, and in so doing you capture
- 20 semiannual coupons of $40 each;
- The expected trading price of the bond at your sale date (I also concur with your 907.99 expected pricing on the bond at 10 years hence).
You immediately roll each coupon--upon receipt--into a vehicle earning 6% compounded semiann.
Given all that, what you'll have at the 10-year mark is (1) 1,074.81, the amount to which the reinvested coupons have grown; plus (2) the sales proceeds of the bond, 907.99; for a total of 1,982.80.
After that, it's a little hazy as to what the question's terminology is asking for. If your investment horizon is indeed 10 years, then your holding-period return (or "horizon return") would be determined as that rate which causes an initial investment of 810.71 to accumulate to a horizon-value of 1,982.80.
If you could toss us a bit more guidance on what the question's asking for (especially that 'interest on interest plus coupon' thing), it'd be helpful.
|
|
 |
Uber Member
|
|
Mar 3, 2010, 02:26 AM
|
|
Er... the "coupon" is the interest? Well, I screwed that part up. :-) I do have those same numbers (e.g. 1982.80 etc), but confused over some terms. "Interest on interest plus coupon" just sounds darn strange to me.
|
|
 |
Senior Member
|
|
Mar 3, 2010, 08:43 AM
|
|
Roger that, M3... I'm curious to hear what 'IOIPC' means myself. Of course, every field has some variation in its jargon, so it'll be something that goes by other names, I'm sure.
And I'm glad to see you came to 1,982.90 as well... now I can toss my scratch notes :)
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Finance Question
[ 2 Answers ]
Calculating the number of payments:
Your're prepared to make monthly payments of 125, beginning at the end of this month, into an account that pays 10% interest compnounded monthly. How many payments will you have made when your account balance reaches 20,000. I know you solve for t but I get...
Excel question
[ 10 Answers ]
I've got a spreadsheet of 1688 rows.
I only want rows 1, 9, 17, 25, 33, etc. (deleting 7 lines between each one I want to keep).
I don't want to just delete the contents of the other lines, I want to delete the lines themselves, so that I end up with current rows 1, 9, 17 etc. being 1, 2, 3...
Question about MS Excel
[ 1 Answers ]
Hi,
I want to copy & transpose the content of one spreadsheet to another without cell reference changing. It seems that I just cannot use Paste Link and Transpose at the same time. What should I do?
Thanks in advance,
Wei
Excel question
[ 1 Answers ]
I have a number of worksheets in one excel file. I have been doing VLOOKUPS but now I want to know how to get once cell to = one cell in another worksheet. I hope someone knows??
QUestion Excel
[ 1 Answers ]
Hello, my name is Carmen and I live in Mexico, City.
I don' t speak English very well but I try to state my question:
In Excel I want to put in order some data for some column. I use the "hierarchy" function because I want a number of the order for the column "grade". But in this function I...
View more questions
Search
|