SQL Server MOIC Function

Updated 2023-10-09 20:13:13.673000

Description

Use the aggregate function MOIC to calculate the multiple of invested capital.

Syntax

SELECT [wct].[MOIC] (
  <@Amt, float,>)

Arguments

@Amt

the invested capital, distributions and residual value. @Amt is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

@Amt values less than zero are treated as invested capital.

@Amt values greater than zero are treated as distribution and invested capital.

Examples

In this example we have cash flows occurring sporadically from 30-Nov-2011 through to 06-Feb-2014.

SELECT wct.MOIC(cf_amt) as MOIC
FROM
(
    VALUES
        (-100000, '11/30/2011', 'Investment'),
        (-50000, '3/15/2012', 'Investment'),
        (-2500, '7/18/2012', 'Investment'),
        (12500, '11/30/2012', 'Distribution'),
        (37500, '1/23/2013', 'Distribution'),
        (75000, '4/30/2013', 'Distribution'),
        (90000, '2/6/2014', 'Residual Value')
) n (cf_amt, cf_date, descr);

This produces the following result.

MOIC
1.40983606557377

In this example, there are investments in several funds and we want to calculate the multiple of invested capital for each fund.

SELECT id_fund,
       wct.MOIC(cf_amt) as MOIC
FROM
(
    VALUES
        ('fund_4', 52648.79, '2011-07-26', 'Distribution'),
        ('fund_2', 24139.12, '2010-03-15', 'Distribution'),
        ('fund_4', -7753.76, '2009-12-03', 'Investment'),
        ('fund_3', -79186.02, '2009-05-18', 'Investment'),
        ('fund_1', 3066.05, '2008-07-07', 'Distribution'),
        ('fund_4', -31117.56, '2009-04-23', 'Investment'),
        ('fund_4', -97759.99, '2010-05-25', 'Investment'),
        ('fund_3', 94854.52, '2009-01-10', 'Distribution'),
        ('fund_1', 70723.28, '2008-08-06', 'Distribution'),
        ('fund_2', 55094.54, '2012-01-17', 'Distribution'),
        ('fund_1', -47405.78, '2012-06-27', 'Investment'),
        ('fund_3', 90918.52, '2009-05-06', 'Distribution'),
        ('fund_1', -64781.66, '2009-06-02', 'Investment'),
        ('fund_2', 42763.04, '2008-02-08', 'Distribution'),
        ('fund_2', -82375.82, '2012-04-13', 'Investment'),
        ('fund_4', -48254.35, '2011-01-01', 'Investment'),
        ('fund_2', -14354.14, '2009-01-15', 'Investment'),
        ('fund_1', -28037.22, '2010-06-14', 'Investment'),
        ('fund_2', -91220.67, '2010-07-27', 'Investment'),
        ('fund_3', -35367.08, '2008-03-29', 'Investment'),
        ('fund_1', 108572.27, '2012-11-30', 'Residual Value'),
        ('fund_2', 02267.47, '2012-11-30', 'Residual Value'),
        ('fund_3', 123225.51, '2012-11-30', 'Residual Value'),
        ('fund_4', 122355.01, '2012-11-30', 'Residual Value')
) n (id_fund, cf_amt, cf_date, descr)
GROUP BY id_fund;