Present value of 5% bond

[davep3000]

Hey, I was wondering if you guys could explain this to me.



On the 1st tab I have a situation where a $100 bond pays coupon payments of $5 (5% bond). I have 3 examples... a 1 year bond, a 2 year bond and a 3 year bond.



On that tab, I discount the cash flows at 5% and so I expect the NPV formula to give me $0 (and it does). I expect the sum of the discounted cash flows to give me $100 and they do



But on the 2nd tab I change the discount rate to 3%. As expected, the $5 coupon bond trades at a premium. But what I don't understand is the NPV formula gives a slightly different result to the manual discounting columns (and the PV formula columns). That is indicated by the "Variance" on row 23.



The 3 year bond has a bigger variance than the 2 year which has a bigger variance than the 1 year.



Same thing when I use a 7% discount rate (3rd tab)



I'm wondering what I'm doing wrong / which is right - the NPV formula or the manual calc.

Can't download the mini-sheet software unfortunately but I can email you the file. Pretty easy to reconstruct...

 

[davep3000]

so, I just had the idea of adding some dates and using the XNPV formula and I see that agrees with my manual recalc. So I guess there is something about the NPV formula that I don't understand

 

[davep3000]

I figured this out, I shouldn't have included the initial $ - 100 CF in the NPV formula because Excel thinks that happens at the end of year 1 instead of at period zero.