K
Kristen
I am trying to determine the PV of a stream of cash flows that have
mixed rates and periods in order to determine the price of a Bond from
the YTM of various bonds from 6 months to a few years (see below).
Below is my idea. But it is not quite correct.
XNPV has been suggested in the other function forum, but I need a
single formula that will handle annual and semi-a periods and new
data.
(Hope this is not confusing or messed up when I post it.)
Thankyou for your help
Kristen
1 Annual/Semi-annual 2
2 Period Coupon Payment $25
3
4 15/Nov/99 Beginning
5 Maturity Time Yield PV
6 31/May/00 0.544 5.4% $48.56 -> =PV((C6)/B1,B6*B1,,-B2*B1)
7 30/Nov/00 1.042 5.8% $47.12 -> =PV((D7)/$C$1,C7*$C$1,,-$C$2*$C$1)
8 30/Nov/01 2.042 5.9% $44.37
9 30/Nov/02 3.042 5.9% $41.86
10 30/Nov/03 4.042 6.1% $39.30
11
12 Sum PV $221.20
mixed rates and periods in order to determine the price of a Bond from
the YTM of various bonds from 6 months to a few years (see below).
Below is my idea. But it is not quite correct.
XNPV has been suggested in the other function forum, but I need a
single formula that will handle annual and semi-a periods and new
data.
(Hope this is not confusing or messed up when I post it.)
Thankyou for your help
Kristen
1 Annual/Semi-annual 2
2 Period Coupon Payment $25
3
4 15/Nov/99 Beginning
5 Maturity Time Yield PV
6 31/May/00 0.544 5.4% $48.56 -> =PV((C6)/B1,B6*B1,,-B2*B1)
7 30/Nov/00 1.042 5.8% $47.12 -> =PV((D7)/$C$1,C7*$C$1,,-$C$2*$C$1)
8 30/Nov/01 2.042 5.9% $44.37
9 30/Nov/02 3.042 5.9% $41.86
10 30/Nov/03 4.042 6.1% $39.30
11
12 Sum PV $221.20