Any Answers (back to index)

Excel calculation error

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

 

stevie's picture

Try the DATE function

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.

 

Best reply

I haven't used 'Yield'

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

Are you mssing an argument?

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

Excel calculation error

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

shurst's picture

Analysis Toolpak information

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

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

 

Create your free account

  • Access all articles in full
  • View multimedia
  • Receive email bulletins
  • Private messaging
Register now

Login

Forgotten your password?

Any Answers theme of the month

Latest questions on
Preparing for 2010/11
- payroll & tax planning advice:

Overseas director and PAYE / NIC

Directors and NIC

P46(Car) - what are the rules?

No PAYE scheme but P11d may be needed

IT Zone today

Apple iPhoneGadget blog
Latest news from CES

Snow strategy:
Work remotely

SA songs
Listen to our Spotify list!
 

Download library

Free downloads
Check out our library of podcasts and tutorials.