#div/0! error message

[dc4000]

Hi All,

Could someone advise what formula can be applied in order to remove the #div/0! value but still perform the underlying calculation? (Bond price @0% yield). The formula works for all values (%) from 1 to 20.

(not sure how to add excel file or screen shot)

The below list is how my s/s is populated:
A4: Coupon Rate
A5: Face Value
A6: Time to maturity
A7: Yield
A8: 0%
A9: 1%

B4: 6%
B5: 100
B6: 5

B8: (formula) =($B$4*(1-1/(1+$A8)^$B$6)/$A8)+($B$5/(1+$A8)^$B$6)

Result : #DIV/0!

B9: (FORMULA) = =($B$4*(1-1/(1+$A9)^$B$6)/$A9)+($B$5/(1+$A9

Result: 124.27 (as expected)

 

[Joe4]

Welcome to the Board!

If your first formula, you are dividing by A8. But as you indicate, A8 is zero. You cannot divide by zero. It is an impossible mathematical calculation (not limited to Excel, this is a true, mathematical concept - dividing by zero makes no sense).

So you really cannot continue on with the calculation, since it is an impossible operation. What you can do is tell it what to return in the event of an error, i.e. by using the IFERROR function, or by checking to see if the values you are trying to divide by are zero in an IF statement.