FAQs

Watch the Online Video Course Excel 2007: Financial Analysis

2h 18m Intermediate Aug 25, 2009

Viewers: in countries Watching now:

Calculating the value of zero coupon bonds

Bonds that issue coupons or pay interests to investors at regular intervals offer bondholders the option to invest their earnings elsewhere. Some issuers decide to offer zero- coupon bonds, which only pay interest at the end of the investment period. A zero-coupon bond sale lists the price and the payment due when the bond matures, but doesn’t usually list the investment’s interest rate. You can calculate that rate for yourself using the RATE function. The RATE function has three required arguments and three optional arguments. The required arguments are nper, the number of periods, pmt or payment, which are any payments made each period.

And present value, which is what the investment is worth today. That is the cost to purchase the investment. The optional arguments are fv, the future value of the bond, type, whether you pay at the beginning or end of an investment period and guess, which is a guess as to the interest rate. If you leave guess blank, Excel assumes a value of 10%, which is often a good enough starting point for Excel to zero in on the bonds annual percentage rate. So I’ll finish filling out this RATE formula. So we have the number of periods, and that is the number of years in B3, multiplied by the Periods Per Year 12, which are in cell B4, then the Payment that in this case is 0. That’s in cell B5.

And then we have the Present Value. The Present Value is the amount that you pay to invest in the zero-coupon bond. Payment is 0 because you do not make a monthly payment, instead you put all the money in upfront and that indicates the present value. Present Value is expressed as a negative number, because it represents an expense or negative cash flow to you. So you have it as a negative number. And that is in cell B6, and then you have the Future Value, which is in cell B7. And in this case, you have a Future Value of $12,500.

I’m not going to use either type or guess. So I’ll close the parenthesis. Now you’ll notice that the formula I have here below includes a multiplication by the number of periods per year. Not multiplying by the number of periods per year results in a formula that makes sense, but actually produces a result other than what you want. So I’ll press Return and you see that you have a result of 0.37%. What that means is that is that you have calculated the monthly rate. When you multiplied by 12, the number of periods per year you change the calculation so that you actually got the rate for a month.

So each month you make .37% of interest, but if I multiply the result of the RATE formula by B4, the number of periods per year then you get 4.47%. By way of checking my math, I input 4.47% into a future value formula divided by 12 and then use the same terms that I used in the RATE calculation to come up with a value of $12,499.27 which is just a rounding error away from the rate as expressed in cell B9.

You should consult with your tax professional about the advisability of investing in a zero-coupon bond. The reason is that you might owe taxes on interest earned even if you haven’t received any interest payments.

There are currently no FAQs about Excel 2007: Financial Analysis.