Calculate performance and ratios in Excel
Monthly return in %: previous month / current month - 1
Portfolio returns: SumProduct(% of each stock in portfolio, returns for each stock)
Average price (indicator) and money flow indicator
Step 1: The average of the high, low and close price is known as the typical price. Typical Price = (High + Low + Close) / 3 Step 2: Then multiply the typical price by the trading volume. This gives the money flow. Money Flow = Typical Price x Volume Step 3: Now calculate the Positive and Negative Money Flow. For a trailing time window (of usually 14 days), add the money flows where the typical price is greater than the previous day’s typical price. This is the Positive Money Flow. Add the money flows where the typical price is lower than the previous day’s typical price. This is the Negative Money Flow Step 4: The Money Ratio is simply the Positive Money Flow divided by the Negative Money Flow Step 5: The Money Flow Index is then simply calculated with this formula. Money Flow Index = 100 – 100 / (1 + Money Ratio)
Determine up or down in prices
=IF(AND(A2<A3,A4<A3),1,IF(AND(A2>A3,A4>A3),-1,0)) Peaks = 1, throughs = -1
Sortino ratio
This Excel spreadsheet calculates the Sortino Ratio for an investment, a measure of risk-adjusted return. Investments that emphasize their Sortino Ratio often try to minimize their losses as a part of their trading strategy.
Investors can use a range of measures to gauge the suitability of investments. The Sharpe Ratio, for example, measures the risk-efficiency of investments by using standard deviation to represent risk. A disadvantage of this approach is that this penalizes both downside and upside volatility. Given that investors don't mind (and indeed welcome) large upwards movements in price, the Sharpe Ratio may not best represent investor psychology. The Sortino Ratio, however, only penalizes downside risk, and is defined as
The Target Return is either a minimum acceptable return (as in the original definition), or a risk-free rate. Lower Sortino Ratios signify investments with a greater risk of large losses and should be avoided by risk-averse investors.
The Sortino Ratio was developed as a commercial measure by the investment industry, and does not have the academic heritage and strict mathematical definition of the Sharpe Ratio. As such, several methods are commonly used to measure downside risk, including the semi standard deviation, or the square root of the 2nd lower partial moment. The Excel spreadsheet uses the latter (as suggested by Wikipedia) to represent downside risk.
There's some uncertainty about whether you simply set all values above the minimum acceptable return to zero, or discard those values entirely when computing the downside risk. Setting values above the MAR to zero reduces the computed risk; some feel that this is not consistent with the spirit of the Sortino Ratio and remove these values entirely instead. Given the flexible definition of the Sortino Ratio, then as long as you use a consistent definition to compare the the risk efficiency of investments this may not matter.
Sortino Ratio in VBA
| Function SortinoRatio(returns As Range, MAR As Variant) As Variant Dim n As IntegerDim i As IntegerDim avgReturn As DoubleDim mm2d As DoubleDim downDev As Double n = returns.Rows.CountavgReturn = WorksheetFunction.Average(returns)moment2d = 0For i = 1 To n If returns(i) - MAR < 0 Then mm2d = mm2d + ((returns(i) - MAR) ^ 2) End IfNextdownDev = Sqr(mm2d / n)If downDev > 0 Then SortinoRatio = (avgReturn - MAR) / downDevElse SortinoRatio = "undefined"End If End Function |
This Excel spreadsheet implements both a manual worksheet method and a VBA function to calculate the Sortino Ratio Download Excel Spreadsheet to Calculate the Sortino Ratio From <http://4.bp.blogspot.com/-fyRulfvJ4EM/TeFxCLDnkoI/AAAAAAAAA9c/uQ9QcTiE_Lk/s320/Lower+partial+moment+downside+risk.png>
