Portfolio analysis and optimizationPortfolio Management

Portfolio Optimization

Written by Priya Kumar
Sat Aug 29 2020
Portfolio Optimisation
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Portfolio Optimisation

The Portfolio Optimization with MarketXLS calculates the optimal capital weightings for a basket of investments that gives the highest return for the least risk. The MarketXLS add-in enables it to be applied to either financial instrument or business portfolios. The ability to apply optimization analysis to a portfolio represents an excellent framework for driving capital allocation and investment.

What Is Portfolio Optimization?

Portfolio optimization is called as ‘optimal asset allocation’, building the portfolio to yield the maximum possible return while maintaining the amount of risk you’re willing to carry.  Creating a balanced portfolio means you want to spread your investment capital across various assets. Then, you can balance those assets to attain your desired risk-reward outcome.

Portfolio optimization should result in what investors call an ‘efficient portfolio’. This means it’s generating the highest possible return at your established risk tolerance. Alternatively, this term may refer to a portfolio with the minimum amount of risk for the return it seeks, although it’s less common usage.

Advantages of Portfolio Optimization

Maximizing Return – The first and foremost objective of portfolio optimization is maximizing return for a given risk level. The risk-return is maximized at the point on the efficient frontier that represents the optimal portfolio. So investors pursuing the process of portfolio optimization are often able to achieve high returns per unit of risk for their portfolio.

Diversification – Optimal Portfolios are well diversified to do away with the unsystematic risk or the non-priced risk. Diversification helps in protecting investors against downside in case a particular asset under-performs. The other assets in the portfolio will protect the investor’s portfolio from crashing.

Key features of the Portfolio Optimization with MarketXLS

1. Ease and flexibility of input, with embedded help, prompt.

2. Ability to specify the weights held in each asset in the portfolio.

3. Specifies the returns and the risk associated using MarketXLS formula for the optimized portfolio.

4. Optimization can be made on the Sharpe ratio or Sortino ratio as well as to get return-risk ratio.

5. Results are saved for both minimum and maximum levels of returns and risk with Value-at-risk (VaR) calculation for that resulting portfolio.

6. The intuitive graphical result is displayed with the Monte Carlo simulation and Efficient Frontier, including probability analysis on a specified portfolio.

Process of Portfolio Optimization with MarketXLS

Portfolio Optimization with MarketXLS® add-in helps investors assess their portfolios and make investment decisions. MarketXLS® template has now made it easy to perform Portfolio Optimization with just a few clicks. The asset allocation for an optimal portfolio is essentially a two-part process:

1. Selecting asset and respective weightsPortfolio managers first choose the asset classes they want to allocate funds. Then they decide the weight of every asset class is included.

2. Selecting the Function – After deciding the asset classes, the investor decides how much of a particular stock or a bond he wants to include in the portfolio based on the highest returns and minimal returns using MarketXLS.

The MarketXLS provides formulas for Sharpe Ratio, Sortino Ratio, Value-at-risk(VaR), Efficient Frontier, Monte Carlo, Portfolio Beta, Weighted index to optimize the portfolio representing the risk-return relationship.


Portfolio Optimization


Here are some of the outputs of graphs provided in MarketXLS tool:

The Efficient Frontier gives a combination of assets with the optimal level of expected return for a given level of risk, all the portfolios on the line are efficient.

Portfolio Optimization

Monte Carlo simulates the stock price with 100 repeated simulations of random number with time steps on the graph:

Portfolio Optimization

Portfolio Optimization with MarketXLS is efficient for those investors who want to maximize the risk-return since this process is targeted at maximizing the return for every additional unit of risk taken in the portfolio. The investors can combine a combination of risky assets with a risk-free asset. Portfolio Optimization gives the portfolio that would generate the highest possible return from the combination.


Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

Optionable: Yes
Market Cap: 8,953 M
Industry: Auto & Truck Dealerships
52 week range    
Zim Integrated Shipping Services Ltd logo

Zim Integrated Shipping Services Ltd

Optionable: Yes
Market Cap: 7,792 M
Industry: Deep Sea Freight Transportation
52 week range    
Universal Logistics Holdings Inc. logo

Universal Logistics Holdings Inc.

Optionable: No
Market Cap: 684 M
Industry: Trucking
52 week range    
NRG Energy Inc. logo

NRG Energy Inc.

Optionable: Yes
Market Cap: 10,725 M
Industry: Utilities - Independent Power Producers
52 week range    
Corteva Inc. logo

Corteva Inc.

Optionable: Yes
Market Cap: 41,692 M
Industry: Agricultural Inputs
52 week range    
Avnet Inc. logo

Avnet Inc.

Optionable: Yes
Market Cap: 4,732 M
Industry: Electronics Distribution
52 week range    
Universal Corporation logo

Universal Corporation

Optionable: Yes
Market Cap: 1,529 M
Industry: Tobacco
52 week range    
Kronos Worldwide Inc logo

Kronos Worldwide Inc

Optionable: Yes
Market Cap: 1,897 M
Industry: Specialty Chemicals
52 week range    
Boise Cascade L.L.C. logo

Boise Cascade L.L.C.

Optionable: Yes
Market Cap: 3,260 M
Industry: Lumber & Wood Production
52 week range    
Rollins Inc. logo

Rollins Inc.

Optionable: Yes
Market Cap: 17,428 M
Industry: Business Services
52 week range    

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!