Help: Black Scholes Option Pricing model in Excel

C

Chuckling

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
Who knows where to find this built in Excel? I’m trying to build a bond valuation model with make whole feature.
For those who work in the fixed income department, what discount rates do you use in your firm’s bond valuation model? Treasury spot rates? Do you get them from Bloomberg?
Many thanks!
 
Honestly, you can’t get out a book look up the formula and type in into Excel? I would like to be nice about that, but Excel has the Normal pdf and cdf. Typing this into Excel should take less than 3 minutes.
Now that raises the next question - blind staggering drunk and tripping on LSD I could get that working in Excel in under 5 minutes. My bond valuation models get years of thought and I’m still not happy with them….
 
Chuckling Wrote:
——————————————————-
> For those who work in the fixed income department,
> what discount rates do you use in your firm’s bond
> valuation model? Treasury spot rates? Do you get
> them from Bloomberg?
I’d think those working in FI have no use for transforming a discount rate into a price (though they regularly do the reverse). I’ve only heard of that being done in a class on bond math.
What exactly are you trying to do?
 
Yes, it’s a project for school. It doesn’t need to be perfect but I’d like to prove what I’ve learned from textbook is theoretically accurate. I have the actual mkt values of a convertible bond at different stock prices. I think if I can value the option free bond and the call option at different stock prices and then add the two together, I should get a number close to the mkt value. Now my questions are:
1. I don’t have the option pricing model built in Excel, other than an option calculator which is an .exe file.
2. CFA text book tells me to use spot rates to value a bond. I’d like to know what the basic assumption is in a real bond valuation model.
DarienHacker Wrote:
——————————————————-
> Chuckling Wrote:
> ————————————————–
> —–
> > For those who work in the fixed income
> department,
> > what discount rates do you use in your firm’s
> bond
> > valuation model? Treasury spot rates? Do you
> get
> > them from Bloomberg?
>
> I’d think those working in FI have no use for
> transforming a discount rate into a price (though
> they regularly do the reverse). I’ve only heard
> of that being done in a class on bond math.
>
> What exactly are you trying to do?
 
1. Do what JDV says: just type B-S into excel, it takes less time to do than the minutes you’ve spent on this bboard posting about it.
2. Look up the yields on the firm’s outstanding bullet bonds; pick the bond of maturity (and other terms) closest to your convert.
 
you need the Monte-Carlo simulated option adjusted spread!!!!
j/k, you wont be figuring that one out on your own anytime soon.
You certainly wont use the treasury spot rates. Those would not be good estimators.
Darien is right, use comparable bullets (regular non-option bonds) of similar maturities and other caracteristics. You then have a good estimator to value the convertible without the option. Then just use black sholes for the option…
but if im not mistaken, you might wanna use VBA for this
 
you don’t need to use VBA to calculate B-S formula. It’s a very simple formula, you only need to use Normdist, ln and exp in Excel.
 
i know. damnit, i was thinking about programming the binomial tree.
And, yes exactly your right on that
 
derivagem software does binomial and BSM pricing in excel, i have it on CD , maybe you can find it online or something
 
You must log in or register to reply here.
Back
Top