Actuarial Outpost Problem with Excel Solver
 User Name Remember Me? Password
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

ACTUARIAL SALARY SURVEYS
Contact DW Simpson for a Personalized Salary Survey

 Thread Tools Search this Thread Display Modes
#1
11-24-2009, 09:21 AM
 dzdydx Member CAS Join Date: Jan 2009 Location: Toronto Studying for a vacation after exams College: University of Waterloo Posts: 286
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, 2-yr bond becomes 1, 3-yr 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 4-parameter non-linear models? Are there any quick manual ways to check reasonability in our case?
#2
11-24-2009, 09:33 AM
 Ron Weasley Member CAS AAA Join Date: Oct 2001 Studying for naught. Favorite beer: Butterbeer Posts: 8,623

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.
#3
11-24-2009, 10:07 AM
 MountainHawk Member CAS AAA Join Date: Dec 2001 Location: Salem, MA Studying for Nothing!!!! College: Lehigh University Alum Favorite beer: Yuengling Posts: 64,850

I don't trust Excel Solver at all. It seems too dependent on the initial 'guess' for where the answer comes out.
__________________

Play in the AO Prediction Game now!

1
#4
11-24-2009, 11:28 AM
 whisper Member CAS AAA Join Date: Jan 2002 Location: Chicago Favorite beer: Hefewizen Posts: 39,596

Quote:
 Originally Posted by dzdydx How good (accuracy) is the Excel Solver when it comes to optimizing 4-parameter non-linear models? Are there any quick manual ways to check reasonability in our case?
Solver has a very bad habit of finding the local minimum/maximum and not necessarily the global minimum/maximum.

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
11-24-2009, 11:51 AM
 far_side Member Join Date: Jan 2005 Posts: 299

Add another solver restriction that your AAA result should fall within the min-max 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 open-ended.

far_side
#6
11-24-2009, 12:04 PM
 SirVLCIV Member SOA Join Date: Feb 2006 Posts: 46,902

My first instinct would be to change the model from

z = a(b^x)(c^y) + d

to

ln(z-d) = ln(a) + x*ln(b)+y*ln(c) ---->

ln(z-d) = a + bx + cy

and try optimizing that.
#7
11-24-2009, 12:35 PM
 mxpx=1/2 Member CAS AAA Join Date: Nov 2008 Studying for reasons unknown Posts: 329

Your Solver fit may improve by using the following options:
Use Automatic Scaling
Central

Better yet, you could use R which is a much better tool for this purpose.
#8
11-24-2009, 04:26 PM
 asdfasdf Member Join Date: May 2004 Location: My perception of reality Studying for nication Posts: 20,078

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
11-24-2009, 06:13 PM
 optimizer Member Join Date: Mar 2009 Studying for VEE & FAP Posts: 352

Quote:
 Originally Posted by dzdydx 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, 2-yr bond becomes 1, 3-yr 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 4-parameter non-linear models? Are there any quick manual ways to check reasonability in our case?
There have been some good suggestions so far in this thread, especially this one:

Quote:
 Originally Posted by whisper 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.
I'll add that fitting exponentials is a very nasty type of nonlinear least squares problem, because such problems are extremely ill-conditioned. Small changes in your data can cause very large changes in your parameters, especially b and c. You need a robust solver that can carefully handle the linear algebra issues. One option is Matlab.

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
11-24-2009, 10:54 PM
 dzdydx Member CAS Join Date: Jan 2009 Location: Toronto Studying for a vacation after exams College: University of Waterloo Posts: 286

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 Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off

All times are GMT -4. The time now is 08:16 AM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top