# xlf EandA series

## StockReturn function

A simple code exercise. Time allocation 10 minutes.

## 0. Preliminary

### The EXERCISE - complete the following

Write a custom function to return the value of StockReturn (SkRet abbrv), with arguments for each price and optional Return method, and log being the default (the other you must identify). Use only VBA functions (not WS functions). Any error returns #VALUE!

This is a suggested coding solution

### StockReturn function

The task is a VBA based application of stock return formulas covered in session 2 and assignment 1.

Relevant Formulas:

• Log return: $R_t (log) = \log \left ( \frac{P_t}{P_{t-1}} \right )$
• Discrete return: $R_t (discrete) = \frac{\left (P_t - P_{t-1} \right )} {P_{t-1}}$

For return at time t $R_t$, given Price at time t $P_t$ and t -1 $P_{t-1}$

Code 1: the StkRet function interpretation of set task
Function StkRet(P_t As Double, P_tm1 As Double, Optional RetM As Variant) As Variant
' RetM 0 for log, 1 for Delta

On Error GoTo ErrHandler

If IsMissing(RetM) Then RetM = 0

If RetM = 0 Then
StkRet = Log(P_t / P_tm1)
ElseIf RetM = 1 Then
StkRet = (P_t - P_tm1) / P_tm1
Else
GoTo ErrHandler
End If

Exit Function
ErrHandler:
StkRet = CVErr(xlErrValue)
End Function



• Published: 18 November 2022
• Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Time (AET)]