
#1




Problem with Excel Solver
Background: We use Excel 2003
At my workplace, we're using regression to model corporate bond spreads (bond yield  yield on Canada bonds with same term) over 2 parameters, term and rating. We convert the bond term and ratings into numeric equivalents, for example AAA becomes 1, AA becomes 2, 2yr bond becomes 1, 3yr becomes 2, etc. Our model is z = a(b^x)(c^y) + d, b >= 1, c >= 1, a > 0 where z is the bond spread, x is the term's numeric equivalent, y is the rating's numeric equivalent, and a, b, c, d are the parameters. Every month we feed Excel new corporate bond spread data with sample size ~ 400 and set up the spreadsheet to calculate the squared difference of each bond  actual spread minus predicted spread squared. We would then use the Solver to minimize the total variance. We have been using this methodology for a while with somewhat reasonable results. However yesterday we grew suspicious when Excel predicted AAA spreads (15 bps) that are much lower than the AAA spread data we gave Excel (30  60bps). Upon closer scrutiny we found that everytime we use the Solver, the optimized values b and c get changed noticeable while the values a and d remain fairly constant (in fact, a + d hardly changes at all) Also, despite the seemingly large amount of calculations in optimizing this badboy, the Solver solves it in less than 5 seconds. Question: How good (accuracy) is the Excel Solver when it comes to optimizing 4parameter nonlinear models? Are there any quick manual ways to check reasonability in our case? 
#2




There would be a learning curve, but, for testing purposes, you could repeat the regression on another platform, such as R, to compare the results. Similarly, you could create VBA code for the regression instead of using the Excel canned capability.

#4




Quote:
The first thing to know is if this project is of enough importance to purchase a better optimization tool than Solver. If so, go purchase something that doesn't have the flaws that Solver has. If not, you may need to run solver with multiple starting points and some logic restrictions to make sure results make sense. 
#5




Add another solver restriction that your AAA result should fall within the minmax range of your AAA input data. Perhaps you could narrow the ranges for a,b,c,d also based on your previous results instead of leaving them openended.
far_side 
#8




I came across a similar problem earlier this week, it doesn't really try very hard, especially if you just tell it to minimize a variance parameter, for me it was really just adjusting one of my four parameters, I ended up eyeballing it (the project wasn't terribly important so that worked out ok).
__________________
Your own conciousness blinds you to the true existence of all things external to it. 
#9




Quote:
Quote:
Whatever solver you use, do try many starting points. The fact that Excel's Solver is giving you a solution in less than 5 seconds is a red flag, IMHO. I would be interested to know what its stopping criteria are, and how it takes into account the possibility of local minima. I would also like to know how R solves nonlinear least squares problems. I would appreciate it if an R user could provide me with a link that describes the algorithmic details. 
#10




Thanks for the many suggestions.
We don't have R but we do have Matlab at work. I don't think my team uses it much (if at all). As I have not used Matlab before this sounds like a somewhat big endeavor for the future. In the meantime I'll try giving Excel Solver many starting guesses and see if anything changes. Is Excel 2007's Solver any different from Excel 2003's? Maybe upgrading would be the cheaper and faster option. 
Thread Tools  Search this Thread 
Display Modes  

