## Portfolio Optimization in Excel using Solver Function

### Portfolio Optimization in Excel using Solver Function

I have searched the board for and cannot find how to run a portfolio optimizer using Simba's backtester. I found some stuff on the web that while precise, still confused me.

I would like to optimize a portfolio. I have added in the Excel function SOLVER, which I understand is the right function to use; I am just having trouble with putting in the correct inputs/constraints/target.

Can someone, anyone, walk us through how to use SOLVER (or any other similar approach) to optimize a portfolio using the backtester?

I would like to optimize a portfolio. I have added in the Excel function SOLVER, which I understand is the right function to use; I am just having trouble with putting in the correct inputs/constraints/target.

Can someone, anyone, walk us through how to use SOLVER (or any other similar approach) to optimize a portfolio using the backtester?

### Re: Portfolio Optimization in Excel using Solver Function

jonojono1,

thanks for asking this. I too would like to learn how solver works for MVO.

1210

thanks for asking this. I too would like to learn how solver works for MVO.

1210

### Re: Portfolio Optimization in Excel using Solver Function

Funny, but that very same issue, or should I say desire to discuss results of such optimization, prompted me to post for the first time on this board, years ago:

viewtopic.php?t=10313

As you can see from the above thread, people question the value of such optimization, and after spending years on this board I do too.

As in "Past performance..." and you should know the rest by now.

Hopefully that thread is of some help despite not addressing your request directly.

viewtopic.php?t=10313

As you can see from the above thread, people question the value of such optimization, and after spending years on this board I do too.

As in "Past performance..." and you should know the rest by now.

Hopefully that thread is of some help despite not addressing your request directly.

### Re: Portfolio Optimization in Excel using Solver Function

1210SDA: Thanks for sharing that. That means at least 2 of us would benefit by the answer. I am sure there are more among us that would also benefit.

Serbeer:

I appreciate you linking me to that thread. While I do not claim to fully understand the limitations of backtesting, I am aware that there is strong academic and empirical evidence that wisely caution against it. But it would still help me to know how to construct optimizations using SOLVER because I want to take those optimizations, run them against large swaths of data, find relationships across that data with other portfolios and then make some assumptions as starting points for a new hypothesis.

I read the link you posted, and from it I gather that you may be someone who knows how to walk us through how to optimize using SOLVER in Simba's backtester. If this is true, would you walk us through it?

Serbeer:

I appreciate you linking me to that thread. While I do not claim to fully understand the limitations of backtesting, I am aware that there is strong academic and empirical evidence that wisely caution against it. But it would still help me to know how to construct optimizations using SOLVER because I want to take those optimizations, run them against large swaths of data, find relationships across that data with other portfolios and then make some assumptions as starting points for a new hypothesis.

I read the link you posted, and from it I gather that you may be someone who knows how to walk us through how to optimize using SOLVER in Simba's backtester. If this is true, would you walk us through it?

### Re: Portfolio Optimization in Excel using Solver Function

JonoJono1 wrote:I read the link you posted, and from it I gather that you may be someone who knows how to walk us through how to optimize using SOLVER in Simba's backtester. If this is true, would you walk us through it?

No, not true. I used 3rd party software (as stated my original post), not Solver. Sorry, but cannot help with Solver, never used it.

### Re: Portfolio Optimization in Excel using Solver Function

I used to teach an online course in asset allocation at UC Irvine; the objective was to teach the students how to create efficient portfolios in Excel using Solver.

Shoot me a PM if you like.

Shoot me a PM if you like.

Simplify the complicated side; don't complify the simplicated side.

### Re: Portfolio Optimization in Excel using Solver Function

Magician: Wow! Thanks for the invite. PM sent.

### Re: Portfolio Optimization in Excel using Solver Function

1210sda wrote:jonojono1,

thanks for asking this. I too would like to learn how solver works for MVO.

1210

I sent you a PM.

Simplify the complicated side; don't complify the simplicated side.

### Re: Portfolio Optimization in Excel using Solver Function

JonoJono1 wrote:Magician: Wow! Thanks for the invite. PM sent.

Apparently your PM is disabled; I sent you an e-mail.

Simplify the complicated side; don't complify the simplicated side.

### Re: Portfolio Optimization in Excel using Solver Function

Magician,

What you sent to me by email was great! Very kind of you to offer it up.

I am now a Solver practitioner.

My PM is disabled (I think) because I am still a noob. When LadyGeek determines I am worthy, she will upgrade me, I hope.

What you sent to me by email was great! Very kind of you to offer it up.

I am now a Solver practitioner.

My PM is disabled (I think) because I am still a noob. When LadyGeek determines I am worthy, she will upgrade me, I hope.

### Re: Portfolio Optimization in Excel using Solver Function

JonoJono1 wrote:Magician,

What you sent to me by email was great! Very kind of you to offer it up.

My pleasure.

JonoJono1 wrote:I am now a Solver practitioner.

With therapy, you can return to normal. But it takes years.

Simplify the complicated side; don't complify the simplicated side.

### Re: Portfolio Optimization in Excel using Solver Function

Magician,

Thanks for the Excel Solver link. It is a really neat program. Prior to this I was clueless as to how to use Solver.

I would recommend that any Boglehead's who want to optimize their portfolio's using Solver contact you with a PM.

Thanks again.

1210

Thanks for the Excel Solver link. It is a really neat program. Prior to this I was clueless as to how to use Solver.

I would recommend that any Boglehead's who want to optimize their portfolio's using Solver contact you with a PM.

Thanks again.

1210