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!


1. Tasks

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:

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
        GoTo ErrHandler
    End If

Exit Function
    StkRet = CVErr(xlErrValue)
End Function