More PV and mixed rates

[Kristen]

I am trying to determine the PV of a stream of cash flows that have
mixed rates and periods. Below is what I am doing. Is it correct? (A
book I'm reading gives a different answer).

(Hope this is not confusing/messed up when I post it.)

As always, thankyou for your help

Kristen

A B C D
1 Annual/Semi-annual 2 = Payments per year
2 Period Coupon Payment $25

3 15/Nov/99 Beginning
4 Maturity Period Yield PV
5 31/May/00 0.544 5.4% $48.56 <- =PV((C6)/B1,B6*B1,,-B2*B1)
6 30/Nov/00 1.042 5.8% $47.12
7 30/Nov/01 2.042 5.9% $44.37
8 30/Nov/02 3.042 5.9% $41.86
9 30/Nov/03 4.042 6.1% $39.30
$221.20 <- Sum above

 

[Norman Harker]

Hi Kristen!

I have problems with this. Payments are at fixed dates of May and November
and with a 15-Nov-1999 base date the next payment is only 15 days away. Also
the measurement of the term is suspect as is using a rate that is not based
upon that term

My approach would be to schedulize the cash flows against dates and use the
XNPV function for each of the investments and sum the results. You'll need
to convert your rates to the annual effective equivalent of these semi
annual rates as XNPV requires an annual effective discount rate.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Kristen]

Thanks Norman

Below is what I've come up with (not 100% yet). But it is very messy.
I'm trying to develop something that will allow me to change from
annual to semi-A quickly and enter the data with reasonable ease, thus
a single formula as in my first attempt:

1 Annual/Semi-annual 2
2 Period Coupon Payment $25
3
4 15/Nov/99 Beginning
5 Maturity Yield
6 31/May/00 0.544 5.4% $48.56 -> =PV((C6)/B1,B6*B1,,-B2*B1)

Thankyou so much
Kristen


1 Date PerLgth CF Rate/Per YTM (An) XNPV CF
2 15/Nov/99
3 31/May/00 0.544 $27.22 2.96% 5.43% $26.44
4 30/Nov/00 0.500 $25.00 2.89% 5.78% $23.62
5 30/May/01 0.500 $25.00 2.89%
6 30/Nov/01 0.500 $25.00 2.97% 5.94% $49.30
7 30/May/02 0.500 $25.00 2.97%
8 30/Nov/02 0.500 $25.00 2.97% 5.93% $49.28
9 30/May/03 0.500 $25.00 2.97%
10 30/Nov/03 0.500 $25.00 3.03% 6.05% $49.27

Sum XNPV =$197.91

 

[Norman Harker]

Hi Kristen!

I'm not sure that I'm interpreting your investment right but I've sent
a possible solution.

I have had to expand the columns to get one investment per column and
take the XNPV of each column but in doing that I get some rather
strange investments.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.