Thread: Xnpv Calculation Does not Seem to work out Exactly.

Dear All

I have a doozie! I was wondering if anyone who is an expert at Mathematics/excel can tell me why the yield calc in the attached spreadsheet does not equal the discount rate. It is only marginally out but I am racking my brains as to why!
Please help the yeild should equal 12.5% exactly

im not sure but it may have something to do with the fact that your cashflows stop abrubptly after 2061. If you extend to the end of the sheet then you get down to 12.51%. You may find that if you do it for a perpetuity they are exactly equal.



No guarantee that im right, however.

Originally Posted by SimonMilitano

Dear All

I have a doozie! I was wondering if anyone who is an expert at Mathematics/excel can tell me why the yield calc in the attached spreadsheet does not equal the discount rate. It is only marginally out but I am racking my brains as to why!
Please help the yeild should equal 12.5% exactly

XNPV assumes that the first cash flow occurs at t=0
Thus instead of using 30 Jun 11 as the starting date, set it to 01 Jun 11 the rest of the dates are not affected and the EOMONTH formula will still produce the correct dates starting at t=1 i.e. 30 Jun 12

This will change the answer you have from the XNPV formula and the Yield comes down to 12.508%

You can also try this online XNPV Calculator