Odd Period Compounding Results

[jamorin]

Team Mr. Excel,

I am having a dilemma I just have not been able to solve yet. I have a series of cash flows that I will receive over time: 11 annual payments of $2,029,000 and one final payment in the 2<SUP>nd</SUP> Month of the 12<SUP>th</SUP> year that is $404,139. I’d like to discount these payments at 13% annually. Using Excel’s PV function I come up with a result of $11,637,796. Using the PV formula using math the result is $11,642,766. I would expect these results to match.
Next I refined the model a little bit and used the XNPV function to derive a value indication of $11,638,892; closer to the $11,642,766 number, but still not exact.
I know I am splitting hairs, but I cannot figure out how Excel is handling the last payment when it is at some point before the end of the year. If I adjust the model for 11 full payments, 12 full payments, etc, the results are exactly the same via a math formula or Excel’s.
Any insight into either correcting my model as attached or explaining why the results are different? I have a feeling someone is going to ask and I’d like to have a solid rationale if we cannot get the numbers to match exactly. Thanks for your collective brain power.
https://secure.logmein.com/f?00_cAikU.1J-XIaKujHTOkNS-JrGXj-XLR708C2knAY

 

[rudfaden]

I'm am not sure, but my guess would be that Excel calculates the discount rate on the basis of a year, shuch that a decimal number represents a fraction of days in a year.

 

[jamorin]

I'm am not sure, but my guess would be that Excel calculates the discount rate on the basis of a year, shuch that a decimal number represents a fraction of days in a year.

Probably so but Im trying to get more than a guess : ) Would like to be able to replicate and understand why. Thanks for the reply.

 

[jamorin]

Still having issues with this calculation. Does anyone have any ideas about how Excel handles PV Factors that are not a full period for example.2 months with annual compouding or 1 week with monthly compounding?

Is there more I can post to assist? Is the question and linked spreadhseet not enough?

 

[xenou]

My take is that Excel expects the number of periods to be equal durations so there's no way to add a partial period in the NPV formula.

I would use the NPV formula for the 11 annual payments of 2,029,200 and add to it the PV of the last payment. This comes out the same as your PV math formula.

I'm not an expert on financial function - so that's the best I can make of it.

ξ

 

[jamorin]

Thanks I'll try that. Isn't it funny that sometimes the easiset solution is the one that alludes us. Now I need to work on a formula to truncate the "odd period" stuff automagically and I'll be set.

 

[brettdj]

My take is that Excel expects the number of periods to be equal durations so there's no way to add a partial period in the NPV formula.ξ

That is correct

It is worth pointing out that NPV

1) assumes end period cashflows. If cashflows are occuring during periods then the NPV can be "tweaked" to a mid-period basis by multiplying the equation by the root of (1+discount rate)
=NPV(3%,A1:A10)*(1+3%)^0.5

2) ignores empty cells at the start of a range, ie if A1:A2 were empty in the earlier formula then A3 is treated as occuring at the end of Period 1 - not Period 3

Personally I prefer calculating manual discounting factors and cross-checking with XNPV

Cheers

Dave