I've written some VBA code to simulate the effect of borrowing money, investing it, and repaying the loan daily.


  1. Start with a portfolio value of P = 1
  2. Each day borrow P, invest 2*P, and get a lognormal return on double the portfolio. Then repay (1+daily interest)*P.
  3. Repeat for each trading day in the year
  4. Repeat for numT trials
  5. Calculate the mean and variance of excess returns for the trial results. Use this to calculate a Sharpe Ratio.

I would expect that the Sharpe Ratio would be the same as the unleveraged Sharpe Ratio when borrowing at the risk-free rate and lower (higher) when borrowing at a higher (lower) rate. However, I'm not matching the expected Sharpe Ratio. I've tried calculating returns as arithmetic or as log, and neither has made me match.

Actual Code:

Sub margintest()
Dim x&, y&, numT&
Dim m#, my#, v#, vy#, p#, rf#, sum#, SqSum#

my = 0.08
vy = 0.5
rf = 0.03
m = my / 252
v = vy / 252 ^ 0.5
numT = 10000
Debug.Print "Expected Sharpe:" & (my - rf) / vy

For y = 1 To numT
    p = 1
    For x = 1 To 252
        p = 2 * p * Exp(WorksheetFunction.Norm_Inv(Rnd(), m, v)) - p - p * rf / 252
    Next x
    sum = sum + p - 1 - rf
    'sum = sum + Log(p) - rf
    SqSum = SqSum + (p - 1 - rf) * (p - 1 - rf)
    'SqSum = SqSum + (Log(p) - rf) * (Log(p) - rf)
Next y
mean = sum / numT
Var = SqSum / numT - mean ^ 2
Sharpe = mean / Var ^ 0.5
Debug.Print "Mean:" & mean & ", Var:" & Var & ", Sharpe:" & Sharpe

End Sub

Results: Expected Sharpe Ratio = (.08-.03)/.5=.1

Simulated Sharpe Ratios (Log Returns): -0.1461820, -0.1531049, -0.1427345 Simulated Sharpe Ratios (Arithmetic Returns): 0.2332556, 0.2367405, 0.2286082

How should the Sharpe Ratio be calculated and why is it not matching the expected ratio when borrowing at the risk-free rate?


I have not fully gone through the logic of your investment process, but I think the term which you use to propagate the price from one timestep to the next is flawed:

Exp(WorksheetFunction.Norm_Inv(Rnd(), m, v))

This function generates Gaussian random numbers with mean $\mu t$ and standard deviation $\sqrt{\sigma/252}$. What is missing in this is the $\sigma$ component in the drift:

$S_{i+1}=S_{i} exp((\mu-0.5\sigma^{2})\delta t+\sqrt{\delta t}\sigma z)$

where z is a (0,1) normal random number. To fix this, you will have to adjust the definition of "my" in the VBA code accordingly.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.