Excel calculation error

Posted by Anonymous PM | on Wed, 03/02/2010 - 10:50 737 5 comments | report

Just wondering if anyone out there can help me.

I have a calculation in an Excel spreadsheet that works

out a yield % per year on a bond. This caculation worked

fine in the spreadsheet until recently but now is throwing out

a #name error.

The infromation for the formula is as follows:

Issue value 84.00

nominal value 100.00

annual coupon rate 4.0%

settlement date 03/10/1985

maturity date 03/04/2015

payment frequency 2

day count basis 2

value of bond as % of par 84.10552694

My formula is (YIELD,03/10/1985,03/04/2015,4.0%,84.10552694,2,2)

This should return a yield of 5.0408720970%.

Why is the excel formula no longer working, is there a formatting issue?

Would appreciate any suggestions to solve this problem, it is driving me mad.

Thanks,

Damian

Comments

Try the DATE function

stevie | Wed, 03/02/2010 - 11:25 | Permalink

Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

I haven't used 'Yield'

Richard Willis | Wed, 03/02/2010 - 13:15 | Permalink

But if it is a function it should be outside the brackets.

Are you mssing an argument?

Anonymous | Wed, 03/02/2010 - 13:56 | Permalink

I thought the formula required 7 variables, yet yours contains only 6

Excel calculation error

Anonymous | Wed, 03/02/2010 - 14:21 | Permalink

Yes, you are correct . That was a typo, the Nominal value should be in the middle

of the formula as well. I used the date function as suggested by Stevie

plus I also loaded an Excel add on called Analysis Tool Pak and the problem

seems to be solved. Thanks for everyones help with this, much appreciated.

Damian

Analysis Toolpak information

shurst | Thu, 04/02/2010 - 08:48 | Permalink

This article from the Excel compendium might help explain what is going on:

http://www.accountingweb.co.uk/item/173691