find selling price of bonds

[charlene]

is there a way in excel to calculate selling price of bonds

[David McRitchie]

Hi Charlene,

= (amount.received + brokerage.fees) / number.of.bonds

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"charlene" <charlene@discussions.microsoft.com> wrote in message news:D16EC3E8-2818-4480-8B8F-2DEA17A4CE8B@microsoft.com...
> is there a way in excel to calculate selling price of bonds

[vandenberg p]

Try the =PRICE() function in the Analysis Tool Pak. In order to
use it you need to make sure that addin is loaded (Tools/addins Analysis Tool Pak).
Once the addin is loaded it works just like any other function.
If you can't find it then you need to load it from the Office Install CD.
The help file will help you fill in the needed information.

An approximation could be achieved by the following example:

A B
Coupon 50
Periods to maturity 20
Yield to maturity 0.06
Price ($885.30)

Assume that titles start in A1 and the values start in B1
The formula in B4 is =PV(B3,B2,B1,1000)

Be sure that the coupon $, the periods to maturity and
the the Yield to maturity (YTM) all are for the same frequency.
The above bond would be for a 10 year bond paying the coupon
of $50 twice a year (10% a year) and YTM be 6% per period
(12% year Compounded semi-annually) and the bond has a face
value of $1,000. This will give you
the correct answer assuming that you bought it on the coupon
date.

The toolpak process is more robust in that it calculates prices
between coupon dates. I have not double checked that the Excel
procedure exactly matches the procedure used in US Bond markets.
But it appears to be close.

Pieter Vandenberg

charlene <charlene@discussions.microsoft.com> wrote:
: is there a way in excel to calculate selling price of bonds