PV() with a rate change?

  • Thread starter Thread starter Karl Thompson
  • Start date Start date
K

Karl Thompson

What if the discount rate changes during the term. How can that be handled?

Example, assume FV = $10,000, 1 year term, 10% for 6 months and 5% for 6
months. What's the PV?

I tried this:

PV1 =PV( 10/100, 0.5, 0, 10000, 0 )

PV2 = PV( 5/100, 0.5, 0, PV1, 0 )

PV2 = $9,304.84

However, the client maintains that the result should be $9281,58

TIA
 
I'd assume your client calculates the interest rate on a
monthly basis, which you haven't done. If you change your
formulae to:
PV1=PV(10/100/12,6,0,10000,0)
PV2=PV(5/100/12,6,0,-PV1,0)
then you'll get:
PV2 = $9279.84
If that is how the interest rate is supposed to be
applied, this shows that even your client's calculation
overstates the amount, but nowhere near as much as your's.

Cheers
 
What if the discount rate changes during the term. How can that be handled?

Example, assume FV = $10,000, 1 year term, 10% for 6 months and 5% for 6
months. What's the PV?

I tried this:

PV1 =PV( 10/100, 0.5, 0, 10000, 0 )

PV2 = PV( 5/100, 0.5, 0, PV1, 0 )

PV2 = $9,304.84

However, the client maintains that the result should be $9281,58

TIA
Click to expand...



I'm no expert in this area but here's my two cents.

In your formula, the rate should be the rate per period, and the number of
periods adjusted appropriately.

So it looks as if your Rate should be 10/100/2 and the NPER should be 1.

I don't know how your client comes up with that number. If interest is
compounded monthly, then I would use the formula:

=PV(Rate2/12,6,0,PV(Rate1/12,6,0,10000))

which gives a result of $9,279.84

If interest is just compounded at the end of the interest change period, then:

=PV(Rate2/2,1,0,PV(Rate1/2,1,0,10000))

or $9,291.52

If he is averaging the interest rate, then the differences would be even
greater.


--ron
 
Back
Top