Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

ACTUARIAL SALARY SURVEYS
Contact DW Simpson for a Personalized Salary Survey

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 11-24-2009, 10:21 AM
dzdydx's Avatar
dzdydx dzdydx is offline
Member
CAS
 
Join Date: Jan 2009
Location: Toronto
Studying for a vacation after exams
College: University of Waterloo
Posts: 286
Default 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?
Reply With Quote
  #2  
Old 11-24-2009, 10:33 AM
Ron Weasley's Avatar
Ron Weasley Ron Weasley is offline
Member
CAS AAA
 
Join Date: Oct 2001
Studying for naught.
Favorite beer: Butterbeer
Posts: 8,623
Default

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.
Reply With Quote
  #3  
Old 11-24-2009, 11:07 AM
MountainHawk's Avatar
MountainHawk MountainHawk is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: Salem, MA
Studying for Nothing!!!!
College: Lehigh University Alum
Favorite beer: Yuengling
Posts: 64,850
Default

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
Reply With Quote
  #4  
Old 11-24-2009, 12:28 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 38,785
Default

Quote:
Originally Posted by dzdydx View Post
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.
Reply With Quote
  #5  
Old 11-24-2009, 12:51 PM
far_side far_side is offline
Member
 
Join Date: Jan 2005
Posts: 299
Default

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
Reply With Quote
  #6  
Old 11-24-2009, 01:04 PM
SirVLCIV's Avatar
SirVLCIV SirVLCIV is offline
Member
SOA
 
Join Date: Feb 2006
Posts: 46,904
Default

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.
Reply With Quote
  #7  
Old 11-24-2009, 01:35 PM
mxpx=1/2's Avatar
mxpx=1/2 mxpx=1/2 is offline
Member
CAS AAA
 
Join Date: Nov 2008
Studying for reasons unknown
Posts: 329
Default

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

Better yet, you could use R which is a much better tool for this purpose.
Reply With Quote
  #8  
Old 11-24-2009, 05:26 PM
asdfasdf's Avatar
asdfasdf asdfasdf is offline
Member
 
Join Date: May 2004
Location: My perception of reality
Studying for nication
Posts: 20,078
Default

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.
Reply With Quote
  #9  
Old 11-24-2009, 07:13 PM
optimizer optimizer is offline
Member
 
Join Date: Mar 2009
Studying for VEE & FAP
Posts: 352
Default

Quote:
Originally Posted by dzdydx View Post
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 View Post
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.
Reply With Quote
  #10  
Old 11-24-2009, 11:54 PM
dzdydx's Avatar
dzdydx dzdydx is offline
Member
CAS
 
Join Date: Jan 2009
Location: Toronto
Studying for a vacation after exams
College: University of Waterloo
Posts: 286
Default

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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 07:47 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.23356 seconds with 7 queries